The ever dreaded many to many

[Charmed7]

I can't quite get my head wrapped around this, so any help will be appreciated. Here's my set up.

Every table has an AutoNumber Unique ID to make things simple.
This is about insurance information for a company that owns a lot of property.
There are other tables in my database that I am not mentioning because they do not affect my many to many drama. So if it looks incomplete, it (hopefully) isn't and I just didn't mention it.


Tables are:

tblBuilding
BuildingID
BuildingName


tblPolicy
PolicyID
EffectiveDate
ExpirationDate

tblPolicyCoverage
PolicyCoverageID
CoverageType
PolicyID

tblCoverageInfo
CoverageInfoID
PolicyCoverageID


True Statements about this information:

A "Policy" has many "Policy Coverages"
A "Policy Coverage" has many "Coverage Info"
A "Building" has many "Policy"
A "Policy" has many "Building"

So my Building/Policy relationship is the many to many relationship. So I want to create a middle table which will have a New ID, and the Building ID and the Policy ID. I think.

Here is my confusion. Do I create a middle table to connect the building to the policy? Or do I connect the building to the policy coverages? or do I connect the building to the Coverage Info? Because it's true that a building has many policies, many coverages and many coverage info. I'm thinking I put it at the policy level but I'm not 100% on that.

I fear I won't get this answer without hours of trial and error and I don't have hours being this is just a side thing for one client because they own like 200 properties. If you need more info, let me know. Any suggestions, much appreciated. Thank you.

 

[Reader]

Hi there,

well, for any many to many relationship between two tables you need to break them into two one to many. so basically you need to create a new table (junction table) between TblPolice and tblbuliding.

this is how i would do it...

all the best!!!

 

[The_Doc_Man]

I would approach this as follows:

1. Table of Policies by ID number

2. Tables of Buildngs by Address but having unique ID number

3. Tables of possible coverage elements with unique ID numbers.

4. Big friggin' junction table consisting of a policy ID, building ID, and coverage ID.

The idea is that if there is a (Policy, Building, Coverage) record then the named policy provides the named coverage for the named building. I don't see the need (at the moment) to separately list the buildings covered by a policy or the policies that cover a building in a separate table because you can do "GROUP BY" on that 4th table to show the policies, buildings, or coverages depending on what you are reporting.

 

[Charmed7]

I get the big friggin' junction table thingy - sort of. I just want to put this one last thought out there to make sure I'm still going in the right direction.

Here's my scenario with the same tables mentioned above.

Let's say my client has 10 Buildings (tblBuilding)

Policy # 1234 (tblPolicy)
Coverage: Property and General Liability (tblPolicyCoverage)
Coverage Info: (tblCoverageInfo)
Building 1 - Property $500
Building 1 - General Liability $200
Building 2 - Property $200
Building 3 - General Liability $20


Policy # 4567
Coverage: Inland Marine
Coverage Info:
Building 1 - Inland Marine $300
Building 4 - Inland Marine $500


Policy can have many Coverages and Coverages can have many Informations.

Should I put all four tables into the junction table? Geez, I just confused myself some more. I'm gonna keep looking at it.

Thanks again, sorry for beating a dead horse.

Charmed

 

[The_Doc_Man]

No, not same table.

tblBuildings
BldID - Autonumber for Prime Key is as good as another
Address, owner name, etc.

tblPolicies
PolID - again, Autonumber PK works
Policy agent
Policy holder
etc. (NOTE: This table can be further normalized but I'm focusing on the other question right now...)

tblCoverages
CovID - Autonum PK works

the junction table has

BldgID
PolID
CovID

If the CovID just says things like Fire, Flood, Theft, Storm, Terrorist, Mold, etc. then the amount of the exact coverage goes in this junction table, too.

Then base reports on the junction and group by buildings to see how a building is covered and by what policies. Group by policies to see what buildings are covered etc. Group by coverages to see who has what type of coverage where. Use parent-child relationships to look up details of policy, building, or coverage. Read up on this using Access Help and search this forum for ideas on relationships.