Creating new layout for tables and need advice...

[SimonSezz]

I'm converting an old access database to a new one and I have imported some tables. The old database had just one table for insurance plans, named "tblPlan". It had compound primary keys in which the set of the three fields (PlanType, PlanCategory, CoverageType) had to be unique. I want to do this the right way and I figure that would entail having a table with an autonumber ID that points to each PlanType, PlanCategory, and CoverageType. I'm just not sure what is the best way to go about doing this. Here is an image of the data in the old table:

As you can see there are multiple plan categories, and for each category there are multiple plan types, and for each category and plan type there are multiple coverage types. Now this is my solution so far using autonumber ID's:

Is this the correct way to do this or should I do it the way "OLD_tblPlan" is, where I added an autonumber field called "ID" and each unique set of PlanType, PlanCategory, and CoverageType have a unique ID number?

 

[georgedwilkinson]

Make "ID" your primary key. Remove the PK from the other 3 fields.

Put a unique constraint on the other 3 fields: In Access 2007, go into design mode on the table, click on Indexes (the lightning bolt in the Design ribbon), type in the name you want for the index (make it unlike other object names), select the first field name, and then the other 2 field names (one at a time) under that, and change "Unique" to "Yes". Save the table design.

I'm glad to see you're working toward a good design. So few people are interested in doing it right.

Good luck.

 

[Pat Hartman]

I would also suggest changing "ID" in each table to be the real key name like PlanCategoryID, etc. You'll drive yourself nuts if every table has the same key name. Also having the PK and FK have the same name gives you a visual clue that a field is in fact a FK. That would mean that the only time you use ID as a suffix is if it is a PK/FK.