1

How do you model the situation where the (foreign) table to which you relate is dependent on a value in the (primary) table?

This is the case:

  • I have a table of Risks, and each Risk has a TreatmentType.
  • If the TreatmentType is 'Accept', the Risk must be linked to an item in the Persons table.
  • If the TreatmentType is 'Mitigate', the Risk must be linked to 1 or more items in the Controls table.
  • If the TreatmentType is 'Transfer', the Risk must be linked to an item in the Departments table.

I could add different FK-fields for Person, Control and Department, and implement a rule to fill only one of these dependent on the value of TreatmentType, but it seems kind of sloppy.

It's been a while since I made a design for a database, what is good practice for this?

Richard Kranendonk
  • 131
  • 4
  • Could a given Risk have links to more than one of Persons/Controls/Departments? If only one, then you have a (mutual) exclusive relationship [see wp]. _And_ the Treatment Type is a derived field/you don't need to hold it in the database. (It's the kind of thing users want to see on the screen, as a quick indicator.) – AntC Dec 21 '22 at 22:06
  • The user first selects the TreatmentType, then depending on the value the Risk can be linked to 1 person OR to 1 department OR to many Controls (these are exclusive ors). – Richard Kranendonk Dec 23 '22 at 09:40
  • btw, what do you mean by [see wp]? – Richard Kranendonk Dec 23 '22 at 09:41
  • [see wikipedia https://en.wikipedia.org/wiki/Exclusive_relationship_(programming)] The sequence of the user's conversation with the application does not determine the database structure: the database holds the user's input _after_ they've completed a whole logical (and valid) conversation. Or are you saying the User can choose Treatment Type = 'Accept' but then not supply any Person info, and it's valid to leave the data content 'dangling' like that? – AntC Dec 23 '22 at 10:42
  • Yes, dangling is permitted. But what dangles is still dependent on the choice of TreatmentType. Thanks for the insight on user conversation vs database status. I need to think this through because in my application it needs to be possible to have part of that conversation at one moment and finish it later. – Richard Kranendonk Dec 24 '22 at 11:53
  • Just found this article: https://stackoverflow.com/questions/2704655/how-to-model-a-mutually-exclusive-relationship-in-sql-server – Richard Kranendonk Dec 24 '22 at 12:03
  • Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Feb 25 '23 at 21:07

1 Answers1

1

This is a very tricky relationship to model, and there is no simple answer.

In UML you could represent this unambiguously with a generalization set to specialization of the risk response according to the treatment type, and some explicit constraints on the generalization set:

enter image description here

In traditional ERD, there is no solution, except writing down some comments. But you can use an enhanced ERD (EERD) to show a similar solution using IsA relationships. The only issue is that there there are several variants, the semantics are not clearly defined, and no authoritative source.

Whatever you chose for the modelling, there is no direct transposition into an SQL schema:

  • One common technique, if there are only a couple of treatments, and only a couple of diverging fields, is to use single table inheritance pattern. This consist of putting alt the fields needed for any treatment in a single table, and use a TreatmentType value to distinguish which fields are relevant.

  • An alternative technique is the class table inheritance, keeping the treatment type in the treatment table, and modelling a one to one relation with the three tables and programmatically ensuring that only one is used.

Christophe
  • 68,716
  • 7
  • 72
  • 138