Three Entity Intersection Table "many-many-many"

Question

I'm currently working on a data model design (which will be backed by an RDMS). It is based around 3 entities relating to benefits for a member of the organization. The entities are the Benefit, Membership Type, and Provider. I've created a standard many-to-many intersection table to relate two entities, but never 3. I'm wondering if anybody has done such a thing and if there are any potential pitfalls I should keep out for. The table woudl be like the following:

create table BENEFIT_MEM_TYPE_PROVIDER
(
   BENEFIT_ID reference BENEFITS not null,
   MEMBERSHIP_TYPE_ID reference MEMBERSHIP_TYPES not null,
   PROVIDER_ID reference PROVIDERS not null,
   primary key (BENEFIT_ID, MEMBERSHIP_TYPE_ID, PROVIDER_ID)
)

Something about this relationship just doesn't sit right with me, so I thought I'd ask the smart folks for any advice.

Thanks

Answer 1

anybody has done such a thing

Yes.

if there are any potential pitfalls I should keep out for.

This can lead to issues if there are relationships among Benefit-Membership, Benefit-Provider and Membership-Provider that might appear true from data in this table, but shouldn't actually be true.

For example, a Member-Provider-Benefit might have a "restriction" that the Benefit only applies to the Member when a specific Provider is involved. It's possible that the relationship isn't generally true, but is true as a special case because of some qualification.

In this case, you'll need to carry that qualification in this table to be sure that these exceptional cases can be discovered properly by simple SQL queries.

In short, when you have more than 1 relationship, be sure the pair-wise relationships in each row are also true.

Answer 2

You should verify whether your table satisfies Fifth Normal Form (5NF). If it does not then it's probably better to normalize it so that it is in 5NF.

Answer 3

Yes.

Ternary relationships are quite widely used, although not as widely used as binary relationships. This can even be extended to quaternary or in general n-ary relationships.

One place where you can see n-ary relationships in database design is star schema. The fact table at the center of a star is an n-ary relationship, where n is the number of dimension tables referenced by the fact table.

The normalization process consists of detecting departures from some normal form, and decomposing tables in order to yield a more normalized equivalent. Decomposition sometimes results lowering the order of n-ary relationships.

Star schema deliberately goes the other way. That is why star schema and normalization are so frequently seen as being at odds with each other.