Join three or more tables in many to many link

[elicoten]

(Bold is table names)


I have a database containing details of establishments, which have branches. The database also stores contact details. The complication is that there is a many to many link between contacts to both branches and establishments. That is, both Establishments and Branches can have Contacts,and there must be a many to many link between these tables.

A similar example, but involving four tables in the same database concerns Enquiries/Questions. The system also stores details of Products, as well as Certification Authorities who certify Products as well as Policies, which are separate. The system can accept questions which relate to any combination of Certification Authorities, Products and Policies, so I need a many to many link between Questions and any combination of Products, Policies and Certification Authorities

My question is, what is the best way to deal with such a setup? I already have tried something which seems to work but I'd like to see what the best/correct way to do this is.

Thanks for your advice,

Eli

[NassauBob]

You are headed for trouble with many-to-many relationships, my friend. I'd suggest a small table which would instead have one-to-many relationships to the others, instead.

For every Establishment, there are 1 or more Branches, right? So do a one-to-many for that relationship.

And if you relate Contact to Establishment, rather than to Branch, then the relationship is the same: For each Establishment, there are one or more Contacts.

Maybe like this?:

Contact >------ Establishment ------< Branch, with EstabID common in all tables?


Would that work?

[elicoten]

Yeah, that will work and that is what I've done, but what about the fact that Branches also have Contacts?

Contacts link to both Branches and Establishments in addition to the fact that Establishments have Branches, so what's missing from that diagram is the fact that Branches have Contacts.

Thanks very much for your assistance.

[NassauBob]

True, but can not a Contact be directly related to Establishments, and indirectly (through Establishment) related to Branches?

Am I interpreting your situation correctly?