|
|
Posted by sTony on 11/19/06 04:31
Could someone please take a look at this. I'm a newbie, and this is going
to be first internet application. I'd like to know if everything looks ok
before I start writing the php for it.
Thanks in advance,
sTony
-- This Database Definition file is for "The Sharing Circle", which is an
online application
-- designed to help friends share. It is meant to be extensible, meaning
that users should be
-- able to use it to share pretty much everything. The Primary Tables are
USER, MEDIA, and PLACES.
-- In Practice, a user borrows and lends through places designated as
sharing zones. I've also
-- included tables for chat and messaging.
CREATE TABLE USER
-- The User table keeps track of users. Only basic information is needed.
(
id int(7) UNSIGNED NOT NULL DEFAULT '0' auto_increment,
Nick varchar(25) NOT NULL,
Pass varchar(25) NOT NULL,
Email varchar(25) NOT NULL,
ZipCode int(7) UNSIGNED NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE ZipCodes
-- A list of ZipCodes.
(
id int(7) UNSIGNED NOT NULL DEFAULT '0' auto_increment,
ZipCode string(7) NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE MEDIA
-- The Media Table keeps track of all the different peices of media. Along
with
-- descriptive information, it also keeps track who owns the media and who
has
-- the media.
(
id int(15) UNSIGNED NUT NULL DEFAULT '0' auto_increment,
OwnsIt int(7) UNSIGNED NOT NULL,
HasIt int(7) UNSIGNED NOT NULL,
WantsIt int(7) UNSIGNED NOT NULL, -- references Reservations.id
Type int(5) NOT NULL, -- references Types.id
Format string(10) NOT NULL, -- references Formats.id
Catagories int(5) UNSIGNED NOT NULL,
Credits int(10) UNSIGNED NOT NULL,
Title int(10) UNSIGNED NOT NULL, -- references Titles
Description blob,
Credits int(10) UNSIGNED NOT NULL, -- references Credits.id
PRIMARY KEY(id)
);
CREATE TABLE TITLES
-- Titles are listed by media type.
(
id int(10) UNSIGNED NOT NULL DEFAULT '0' auto_increment,
Type int(5) UNSIGNED NOT NULL,
Title string NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE RESERVATIONS
-- Reservations are for each individual media title of a given type and
format
(
id int(10) UNSIGNED NOT NULL DEFAULT '0' auto_increment,
Title int(10) UNSIGNED NOT NULL, -- references Titles.id
Type int(5) UNSIGNED NOT NULL, -- references Types.id
Format int(3) UNSIGNED NOT NULL, -- references Formats.id
User int(7) UNSIGNED NOT NULL, -- References User.id
When timestamp NOT NULL, -- When The Reservation Was Made.
PRIMARY KEY(id)
);
CREATE TABLE HISTORY
-- The history table keeps track of an items transaction history. It's a
simple
-- table that lists the giver and the taker of an item, as well as the time
and place
-- of the transaction.
(
Media int(15) UNSIGNED NOT NULL, -- The Media in Question - References
Media.id
Giver int(7) UNSIGNED NOT NULL, -- Who Gave The Item - References User.id
Taker int(7) UNSIGNED NOT NULL, -- Who Took The Item - References User.id
When timestamp NOT NULL, -- The Time of the transaction. - a TimeStamp
Location int(7) UNSIGNED NOT NULL, -- References Places.id
PRIMARY KEY(Media,When)
);
CREATE TABLE TYPES
-- A list of all possible media types. Each peice of media may
-- belong to only one type. This is a one to one relationship.
(
id int(5) UNSIGNED NOT NULL DEFAULT '0' auto_increment,
value string(20) NOT NULL, -- Media Types, such as Movies, Music, and
Games.
PRIMARY KEY(id,value)
);
CREATE TABLE CREDITS
-- Credits are listed for every title of every type.
(
id int(10) UNSIGNED NOT NULL DEFAULT '0' auto_increment,
Title string NOT NULL, -- The Title of the media -- A string.
Type int(5) UNSIGNED NOT NULL, -- references Types.id
Name string(20) NOT NULL, -- The name of the credit, example: Director.
Value string(20) NOT NULL, -- The value of the credit, example: Mel Gibson.
PRIMARY KEY (title,type)
);
CREATE TABLE FORMATS
-- Each Shared Item has only one format, so the id value of this table
-- is referenced by the Format field of Shared Items. This creates a one
-- to one relationship where the value is a controlled variable. In other
words,
-- Media can be in any one of the formats available in the list, or a new
format,
-- which would be added to the list.
(
id int(3) UNSIGNED NOT NULL DEFAULT '0' auto_increment,
value string(10) NOT NULL,
PRIMARY KEY (value)
);
CREATE TABLE CATAGORY
--This Table holds a list of catagory names, allowing catagories to also be
extendable.
(
id int(5) UNSIGNED NOT NULL DEFAULT '0' auto_increment,
value string(25) NOT NULL,
PRIMARY KEY (value)
);
CREATE TABLE CATAGORIES
-- Each Shared Item can belong to many catagories. The id field in this
-- table is a reference to the id field in the Media table. This is a many
-- to one relationship, meaning that there may be many references from this
-- table to each individual peice of media. In other words, any media that
-- is shared can belong to many different catagories.
(
id int(5) UNSIGNED NOT NULL DEFAULT '0' auto_increment,
value int(5) UNSIGNED NOT NULL, -- References Catagory
PRIMARY KEY(id,value)
);
CREATE TABLE PLACES
-- This is a list of places where sharing happens.
(
id int(10) UNSIGNED NOT NULL DEFAULT '0' auto_increment,
name string(25) NOT NULL, -- The Name given to this Sharing Zone.
address tinyblob, -- No need to store address details. Freetext will do.
PRIMARY KEY(id)
);
CREATE TABLE ZONES
-- The Zones Table is used to create a one to many relationship between
places and zipcodes.
-- The id field references PLACES.id
(
id int(7) UNSIGNED NOT NULL DEFAULT '0' auto_increment,
Owner int(7) UNSIGNED NOT NULL,
ZipCode int(7) UNSIGNED NOT NULL,
PRIMARY KEY(id,ZipCode)
);
CREATE TABLE BBS
-- The BBS Table is for a basic message system.
(
id int(10) UNSIGNED NOT NULL DEFAULT '0' auto_increment,
parent UNSIGNED NULL DEFAULT NULL, -- references the parent of this
message.
subject string(25) NOT NULL,
Message blob,
PRIMARY KEY(id)
);
CREATE TABLE CHATROOMS
-- Any User can start their own chat room.
(
id int(5) UNSIGNED NOT NULL DEFAULT '0' auto_increment,
Name string(25) NOT NULL,
Owner int(7) UNSIGNED NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE CHAT
-- This is where the actual chat messages are stored.
(
id int(10) UNSIGNED NOT NULL DEFAULT '0' auto_increment,
room int(5) UNSIGNED NOT NULL,
message tinyblob,
PRIMARY KEY(id)
);
Navigation:
[Reply to this message]
|