Three-sided many-to-many relationship design

database-designmany-to-many

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.
    Solution 1
  • 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?

Best Answer

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.

Related Question