Software

Ask something
Discussion Locked

Normalized Design Quandry

0 helpful
2 replies

by druid2112

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a table of options:
tbl_Options
Key Value
1001 "Power Steering"
1002 "Power Brakes"
1003 "Airbag"

I have a table of option combinations:
tbl_Models
Key Value
101 "Standard"
102 "Deluxe"
103 "Extra Snazzy"

I want a table(s) to relate them that DOES NOT LOOK LIKE THIS:
tbl_Combinations
Key Package Opt(3)
10 101
20 102 1001, 1002
30 103 1001, 1002, 1003

I want to normalize the combinations table into one or more tables that will accommodate 0-n (or at least 1-n) Options per Model.

I am not clever enough to figure this out but I know it can be done.

Any ideas?

2 replies

by glen_mcleod

Try this:
tbl_ModelOptions
Model_Number
OptionNumber

Your table will have a row for each combination:

Model Options
101 1001
102 1001
102 1002
103 1001
103 1002
103 1003

You can relate each of the columns back to the original table.

Glen

by burzinmk

Dear druid2112,
It seems like you have a many to many relationship between the options and the models table. In almost all many to many relationships, the entity relationship set which establishes a relationship between the two entity sets (nothing but tables) will be mapped to another table containing at least the primary keys of the two related entity sets. I think an example will make it clear.
Consider two tables
1) Student
2) Courses
The assumption here is that many student may register for many courses.

Now, the entity relationship set that relates these two tables will be mapped to a table containing at least the primary key of the student table and the primary key of the courses table. Some other attributes that are dependant on the relationship set can also be included.
The only problem here lies in identifying the many to many relationship. I guess this should clear some of your designing problems.

Burzin

This discussion is closed to new replies.