Page 1 of 1

database design > mebers table vs login table

Posted: Mon Jul 03, 2006 2:39 pm
by bogdan
Hi, I am new to this, PHP, JScript, database design... you get it.

I have question about database design:

* I will have a general area (index) from which a visitor will be able to:
-- login
-- register

REGISTER:
nickname
password
password confirmation
Firstname
Lastname
etc. ..........

Will it be best to make 2 tables:
table 1 - will hold the nickname and the password
table 2 - will hold the rest of the data
or
have only 1 table that will hold both the nickname, password, firstname, lastname, etc. ?

Regards, B

Posted: Mon Jul 03, 2006 2:42 pm
by Burrito
it makes sense to just have one table (assuming you're not going to have multiple types of information (ie several phone numbers or email addresses etc) per user).

so you may have a user table with: username, password, location, phone, emailaddress etc.

Posted: Mon Jul 03, 2006 5:09 pm
by John Cartwright
I personally like to have their information and their account details seperate, as you won't always need their details when performing account actions.

Posted: Mon Jul 03, 2006 6:55 pm
by Luke
I would make two tables. Say you have this set up:
Nickname, Password, First name, Last name, phone, address, zip

Now you need to set up something that describes where they work... you'd have to add that also to the table
Nickname, Password, First name, Last name, phone, address, zip, work_phone, work_address, work_zip
This way, you can infinately add information about users.

Posted: Mon Jul 03, 2006 6:56 pm
by Luke
The Ninja Space Goat wrote:I would make two tables. Say you have this set up:
Nickname, Password, First name, Last name, phone, address, zip
Now you need to set up something that describes where they work... you'd have to add that also to the table
Nickname, Password, First name, Last name, phone, address, zip, work_phone, work_address, work_zip
Now you need to set a signin time table for them
Nickname, Password, First name, Last name, phone, address, zip, work_phone, work_address, work_zip, signin, signout
So, just to make it easier in the future, I would do this:

USERS:
id, username, password

USER INFO:
id, First Name, Last name, phone, address, zip, user_id

USER'S WORK INFO
id, work_name, work_phone, work_address, work_zip, user_id

USER TIME LOG
id, sign_in, sign_out, user_id
This way, you can infinately add information about users.

Posted: Mon Jul 03, 2006 6:57 pm
by Luke
LOL the above post was the result of hitting "quote" instead of "edit"

Posted: Mon Jul 03, 2006 8:20 pm
by Burrito
unless, as I said above, you're going to have information types that could potentially be duplicated, I see no reason to create separate tables for user information. You're not gaining a normalization advantage doing it that way either.

Posted: Tue Jul 04, 2006 2:42 am
by GM
I agree - no need for separate tables here. I think one would suffice, unless - as Burrito has already said - you require to store multiple instances of the same data item for each user (ie: several phone numbers, several email addresses etc.)