Table Strucuture

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Table Strucuture

Post 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 |
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post 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.
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post 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
User avatar
Buddha443556
Forum Regular
Posts: 873
Joined: Fri Mar 19, 2004 1:51 pm

Post 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
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

This old thread discusses some of these issues:
viewtopic.php?t=2560&postdays=0&postorder=asc&start=0
Post Reply