0

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.

nbk
  • 45,398
  • 8
  • 30
  • 47
Tokkat
  • 3
  • 1
  • b makes sense as you have there the connection between bith tables – nbk Apr 27 '20 at 09:34
  • 1
    *I also want to specify one main stakeholder per product.* This (primacy) is an attribute of a link. So variant (b). – Akina Apr 27 '20 at 09:35
  • There is both an art and a science to relational database design. There are design patterns that are commonly used and useful, and this is one of them. This is not a simple question of one person's opinion vs another, as there are relevant queries required with each of the 2 different approaches answered. I also think that it says a lot that @symcbean answered the question, found people disagreed, then voted to close it later. I don't see how doing things like this, make SO a useful and evolving resource. If his answer has merit others might find it and upvote it in the future. – gview Apr 30 '20 at 22:40

2 Answers2

0

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.
gview
  • 14,876
  • 3
  • 46
  • 51
  • Triggers are a very innefficient way of applying the logic here. – symcbean Apr 27 '20 at 13:25
  • @symcbean You would have to explain what you mean by that comment. The triggers would only be used to disallow a row to be added or updated to isMainStakeholder = 1 if there already exists another stakeholder with isMainStakeholder = 1. That would not be inefficient by any means. – gview Apr 27 '20 at 19:01
0

Option b does not ensure that there is only a single "main stakeholder". I suppose it is possible to create a functional index/virtual column on the product2stakeholder table which do this which would reduce maintenance effort, but this lacks transparency. (I'm deliberately not explaining how to do this on the grounds that if an explanation is required that would be proof that this is not the right solution for you).

I would go for option A but its really a matter of opinion.

symcbean
  • 47,736
  • 6
  • 59
  • 94