Posted by sTony on 11/20/06 15:33

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

"J.O. Aho" <user@example.net> 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.

[Back to original message]