Action Actions represent the activities taken by editors. +-------------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------+------+-----+---------+----------------+ | id | int(11) | | PRI | 0 | auto_increment | The identifier of a unique Action. | description | text | YES | | NULL | | The textual description of an Action. +-------------+---------+------+-----+---------+----------------+ Category Category is a high-level grouping of Sites. Sites can have more than one Category. +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | id | int(11) | | PRI | 0 | auto_increment | The identifier of a unique Category. | name | varchar(150) | YES | | NULL | | The Category's name. | code | int(11) | YES | | NULL | | A number associated with a Category. If this groups Catagories, i.e. many Categories have the same code, then this has meaning. If, however, the code is used to identify a unique Category then code is redundant and id should be used instead. | description | text | YES | | NULL | | A full description of a Category | parentId | int(11) | YES | | NULL | | Link to id of another Category which stands as the parent Category of the current Category, or if there is no parent Category this value is NULL. parentID is a "foreign key" into this table (recursive link into Category table). +-------------+--------------+------+-----+---------+----------------+ Category_Site This is a "join table" between Site and Category. It allows for a many-to-many relation between Site and Category. That is, one Site may have many Categories, and one Category may have many Sites. +------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------+------+-----+---------+-------+ | categoryId | int(11) | | PRI | 0 | | Unique identifier of a Catagory ("foreign key" into Category table). | siteId | int(11) | | PRI | 0 | | Unique identifier of a Site ("foreign key" into Site table). +------------+---------+------+-----+---------+-------+ Company The organization owning/maintaining a Site. +-----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+----------------+ | id | int(11) | | PRI | 0 | auto_increment | Unique identifier of a Company. | name | varchar(150) | YES | | NULL | | The name of a Company. | parentCompanyId | int(11) | YES | | NULL | | Link to id of another Company which stands as the parent Company of the current Company, or if there is no parent Company this value is NULL. parentCompanyID is a "foreign key" into this table (recursive link into Company table). | countryId | int(11) | | | 0 | | Link into the Country table | ownershipType | varchar(40) | YES | | NULL | | Type of company: public, private, etc. | date_modified | date | YES | | NULL | | Records the date a Company record was last changed. +-----------------+--------------+------+-----+---------+----------------+ CompanyTip Reflects only tips related to companies. +-------------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------+------+-----+---------+----------------+ | id | int(11) | | PRI | 0 | auto_increment | Unique identifier of a CompanyTip. | description | text | YES | | NULL | | The textual data of the CompanyTip. | CompanyId | int(11) | | | 0 | | Link ("foreign key") into the Company table. | urlId | int(11) | | | 0 | | Link ("foreign key") into the URL table. | tipTypeId | int(11) | | | 0 | | Link ("foreign key") into the TipType table. | tipRatingId | int(11) | | | 0 | | Link ("foreign key") into the TipRating table. +-------------+---------+------+-----+---------+----------------+ Country Country in which a Company is located/incorporated. +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | | PRI | 0 | auto_increment | Unique identifier of a Country. | name | varchar(10) | YES | | NULL | | Name of the Country. +-------+-------------+------+-----+---------+----------------+ Coverage Coverage reflects the Action taken by an Editor upon a Site. +---------------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+---------+------+-----+---------+----------------+ | id | int(11) | | PRI | 0 | auto_increment | Unique identifier of a Coverage record. | siteId | int(11) | | | 0 | | Link ("foreign key") into the Site table. | editorId | int(11) | | | 0 | | Link ("foreign key") into the Editor table. | actionId | int(11) | | | 0 | | Link ("foreign key") into the Action table. | date_modified | date | YES | | NULL | | Date a Coverage record was last changed. +---------------+---------+------+-----+---------+----------------+ Editor Table of Editor data. +----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | id | int(11) | | PRI | 0 | auto_increment | Unique identifier of an Editor record. | name | varchar(150) | YES | | NULL | | The name of an Editor. | password | varchar(20) | YES | | NULL | | The password of an Editor. +----------+--------------+------+-----+---------+----------------+ Function The specific Function of a URL, i.e. "Amazon CD sales." The same URL can have more than one Function, but one record of the Function table can only relate to one URL. Thus, the word "Amazon" in the example above is essential since this Function only relates to Amazon's CD sales. +----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------+----------------+ | id | int(11) | | PRI | 0 | auto_increment | Unique identifier of a Function record. | siteId | int(11) | | | 0 | | Link ("foreign key") into Site table. | categoryId | int(11) | | | 0 | | Link ("foreign key") into Category table. | name | varchar(150) | YES | | NULL | | Name given to a Function. | decription | text | YES | | NULL | | Textual description of a Function. | urlId | int(11) | | | 0 | | Link ("foreign key") into URL table. | parentId | int(11) | | | 0 | | Link to id of another Function which stands as the parent Function of the current Function, or if there is no parent Function this value is NULL. parentID is a "foreign key" into this table (recursive link into Function table). | rating | int(11) | YES | | NULL | | A number used to rate a Function. | functionTypeId | int(11) | | | 0 | | Link ("foreign key") into FunctionType table. +----------------+--------------+------+-----+---------+----------------+ FunctionTip Tips related to a Function. A Function can have many Function Tips, but one FunctionTip can only have one Function. +-------------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------+------+-----+---------+----------------+ | id | int(11) | | PRI | 0 | auto_increment | Unique identifier of a FunctionTip. | description | text | YES | | NULL | | Textual description of the FunctionTip. | functionId | int(11) | | | 0 | | Link ("foreign key") into Function table. | urlId | int(11) | | | 0 | | Link ("foreign key") into URL table. | tipTypeId | int(11) | | | 0 | | Link ("foreign key") into TipType table. | tipRatingId | int(11) | | | 0 | | Link ("foreign key") into TipRating table. +-------------+---------+------+-----+---------+----------------+ FunctionType The type of a Function. Thus, Functions group under FunctionType. That is, one Function can have at most one FunctionType, but one FunctionType can have many Functions. +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | | PRI | 0 | auto_increment | Unique identifier of a FunctionType. | name | varchar(20) | YES | | NULL | | The name given to a FunctionType. +-------+-------------+------+-----+---------+----------------+ Network Network groups Sites who have a formal/corporate relationship. One Site in the Network acts as the main site of the Network. As such we maintain two relations with Site. First, a one-to-many relation with Site is kept via networkID as a foreign key in the Site table. Second, a one-to-one relation with Site is kept via mainSiteID here to indicate which Site is the main site for the Network. +--------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+----------------+ | id | int(11) | | PRI | 0 | auto_increment | Unique identifier of a Network. | name | varchar(150) | YES | | NULL | | Name of a Network. | description | text | YES | | NULL | | Description of a Network. | mainSiteID | int(11) | | | 0 | | Link into Site table to indicate main Site of Network. | dateModified | date | YES | | NULL | | Date the Network record was last changed. +--------------+--------------+------+-----+---------+----------------+ NetworkTip Tips on Networks. +-------------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------+------+-----+---------+----------------+ | id | int(11) | | PRI | 0 | auto_increment | Unique identifier of a NetworkTip record. | description | text | YES | | NULL | | Textual content of the NetworkTip. | networkId | int(11) | | | 0 | | Link ("foreign key") into the Network table. | urlId | int(11) | | | 0 | | Link ("foreign key") into the URL table. | tipTypeId | int(11) | | | 0 | | Link ("foreign key") into the TipType table. | tipRatingId | int(11) | | | 0 | | Link ("foreign key") into the TipRating table. +-------------+---------+------+-----+---------+----------------+ Our_Quirky The "our" / "our description" and "quirky" / "quirky description" field pairs are structurally identical so we handle both here and add a "type" field. "type" here is either "our" or "quirky." +--------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+----------------+ | id | int(11) | | PRI | 0 | auto_increment | Unique identifier of an Our_Quirky record. | dateModified | date | YES | | NULL | | Date the record was last modified. | shortDesc | varchar(150) | YES | | NULL | | This is the short form of the Mouseion supplied decription. It corresponds to the "ours" and "quirky" fields. | longDesc | text | YES | | NULL | | This is the long form of the Mouseion supplied description. It corresponds to the "our description" and "quirkyFindDesc" fields. | type | varchar(20) | YES | | NULL | | This information is added to distinguish between "our" and "quirky." Thus, we have two types for now. Other types could be added. | siteId | int(11) | | | 0 | | Link ("foreign key") into Site table. +--------------+--------------+------+-----+---------+----------------+ Public Not all companies are public, but those that are have tickers. To facilitate storage of ticker information while avoiding the non-real world idea of a non-public company having a ticker of NULL (that is a non-public company does not have a ticker, NULL or otherwise), ticker information is stored outside of the Company table. note: this is a "weak" entity dependent on Company. +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | companyId | int(11) | | PRI | 0 | | Link to "Strong Entity" Company on which Public depends. | ticker | varchar(15) | | PRI | | | The ticker -- used here as a "discriminant," and part of primary key. +-----------+-------------+------+-----+---------+-------+ Registration Used to indicate whether or not the site requires registration. This is a "weak" entity dependent on Site. Either only sites requiring registration could be here, or this table can indicate whether a site requires registration, does not require registration, or has parts that do require registration and other parts that do not. +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | siteId | int(11) | | PRI | 0 | | Link to "Strong Entity" Site on which Registration depends. | type | varchar(20) | | PRI | | | Indicates "yes" or "no," or if "yes" is implied (that is, only sites that require registration have records in the Registration table) then this field can be dropped. | note | text | YES | | NULL | | An area to record Ad Hoc information about the registration requirementes of a Site. +--------+-------------+------+-----+---------+-------+ Revisit Used to record information on when to next revisit a Site. This is a "weak" entity dependent on Site since not every site needs to have revisit info, but all that do require revisits can be reflected here. +-------------+---------+------+-----+------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------+------+-----+------------+-------+ | siteId | int(11) | | PRI | 0 | | Link to "Strong Entity" Site on which a Revisit depends. | nextRevisit | date | | PRI | 0000-00-00 | | Date on which to next revisit. "Disriminant" and part of primary key. | note | text | YES | | NULL | | Textual description on why to revisit. +-------------+---------+------+-----+------------+-------+ Review Contains user's reviews of Sites in the Mouseion database. +--------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+----------------+ | id | int(11) | | PRI | 0 | auto_increment | Unique identifier of a Review. | userID | int(11) | YES | | NULL | | User who wrote Review. | title | text | YES | | NULL | | Title of the Review. | comment | text | YES | | NULL | | Textual comments about the Site. | siteID | int(11) | | | 0 | | Link into Site table to indicate main Site of Network. | dateModified | date | YES | | NULL | | Date the Review record was last changed. | rating | int(11) | YES | | NULL | | A number rating the Site between 1 and 5. +--------------+--------------+------+-----+---------+----------------+ Site Contains information specific to a Site. This table is a major focus of the database. As such, the application encapsulating the database will likely need to cache Site id's to ensure good performance. +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | id | int(11) | | PRI | 0 | auto_increment | The unique identifier of a Site record. | name | varchar(150) | YES | | NULL | | The name of a Site. | description | text | YES | | NULL | | Textual description of a Site. | alexa | bigint(21) | YES | | NULL | | A number related to the Alexa system -- "It can be from 1 to 1,000,000." | rating | int(11) | YES | | NULL | | A number rating the Site. | frames | tinyint(1) | YES | | NULL | | An indication as to whether the Site uses frames. | companyId | int(11) | YES | | NULL | | Link ("foreign key") into the Comapny table. | networkId | int(11) | YES | | NULL | | Link ("foreign key") into the Network table. +-------------+--------------+------+-----+---------+----------------+ SiteTip Tips related to Sites. +-------------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------+------+-----+---------+----------------+ | id | int(11) | | PRI | 0 | auto_increment | Unique identifier of a SiteTip record. | description | text | YES | | NULL | | Textual content of the SiteTip. | siteId | int(11) | | | 0 | | Link ("foreign key") into the Site table. | urlId | int(11) | | | 0 | | Link ("foreign key") into the URL table. | tipTypeId | int(11) | | | 0 | | Link ("foreign key") into the TipType table. | tipRatingId | int(11) | | | 0 | | Link ("foreign key") into the TipRating table. +-------------+---------+------+-----+---------+----------------+ TipRating All the tips follow the same rating scheme. +-------------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------+------+-----+---------+----------------+ | id | int(11) | | PRI | 0 | auto_increment | Unique identifier of a TipRating. | rating | int(11) | YES | | NULL | | The rating of a tip. | description | text | YES | | NULL | | A description of a TipRating. +-------------+---------+------+-----+---------+----------------+ TipType All the tips follow the same type scheme. +-------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+----------------+ | id | int(11) | | PRI | 0 | auto_increment | Unique identifier os a TipType. | type | varchar(20) | YES | | NULL | | The type of a TipType. | description | text | YES | | NULL | | A description of the TipType. +-------------+-------------+------+-----+---------+----------------+ Url All URLs in system stored here. URLs should be syntactically uniform. URLs are central to the operation of the database, so the application encapsulating the databse should cache URL info. +-----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------------+------+-----+---------+----------------+ | id | int(11) | | PRI | 0 | auto_increment | The unique identifier of a URL | url | varchar(150) | YES | | NULL | | The text of the URL -- URL syntax is PROTOCOL_PART://HOST_PART[:PORT_PART]/PATH[?QUERY] QUERY part should be dropped. PROTOCOL_PART & HOST_PART should stay. PORT_PART and PATH_PART should be used as need. All records should be uniformly structured for consistency in searching. | siteId | int(11) | | | 0 | | Link ("foreign key") inot Site table. | urlTypeId | int(11) | | | 0 | | Link ("foreign key") inot URL type table. +-----------+--------------+------+-----+---------+----------------+ UrlType Organizational and additional information about a URL. Thus, the name field has values like "FAQ", "About", etc. One such UrlType name can be something like "HOME" for the main page of a Site. +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | id | int(11) | | PRI | 0 | auto_increment | Unique identifier of a UrlType. | name | varchar(100) | YES | | NULL | | The name of a UrlType, such as FAQ, About, Home, etc. | description | text | YES | | NULL | | Textual description of a UrlType. +-------------+--------------+------+-----+---------+----------------+ User Records information about a user. This table is not related to any other part of the database. +------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | id | int(11) | | PRI | 0 | auto_increment | The unique identifier of a User | name | varchar(150) | YES | | NULL | | The user name of the User. Here we anticipate values like "usr133" not first name, last name pairs. Should first name, last name, etc. be recorded, simply add necessary fields. | password | varchar(20) | YES | | NULL | | The password of the User. | connection | varchar(50) | YES | | NULL | | The connection of the User -- supplied and maintained by the User. | notes | text | YES | | NULL | | Textual information related to the User. +------------+--------------+------+-----+---------+----------------+