Links
SQL Developers Guide: Database Structure
The Gossamer Links database is made up of several tables. The main three
tables that hold all the data are: Links, Category and
Users. The definition
of the tables are show below. They use mysql syntax for the types, but
GT::SQL will map these to the best type in other databases.
The tables are joined
together as follows:
Note: These
represent the core tables. There are a few other for managing mailing lists
and tracking the status of links, but these are the primary ones.
The
descriptions for the main three tables are below:
Links |
Column |
Type |
Notes |
ID |
INT |
Primay Key, auto incremented integer |
Title |
CHAR(100) |
Title of the link |
URL |
CHAR(255) |
URL of the link |
LinkOwner |
CHAR(50) |
Foreign key to Users table. Represents
user who owns this link. |
Add_Date |
DATE |
Date the link was added |
Mod_Date |
DATE |
Date the link was last changed |
Description |
TEXT |
A description about the link |
Hits |
SMALLINT |
Number of time the link has been clicked
on |
isNew |
ENUM('Yes','No') |
Flag saying whether the link is new |
isChanged |
ENUM('Yes','No') |
Flag saying whether the link has changed |
isPopular |
ENUM('Yes','No') |
Flag saying whether the link is popular |
isValidated |
ENUM('Yes','No') |
Flag saying whether the link has been
validated |
Rating |
FLOAT |
Current rating of link |
Votes |
SMALLINT |
Total number of votes cast |
Status |
SMALLINT |
Status of link (HTTP Status code 200 =>
OK, 404 => Not found, etc) |
Date_Checked |
DATETIME |
Last time the link has been checked |
Timestmp |
TIMESTAMP |
Timestamp of last time link was modified. |
Category |
Column |
Type |
Notes |
ID |
INT |
Primary
Key, Auto Incremented integer |
Name |
CHAR(255) |
Name of
the category |
FatherID |
INT |
ID Number
of parent category, 0 for a root level category. |
Full_Name |
CHAR(255) |
A slash
(/) separated list of category name. i.e. if the category is named
Video Cards and is in Hardware which is in Computers, then Full_Name
would be Computers/Hardware/Video_Cards |
Description |
TEXT |
Optional
description used in category listings. |
Meta_Description |
TEXT |
Optional
meta description for generated HTML pages |
Meta_Keywords |
TEXT |
Optional
meta keywords for generated HTML pages. |
Header |
TEXT |
Optional
category header |
Footer |
TEXT |
Optional
category footer |
Number_of_Links |
INT |
Total
count of all links in this category and subcategories |
Has_New_Links |
ENUM('Yes','No') |
Flag
telling whether there are any new links anywhere within this category. |
Has_Changed_Links |
ENUM('Yes','No') |
Flag
telling whether there are any changed links anywhere within this
category. |
Newest_Link |
DATE |
The date
of the most current link within this category tree. |
Timestmp |
TIMESTAMP |
Auto
generated timestamp of the last time this record was updated. |
Category_Template |
CHAR |
Contains the
name of a custom template to use, or template set to use when displaying a category. |
Users |
Column |
Type |
Notes |
Username |
CHAR(50) |
Primary
key representing the users login name |
Password |
CHAR(25) |
Users
password (default is not encrypted) |
Email |
CHAR(75) |
Email
address |
Name |
CHAR(75) |
Real Name |
Validation |
CHAR(20) |
Optional
validation code that gets sent to users email to ensure its a real
email address. |
Status |
ENUM('Not
Validated', 'Registered','Administrator') |
Flag
saying what class of user this is. |
ReceiveMail |
ENUM('Yes','No') |
Flag
saying whether the user should get any email or not. |
Newsletter |
ENUM('Yes','No') |
Flag
saying whether the user is subscribed to the newsletter or not. |
|