Topic awaiting preservation: DB normalization (Page 1 of 1)

Also posted over at the GN. Wanted to see the various responses

I have like 15 fields... only 4 of those would really be able to repeat data... I think... anyhow, so would I make 5 tables?

Can someone help me understand how they should be laid out, so that they will be in 3rd normal form... I'm having a hard time getting it...

The fields are

real name
nickname -> will go into it's own table with a id and pri key
usr_name -> I thought maybe the usr_n/pass could have it's own...
usr_pass
age
sex -> repeating data
state/province
relationships -> will be one of 4 things, so I can see this needing a table
email
icq
aol
msn
photo
about
testimony

I've been reading, just having a hard time grasping this... and Emp's got me wanting to do it this way... which is awesome, and gives me a lot more flexibility... I didn't realize it until I had thought about it... but that was on a smaller scale, this here is a lil more, and well I'm pulling a lil hair out

thanks guys

/*
Emps reminded me of what I forgot, he flat out rocks!
*/

[This message has been edited by AT (edited 08-30-2002).]

AT: I'm sure flat out rocking can't be good for one's health From what you've said and depending on your plans I'd do something like:

MAIN TABLE
user_id
real name
nickname
usr_name
usr_pass
age
sex
state/province
icq
aol
msn
about
testimony

EMAIL TABLE (if you wanted to allow people to provide more than 1 email address)
user_id
email

RELATIONSHIP TABLE (not sure about this one but if there were a number of other fields connected to the realtionships it might make sense - if it is married, single, etc. then put this in the main table)
user_id
relationships
other_fields?

PHOTO_TABLE (would allow you to have more tan one photo - you could set up a nice randonising script to display them)
user_id
photo

So that is (on the information I have) the maximum number of tables I'd go for and depending on what you want to do you could probably get away with having all of these in one table. It depends on how flexible you want your email and photo fields/tables to be.

___________________
Emps

FAQs: Emperor

And maybe pull the icq, msn, email fields out into a CONTACT table with these fields

user_id
contact_type
contact_id

so that when a new contact type comes out (yahoo, irc?) they can be added without altering the table structure

if I may...

rockin is healthy, and ah you do bro

thanks to both of you