2

In the Table DEVICE I have a TYPE, if the TYPE is HEATER then make a relation to the HEATER table, else if the TYPE is LAMP make a relation to the LAMP table. Is something like this possible to do?

Example

@MikeNakis so this should not be possible:

Device table:
+-----+----------+----------+
| Id  | LampId   | HeaterId |
+-----+----------+----------+
|   1 | NULL     | 1        |
|   2 | 2        | 2        |
|   3 | 3        | NULL     |
|   4 | NULL     | 4        |
+-----+----------+----------+

Lamp table:
+-----+----
| Id  |
+-----+----
|   2 |
|   3 |
+-----+----

Heater table:
+-----+----
| Id  |
+-----+----
|   1 |
|   2 |
|   4 |
+-----+----
x0tester0x
  • 49
  • 7
  • Many examples here on SO, search for `subtype` / `supertype` https://stackoverflow.com/questions/1722741/defining-a-one-to-one-relationship-in-sql-server/1723519#1723519 https://stackoverflow.com/questions/4050784/defining-multiple-foreign-keys-in-one-table-to-many-tables/4051523#4051523 – Damir Sudarevic Mar 23 '20 at 10:26
  • In your example both `heater.ID` and `Lamp.ID` are `PK` *and* also `FK` to `device` – Damir Sudarevic Mar 23 '20 at 10:31

1 Answers1

0

Yes, it is possible to do this.

That's one of the ways (the best way in my opinion) in which Object-Relational-Mapping (ORM) frameworks work. And this is not a coincidence, because what you are trying to implement here is an object-oriented inheritance hierarchy mapped onto a relational database. The "Device" is the base class, while "Lamp" and "Heater" are the derived (descendant) classes.

Here is how to do it:

Get rid of the Type column. As I will show further down, it will become a computed column.

Instead of the Type column, introduce a couple of so-called "descendant-id" columns: a HeaterId column and a LampId column. Add a constraint, if you wish, that only one of those descendant-id columns is allowed to be non-null. And if you add more devices later, you will have to add more descendant-id columns.

Then, you can make the Type be a computed column, based on which one of the descendant-id columns contains a non-NULL value.

The bad news is that you are not going to be able to set the value of the Type column, but that's okay; instead of setting Type, you will be implicitly defining the type by storing a non-null value into one of the descendant-id columns.

Note: in object oriented programming, it is very rare to design a base class so that it has any knowledge whatsoever about its descendants. Some might even say that it is preposterous to have such knowledge. I would not be so absolute, I have come across examples of small, tightly knit, non-extensible class graphs where such knowledge is legitimate and useful to have. But in any case, even if you are a strict adherent to the maxim that says that a base class should never know anything about its descendants, this is not exactly object-oriented programming: this is relational database design. And this is the price you have to pay in order to map a class graph onto a database.

Further clarifications:

First, some terminology: a "long row" is a pair of parent (base/ancestor) row plus descendant row, examined together. A "row fragment" is only one of those, examined in isolation.

Every single descendant row fragment must have the exact same primary key id as its parent row fragment. So, here is an example structure:

Device table:
+-----+----------+----------+
| Id  | LampId   | HeaterId |
+-----+----------+----------+
|   1 | NULL     | 1        |
|   2 | 2        | NULL     |
|   3 | 3        | NULL     |
|   4 | NULL     | 4        |
+-----+----------+----------+

Lamp table:
+-----+----
| Id  | 
+-----+----
|   2 | 
|   3 | 
+-----+----

Heater table:
+-----+----
| Id  | 
+-----+----
|   1 | 
|   4 | 
+-----+----

What this means is that in the base row fragment, the one descendant-id which is not-null always has the exact same value as the primary key of that same row.

This way, besides being very easy to troubleshoot, has an additional benefit of performance: inserting an entire long row into the database does not require any additional round-trips to the database: you use a sequence or an auto-increment value to issue just one id, and this is the only id that you are going to need for all row fragments that participate in the insertion. This works like a charm even if your inheritance hierarchy is deeper than just two levels.

Mike Nakis
  • 56,297
  • 11
  • 110
  • 142
  • But for example: I have an entry with the ID 3 in the DEVICE table and one entry with this ID as foreign key in the HEATER table, I want then that it's not allowed to insert the same ID also in the LAMP table... Can this achieved with this approach? – x0tester0x Mar 23 '20 at 13:02
  • You can add a constraint, if you wish, that every id in a descendant table must also be found in the corresponding descendant-id column in the parent table. This constraint, along with the constraint that requires that only one of the descendant-id columns is allowed to be non-null, should guarantee that your structure is okay. – Mike Nakis Mar 23 '20 at 13:51
  • However, ids should not be randomly inserted in descendant tables; an insertion or deletion to a descendant table should always be accompanied by a corresponding insertion or deletion in the parent (base/ancestor) table. Therefore, the id to use should always be clear. – Mike Nakis Mar 23 '20 at 13:51
  • (See the "Further clarifications" that I added to my answer) – Mike Nakis Mar 23 '20 at 13:52
  • I don't know how to format this here in the comment section.. so i added it to my question... – x0tester0x Mar 23 '20 at 16:33
  • As I have written twice above, if you are concerned about this happening, then add a constraint enforcing that only one of the descendant-id columns be non-null. – Mike Nakis Mar 23 '20 at 17:17
  • Is this database design a table per type inheritance? – x0tester0x Mar 23 '20 at 18:19
  • Yes, that's table-per-type as opposed to table-per-hierarchy. – Mike Nakis Mar 23 '20 at 18:25