Check Please.

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)
);

Re: Check Please.

sTony wrote:

> 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)
> );

This table doesn't do much at all, it just hogs up hard drive space.

CREATE TABLE ZipCodes
-- A list of ZipCodes and the area they cover
(
ZipCode string(7) NOT NULL,
AreaName string NOT NULL,
PRIMARY KEY(ZipCode)
);


Use better names for "id", is it's a users id, then use user_id in all tables
where it's the users id number. You can only have AUTO_INCREMENT for the id
only in it's main table, if you use it in another table too, then the column
can't have AUTO_INCREMENT.

When you create a relations table, then use two id's, and make those together
as a primary key, don't create a new id in a relation table, it's just waste
of space.

Don't normalize everything, look more on what you will need often together,
JOINS takes time, specially when you start to have much of data in your tables.

I guess you mean FORUM and not CHAT in your last tables.


I suggest you start all over again, begin with unnormalized tables, look at
what you have in your table what data that can occur more than once and break
it out and you don't always need a "relation table2 as some data like zipcode
you always have a one to one relation. Other times you save a lot of speed to
not normalize like zipecode and areaname, even if many zipcodes can have the
same area name, the name isn't really long, so you won't be waisting that much
space (space vs speed).


//Aho

Re: Check Please.

Thanks Alot, that really helps, but now I've got more questions.. surprise
surprise.

"J.O. Aho" wrote in message
news:4safbcFuo36pU1@mid.individual.net...
> sTony wrote:
>
> > 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)
> > );
>
> This table doesn't do much at all, it just hogs up hard drive space.
>
> CREATE TABLE ZipCodes
> -- A list of ZipCodes and the area they cover
> (
> ZipCode string(7) NOT NULL,
> AreaName string NOT NULL,
> PRIMARY KEY(ZipCode)
> );
>
>

Thanks, but actually, zipcodes are referenced in two tables. A user has only
one zipcode, but a place (a sharing zones service area) can have many. I
guess my zipcode tables don't really make much sence anyhow, but there was
reason to my madness. I'll have to work something out to get the
functionality I want.

> Use better names for "id", is it's a users id, then use user_id in all
tables
> where it's the users id number. You can only have AUTO_INCREMENT for the
id
> only in it's main table, if you use it in another table too, then the
column
> can't have AUTO_INCREMENT.

Sorry, I don't think I understand what you are saying. Better names for id I
get, but the rest, ??

> When you create a relations table, then use two id's, and make those
together
> as a primary key, don't create a new id in a relation table, it's just
waste
> of space.

Good point. Thanks again.

> Don't normalize everything, look more on what you will need often
together,
> JOINS takes time, specially when you start to have much of data in your
tables.
>
> I guess you mean FORUM and not CHAT in your last tables.

Actually, I want to have both. Sorry it wasn't clear.

>
>
> I suggest you start all over again, begin with unnormalized tables, look
at
> what you have in your table what data that can occur more than once and
break
> it out and you don't always need a "relation table2 as some data like
zipcode
> you always have a one to one relation. Other times you save a lot of speed
to
> not normalize like zipecode and areaname, even if many zipcodes can have
the
> same area name, the name isn't really long, so you won't be waisting that
much
> space (space vs speed).
>

Most of the normalizing I did was just to allow room for expansion. For
instance, the Formats table keeps track of known formats, ie: VHS, BETA,
DVD,
I used a seperate table so I could create select list of all available
formats, without having to know what they are before the site goes up. I
could kill that and several other tables, and just grab a unique list of
format names from the media table, but I thought that would take longer.
Even longer as the media table grows. Was I right about that? Is there
another way to keep a list that can grow? I want people to be able to add
new Formats, Catagories, Types, etc.

>
> //Aho
>

Thanks for the help.

sTony

PS.. I missed making a friends table, a small oversite on my part. Sharing
will only happen when a chain of friends can be formed. Thanks again.

Re: Check Please.

sTony wrote:

> Thanks, but actually, zipcodes are referenced in two tables. A user has only
> one zipcode, but a place (a sharing zones service area) can have many. I
> guess my zipcode tables don't really make much sence anyhow, but there was
> reason to my madness. I'll have to work something out to get the
> functionality I want.

IMHO you got to far in normalize the tables, you have to keep in mind that you
have to find a middle way.

I think you can manage well with one table even if you want to use "zones
service area", it's just add a column for that.


>> Use better names for "id", is it's a users id, then use user_id in all
> tables
>> where it's the users id number. You can only have AUTO_INCREMENT for the
> id
>> only in it's main table, if you use it in another table too, then the
> column
>> can't have AUTO_INCREMENT.
>
> Sorry, I don't think I understand what you are saying. Better names for id I
> get, but the rest, ??

Okey, it's a bit unclean described, so lets assume we have tables

User
User_id INT AUTO_INCREMENT
Name VARCHAR(40)

Password
User_id INT AUTO_INCREMENT
Secret VARCHAR(20)

This will not work when you register a new user, you can't insert safely
his/her password into the Password table. Then it's better to do

User
User_id INT AUTO_INCREMENT
Name VARCHAR(40)

Password
User_id INT
Secret VARCHAR(20)

In both table you still should have the User_id as PRIMARY KEY.



>> I guess you mean FORUM and not CHAT in your last tables.
> Actually, I want to have both. Sorry it wasn't clear.

I wouldn't use a database based chat, frankly I wouldn't use web based chat at
all, those tend to be slow, redirect users to an IRC server/channel instead,
you get a lot better chat there and users can choose a server which isn't
lagging for them and your site won't be affected by high loads. Of course not
everyone agrees with me.


> Most of the normalizing I did was just to allow room for expansion. For
> instance, the Formats table keeps track of known formats, ie: VHS, BETA,
> DVD,

For that part I don't object.


> I used a seperate table so I could create select list of all available
> formats, without having to know what they are before the site goes up. I
> could kill that and several other tables, and just grab a unique list of
> format names from the media table, but I thought that would take longer.
> Even longer as the media table grows. Was I right about that?

Thats right and the list of different media types most likely will be used
quite often, and in this case an own table is useful.


> Is there
> another way to keep a list that can grow? I want people to be able to add
> new Formats, Catagories, Types, etc.

I would be a bit conservative on allowing people to add too much without
moderation, or else you will end up with many categories/types that really are
the same but as different persons describes/names in different ways.

For example: jpeg and jpg


//Aho

Re: Check Please.

> I wouldn't use a database based chat, frankly I wouldn't use web based
chat at
> all, those tend to be slow, redirect users to an IRC server/channel
instead,
> you get a lot better chat there and users can choose a server which isn't
> lagging for them and your site won't be affected by high loads. Of course
not
> everyone agrees with me.

The reason for the chat is want to experiment with uncommon chat features.
Things like, automatic people filtering/selection, so that people are
grouped together according to their interests, and also a sort of democratic
moderation system, where if enough users say "Kick that User" the user is
kicked. Things like that. However, those things are not really important to
this site, and your comment about lag seems much more important. So, I think
I'll use an IRC chat for now, and do an upgrade later, when I've got my
chat-app working well on it's own dedicated server. Yeah, thats a better
idea. I can use the same chat server for all my sites. However, I've never
done an IRC chat, and I don't have any idea on how to proceed. Any help
would be appreciated.

sTony





> > Is there
> > another way to keep a list that can grow? I want people to be able to
add
> > new Formats, Catagories, Types, etc.
>
> I would be a bit conservative on allowing people to add too much without
> moderation, or else you will end up with many categories/types that really
are
> the same but as different persons describes/names in different ways.
>
> For example: jpeg and jpg
>
>
> //Aho

Re: Check Please.

sTony wrote:

> The reason for the chat is want to experiment with uncommon chat features.
> Things like, automatic people filtering/selection, so that people are
> grouped together according to their interests, and also a sort of democratic
> moderation system, where if enough users say "Kick that User" the user is
> kicked. Things like that.

Much of those things you can do with a irc-bot and there are loads of nice
scripts written to one of the best bots, eggdrop.


> I've never
> done an IRC chat, and I don't have any idea on how to proceed. Any help
> would be appreciated.

first of all, you need a IRC client, there are quite many and they have their
goods and bads. X-Chat is one of the most popular I guess, it's quite easy to
use, BitchX is another popular IRC client, that one work fine in a terminal.

Next you have to connect to a IRC server, there are quite many
IRC-server-networks and many of them are listed by default in the IRC clients
lists (at least X-Chat has a load of them). Assuming you pick X-Chat, just
select one and connect.

At this point you have to choose a channel to join

if you type:
/list

you get a list of public channels on the network

to join one, type

/join #channel_name

If there aren't a such channel, a new channel will be created and others can
join it (until you change settings for that channel), the channel will
disappear when the last user leaves it. There are networks which gives you
access to a Chan-server, it's really a bot that keeps your channel and your
settings alive while there aren't any users.

Gosh, there are so much that you can do, that I will just redirect you to a
HOWTO: http://tldp.org/HOWTO/IRC/
http://www.irchelp.org/irchelp/irctutorial.html

Those two should give you a good understanding about IRC, if you see some
windowz-nerds talking about mirc, they usually mean IRC but are thinking that
IRC is called mirc as their IRC-client is called mirc.


//Aho

Re: Check Please.

> Gosh, there are so much that you can do, that I will just redirect you to
a
> HOWTO: http://tldp.org/HOWTO/IRC/
> http://www.irchelp.org/irchelp/irctutorial.html
>
> Those two should give you a good understanding about IRC, if you see some
> windowz-nerds talking about mirc, they usually mean IRC but are thinking
that
> IRC is called mirc as their IRC-client is called mirc.
>
>
> //Aho
>

Thanks, I've saved your message for later. I'm writing the chat page last,
and I'll check out those links then.

sTony

PS. I'm a windowz-nerd. Still to scared to switch to linux.