Jump to content

Database design help =/


Locked

Recommended Posts

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?

Link to comment
Share on other sites

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.

 

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.