Database design help =/

[Locked]

OK i understand how to design a database apart from one of the key factors, Reducing repeat data and splitting up the feilds into their own groups.

 

The more reading i do the more i become confused. Its got to the point where my tables only have 2/3 feilds in them as "user_id" is the key for all the rest of the tables. But what im worried about is splitting them up to much and then having to many indexs/tables but at the same time im trying not to clump everything into one table.

 

Heres the scenario

 

user_account -> user_id,user_name,user_password,user_lastactive // Main table for user accounts

user_contact -> user_id,user_email // Email possibly meant to be in the account table?

user_access -> user_id,user_access,user_duties // For staff access, duties is a varcahr rest is med/small int

 

Theres a lot of tables that would around the same numbers of feilds. Basically asking is this right? and if its not where am i going wrong/the cut off point for repeat data etc?

[aschk]

You could merge the user_account and user_contact table, but only really in the following situations:

1) A user DOESN'T have mutiple email addresses.

2) You expect the majority of accounts to HAVE 1 email address.

 

Also, the same question really applies to the user_access table. Are you expecting to have a table with the user_id in it mutiple times. i.e. does a user have many access levels (e.g. 1,4,5) or is it just represented by a single number (e.g. 10).

 

I think what will help you is to identify the relationships. Write it out (in words).

e.g.

1) Each user has MANY email addresses, but MUST have at LEAST 1 = (1 -> many relationship, so 2 tables)

2) Each user MUST HAVE 1 access level set by an integer. ( 1 -> 1 relationship, so merge the tables)

 

Mull over it and write it out for yourself.

 

Otherwise I can't see anything glaringly wrong with what you have provided.

 

 

[Locked]

Thanks that has helped a whole lot, i now know when to split the tables etc.

 

Each user will only have one email so it can go in the accounts table but the access will only be for a handful of users so that table can stay put