Search code examples
ms-accessmany-to-manyrelational-database

MS Access Multiple Many-to-Many relationships

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

Solution

  • 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