---------------------------------------

# Mouseion database definition scritp

# last edited: 6/9/2000 by gm





#

# Table structure for table 'Action'

#

drop table if exists Action;

CREATE TABLE Action (

  id int(11) DEFAULT '0' NOT NULL auto_increment,

  description text,

  PRIMARY KEY (id)

);





#

# Table structure for table 'categories'

#

drop table if exists Category;

CREATE TABLE Category (

  id int(11) DEFAULT '0' NOT NULL auto_increment,

  name varchar(150),

  code int(11),

  description text,

  parentId int(11),

  PRIMARY KEY (id)

);



#

# Table structure for table 'categories_site'

#

drop table if exists Category_Site;

CREATE TABLE Category_Site (

  categoryId int(11) NOT NULL,

  siteId int(11) NOT NULL,

  PRIMARY KEY (categoryId, siteId)

);



#

# Table structure for table 'Company'

#

drop table if exists Company;

CREATE TABLE Company (

  id int(11) DEFAULT '0' NOT NULL auto_increment,

  name varchar(150),

  parentCompanyId int,

  countryId int(11) NOT NULL,

  ownershipType varchar(40),

  date_modified date,

  PRIMARY KEY (id)

);







#

# Table structure for table 'CompanyTip'

#

drop table if exists CompanyTip;

CREATE TABLE CompanyTip (

  id int(11) DEFAULT '0' NOT NULL auto_increment,

  description text,

  CompanyId int(11)  NOT NULL,

  urlId int(11) NOT NULL,

  tipTypeId int(11) NOT NULL,

  tipRatingId int(11) NOT NULL,

  PRIMARY KEY (id)

);







#

# Table structure for table 'Country'

#

drop table if exists Country;

CREATE TABLE Country (

  id int(11) DEFAULT '0' NOT NULL auto_increment,

  name varchar(10),

  PRIMARY KEY (id)

);







#

# Table structure for table 'Coverage'

#

drop table if exists Coverage;

CREATE TABLE Coverage (

  id int(11) DEFAULT '0' NOT NULL auto_increment,

  siteId int(11) NOT NULL,

  editorId int(11) NOT NULL,

  actionId int(11) NOT NULL,

  date_modified date,

  PRIMARY KEY (id)

);







#

# Table structure for table 'editors'

#

drop table if exists Editor;

CREATE TABLE Editor (

  id int(11) DEFAULT '0' NOT NULL auto_increment,

  name varchar(150),

  password varchar(20),

  PRIMARY KEY (id)

);







#

# Table structure for table 'Function'

#

drop table if exists Function;

CREATE TABLE Function (

  id int(11) DEFAULT '0' NOT NULL auto_increment,

  siteId int(11) NOT NULL,

  categoryId int(11) NOT NULL,

  name varchar(150),

  decription text,

  urlId int NOT NULL,

  parentId int(11) NOT NULL,

  rating int(11),

  functionTypeId int NOT NULL,

  PRIMARY KEY (id)

);







#

# Table structure for table 'FunctionTip'

#

drop table if exists FunctionTip;

CREATE TABLE FunctionTip (

  id int(11) DEFAULT '0' NOT NULL auto_increment,

  description text,

  functionId int(11) NOT NULL,

  urlId int(11) NOT NULL,

  tipTypeId int(11) NOT NULL,

  tipRatingId int(11) NOT NULL,

  PRIMARY KEY (id)

);





#

# Table structure for table 'FunctionType'

#

drop table if exists FunctionType;

create table FunctionType (

  id int(11) DEFAULT '0' NOT NULL auto_increment,

  name varchar(20),

  PRIMARY KEY (id)

);





#

# Table structure for table 'Network'

#

drop table if exists Network;

CREATE TABLE Network (

  id int(11) DEFAULT '0' NOT NULL auto_increment,

  name varchar(150),

  description text,

  mainSiteID int(11),

  dateModified date,

  PRIMARY KEY (id)

);







#

# Table structure for table 'NetworkTip'

#

drop table if exists NetworkTip;

CREATE TABLE NetworkTip (

  id int(11) DEFAULT '0' NOT NULL auto_increment,

  description text,

  networkId int(11) NOT NULL,

  urlId int(11) NOT NULL,

  tipTypeId int(11) NOT NULL,

  tipRatingId int(11) NOT NULL,

  PRIMARY KEY (id)

);







#

# Table structure for table 'Our_Quirky'

#

drop table if exists Our_Quirky;

create table Our_Quirky (

  id int(11) DEFAULT '0' NOT NULL auto_increment,

  dateModified date,

  shortDesc varchar(150),

  longDesc text,

  type varchar(20),

  siteId int NOT NULL,

  primary key (id)

);



#

# Table structure for table 'Public'

#

drop table if exists Public;

CREATE TABLE Public (

  companyId int NOT NULL,

  ticker varchar(15) NOT NULL,

  PRIMARY KEY (companyId, ticker)

);









#

# Table structure for table 'Registration'

#

drop table if exists Registration;

CREATE TABLE Registration (

  siteId int NOT NULL,

  type varchar(20) NOT NULL,

  note text,

  PRIMARY KEY (siteId, type)

);







#

# Table structure for table 'Revisit'

#

drop table if exists Revisit;

CREATE TABLE Revisit (

  siteId int NOT NULL,

  nextRevisit date NOT NULL,

  note text,

  PRIMARY KEY (siteId, nextRevisit)

);





#

# Table structure for table 'Review'

#

drop table if exists Review;

CREATE TABLE Review (

  id int(11) DEFAULT '0' NOT NULL auto_increment,

  userId int,

  title text,

  comment text,

  siteId int NOT NULL,

  dateModified date,

  rating int,

  PRIMARY KEY (id)

);





#

# Table structure for table 'Site'

#

drop table if exists Site;

CREATE TABLE Site (

  id int(11) DEFAULT '0' NOT NULL auto_increment,

  name varchar(150),

  description text,

  alexa bigint,

  # rating here is the integer rating assigned to the site

  rating int,

  # NOT for frames here 0 means no, 1 means yes

  frames bool,

  companyId int,

  networkId int,

  PRIMARY KEY (id)

);







#

# Table structure for table 'SiteTip'

#

drop table if exists SiteTip;

CREATE TABLE SiteTip (

  id int(11) DEFAULT '0' NOT NULL auto_increment,

  description text,

  siteId int(11) NOT NULL,

  urlId int(11) NOT NULL,

  tipTypeId int(11) NOT NULL,

  tipRatingId int(11) NOT NULL,

  PRIMARY KEY (id)

);





#

# Table structure for table 'TipRating'

#

drop table if exists TipRating;

CREATE TABLE TipRating (

  id int(11) DEFAULT '0' NOT NULL auto_increment,

  rating int,

  description text,

  PRIMARY KEY (id)

);





#

# Table structure for table 'TipType'

#

drop table if exists TipType;

CREATE TABLE TipType (

  id int(11) DEFAULT '0' NOT NULL auto_increment,

  type varchar(20),

  description text,

  PRIMARY KEY (id)

);





#

# Table structure for table 'Url'

#

drop table if exists Url;

CREATE TABLE Url (

  id int(11) DEFAULT '0' NOT NULL auto_increment,

  url varchar(150),

  siteId int(11)NOT NULL,

  urlTypeId int NOT NULL,

  PRIMARY KEY (id)

);







#

# Table structure for table 'UrlType'

#

drop table if exists UrlType;

CREATE TABLE UrlType (

  id int(11) DEFAULT '0' NOT NULL auto_increment,

  name varchar(100),

  description text,

  PRIMARY KEY (id)

);



#

# Table structure for table 'User'

#

drop table if exists User;

CREATE TABLE User (

  id int(11) DEFAULT '0' NOT NULL auto_increment,

  name varchar(150),

  password varchar(20),

  connection varchar(50),

  notes text,

  PRIMARY KEY (id)

);