Page 1 of 1

Table Strucuture

Posted: Mon Jun 21, 2004 6:32 pm
by hawleyjr
My two examples are probably similar enough where it doesn't matter but I would like to know how you would do it.

I am creating a table for logging users in and storing user information. Is it best to use 1 table (Option A) or two tables (Option B) with a shared key? I see advantages and disadvantages with both.


User_Table

Option A: 1 table

Code: Select all

| ID | USER_NAME | PASSWORD | FNAME | LNAME | PHONE | EMAIL | DTTM_LAST_LOGIN | STATUS | LOGIN_ATTEMPTS | DTTM_ADDED |
Option B: 2 tables:

TABLE 1:

Code: Select all

| ID | USER_NAME | PASSWORD | LOGIN_ATTEMPTS | DTTM_LAST_LOGIN | STATUS |
TABLE 2:

Code: Select all

| ID | FNAME | LNAME | PHONE | EMAIL | DTTM_ADDED |

Posted: Mon Jun 21, 2004 11:22 pm
by kettle_drum
Try reading this: http://www.serverwatch.com/tutorials/ar ... hp/1549781

And search more on google/read a textbook about database normalization.

Posted: Tue Jun 22, 2004 6:19 pm
by hawleyjr
Thank you for your reply and a good beginner’s reference to db normalization. My question, and obviously I wasn't specific enough; is all cells are dependent upon the "ID" key. Does it make more sense to have a second table solely for User name, password and login information and another for contact information? I am concerned about consistency and security.

Thanks.

-J

Posted: Tue Jun 22, 2004 11:08 pm
by Buddha443556
Here another DB Normalization article: http://www.databasejournal.com/sqletc/a ... hp/1428511
I am concerned about consistency and security.
Are you using a DB that does not allow you to grant/revoke column privileges? MySQL DB has a fine grain access control system down to the column level.

http://dev.mysql.com/doc/mysql/en/Privileges.html

Posted: Wed Jun 23, 2004 12:52 pm
by nielsene
This old thread discusses some of these issues:
viewtopic.php?t=2560&postdays=0&postorder=asc&start=0