Search code examples
mysqlrelational-model

Model N-M relationship with additional info of "main" row

I have a question regarding how to best model a relationship in relational database (I'm using MySQL):

I have an entity "Product" that should be linked to one or more stakeholders. I modelled that relationship with a separate table "Stakeholders" that contains the names of the stakeholders and a linking table "Product2Stakeholder" that links products and stakeholders.

However, I also want to specify one main stakeholder per product. I'm wondering if to do that, I should

  • a) define a foreign key field in the "Product" table that contains the Id of the main stakeholder from the "Stakeholders" table (e.g. "MainStakeholder"), or
  • b) add a column to the "Product2Stakeholder" table containing the info if the stakeholder is the main stakeholder for that product (i.e. a boolean column "IsMainStakeholder"), or
  • c) take an altogether different approach?

Thanks.

Solution

  • First a convention suggestion. When you have a many to many resolver which in your case is "Product2Stakeholder" then just name that table ProductStakeholder. Adding the '2' is amateurish and just makes your table name longer.

    You should implement option b. This always allows you to get the stakeholders for a product the same way by querying ProductStakeholder either with the criteria of the isMainStakeholder attribute = 1 or not.

    Otherwise, you don't even have the primary stakeholder available in the ProductStakeholder table, which should tell you that would be a mistake.

    Enforcing the business rule in the Database

    To address the issue of a type of constraint, you could add before insert and before update triggers that would disallow having more than one isMainStakeholder rows for a product.

    With that said, you will still have to write procedural code of some type to add or change an isMainStakeholder, and if you have those types of triggers, while they protect you, you also have to process changes in order:

    1. set current isMainStakeholder flag to 0.
    2. set new isMainStakeholder flag to 1 for other Stakeholder.