MS Access Multiple Many-to-Many relationships

0
votes

I have one table "Liability" which is currently providing the available coverage amounts in a dropdown box. It has the following fields:

[CoverageAmountID]
[CoverageAmount]
[Cost]
[StateID]
[ProductID]
[CompanyID]

There are many coverage amounts, companies, states & products.

A complicated query currently pulls the correct Coverage Amount options. My question is if I need to split this up and how?

I have no relationships for this now but there are State, Product & Company Tables.

Do I create junction table with the CoverageAmountID, StateID, ProductID & CompanyID as the primary key?

Is there a better way to handle this with multiple m:m junction tables?

I'm struggling with how this data should be structured. The attached picture shows what I have in the table currently.

Liability Table

1
ms-accessmany-to-manyrelational-database
Are those multi-value fields I see? I NEVER use them. Or are those actually CSV strings? Not sure which is worse. Strict data normalization calls for junction tables. It is a balancing act between normalization and ease of data entry/output - "Normalize until it hurts, Denormalize until it works". - June7
I failed to mention that I have 13 other tables in my database that are structured this way. I would like to nomalize everything but not sure how to structure the data since they all have four tables involved with many records from each. - Josh_GAA
To get rid of the multi-value fields you need a State table with StateID's and a Company table with CompanyId's. Then use a junction table with just CoverageAmountID and StateID fields (you should make those both primary keys). So CoverageAmountID 1 will have 3 entries in your junction table. And one junction table with CoverageAmountID and CompanyID (again both should be primary keys) so CoverageAmountID 2 will have 2 entries in this junction table. - Jeffrey
Thanks Jeffery, Apart from eliminating the Multi-value fields is there a better way to normalize this data? - Josh_GAA

1 Answers

0
votes

Normalizing the liability database:

  • Instead of using many values in one field, use intermediary tables for many to many relationships
  • Better use numbers for keys, using text in relationships will be challenging.
  • If you have one product per coverage then you have a one to many relationship in which case you can use a direct relationship from CoverageTbl to ProductTbl
  • Yes, you need separate tables with own primary Keys for ProductTbl, StateTbl & CompanyTbl
  • Attached is a tentative design showing relationships, Note all IDs are of type number, Codes are of type text(5) database design/relationships