Three-sided many-to-many relationship design

Question

I have 3 entity that have a relation to each other. My entities are:

1. OPTION
2. PLAN
3. PRICE

Relationships:

• PLAN and OPTION have a many-to-many relationship
• Map of PLAN and OPTION has a many-to-many relation with PRICE

any plan has some options. an option of one plan has some price.

My possible solutions:

• I can create an PLAN_OPTION_MAP table and then map it with PRICE and create PLAN_OPTION_MAP_PRICE_MAP table.
• I can create an PLAN_OPTION_PRICE_MAP table with id of all 3 tables that primary key is made by composite of them

Which is the better solution?

Answer 1

I think the first solution is good when you know that some instances WOULD NOT participate in relation. But what about second solution? I think it's good when you're sure that all of the instances WOULD participate in relation. The first thing you can do, is analyzing your Problem.

Answer 2

You can have any combination of Plan and Option. Fine. Now here's the question: does a given combination of (P,O) have a given Price? As the three-way intersection table is one of your answers, that would seem to be the case.

Perhaps it would be clearer to think of it like this. An intersection table has two keys that relate to (generally) two different tables which defines a many-to-many relationship between the entities represented by those tables. Any additional fields defined in the intersection table are attributes of the relationship itself, not of either of the entities. For example, the date the relationship was formed.

In your case, price would seem to be an attribute of the relationship (P,O). ("The combination of this Plan with this Option has a price P.") This could, of course, be a FK reference of its own, but that makes me wonder about the makeup of a Price row. Price would seem to be a fairly atomic value.