Posted by J.O. Aho on 11/20/06 16:06

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

[Back to original message]