design question

[kris9]

Hi,


I need help with creating database design structure i've create is correct..


DB is here if anyone can look at it.


https://docs.google.com/file/d/0B2_U...it?usp=sharing




DB is for Migration tracking of Xp to Windows 7.


- Level 1 to 7 are different buisness units users fall under.
- Exclusion is to track if a user falls under a exclusion category. One user may or may not have an exclusion. May have more than one exclusion.
- Comms is to track when (date) a type of communications email is sent to a user.
- One user may have more than one workstation




should I be creating different one-to-one relationships tables or combine all into one user table as there are too many fields ?

[kris9]

hi can anyone assist

[June7]

Bumping your thread just took it out of the 'Unanswered' list. Many members scan that list and might ignore threads with replies. I had tried to download your file but got an error. I planned to try again tonight

[kris9]

I just tried the link and works fine

[rzw0wr]

Kris, You need a little work on your tables.
First go to every table and change the PK to an autonumber.
Then look your structure over and decide what it is about.
Users or work stations?
Do you need information about the users?

Hide all the tables but the most important one. Look it over until it is correct. Read normalization if needed.
Remove all data from the table that is repeated. One bit of data stored one time. Like level. New table needed.

Then add the next important table and go through it to normalize. Continue until all of the tables have been normalized THEN think about relationships.
In your db all of your relationships look to be 1 to many. NONE should be 1 to 1 as I see it.

Post back often with questions.
If you do not get your tables correct in the beginning you will have trouble with this database forever.

You have put a lot of work into a database that is in bad shape as it stands now.

Dale

[June7]

I could not download at work, blocked.

Whether or not the 1-to-1 relationships are valid depends on nature of data. For example, will each LanID always have a single related record in tblMigrationSteps? If so, why not just put those fields in tblUsers? Same question for tblAppReadiness and tblSurveyResponse. I expect each LanID can have multiple communications so tblCommsTracker should be set up like tblMigration with LanID as a foreign key, not primary key.

This does look more like tracking computers. The user information maybe helps tell you where the computer is located but you really don't care about the users. The user could go to another job in the company but the computer stays put.