Foreign key parametred to tackle different tables

Question

My context

Any node has a support....but this support can be a wall or a pylon (actually there are about 7 different types of support)

Important: the column type_support is saying which table to tackle (wall or pylon). You can see it as a parameter for "parametred polymorphism".

• The id_wall starts from 0 till n walls.
• The id_pylon starts from 0 till n pylons.

Database structure:

                              +-------------+
                              |    wall     |
+-------------+            PK +-------------+
|    node     |      |--------|id_wall      |        Legend:
+-------------+      |        +-------------+        PK means primary key
|id_node      |      |                               FK means foreign key
|type_support | FK   |        +-------------+
|id_support   |------|        |   Pylon     |
+-------------+      |     PK +-------------+
                     |--------|id_pylon     |
                              +-------------+

The code (the code between stars (*) is naive/stupid):

CREATE TABLE node 
(
    id_node      INTEGER NOT NULL PRIMARY KEY,
    type_support INTEGER NOT NULL,
    id_support   INTEGER NOT NULL,
    FOREIGN KEY (id_support) REFERENCES *The_right_support_table(id_the_right_support)*;

How to do that ?

Edit: I currently use SQLite - later it will be PostgreSQL.

Conclusion (thanks to detailed @Schwern answer):
In my case, the solution i keep is :
"You can have a support table and then specialized tables."

Answer 1

You have several options.

---

You can do what you've done, have an ID column and a type column. This is how Rails does polymorphism.

A downside is the lack of referential integrity. Instead of using foreign keys, you can enforce this with triggers.

Another downside is common support data must be duplicated across all support tables. For example, supports might all have a "height" so every "support" table must have a height column. Querying these common attributes becomes difficult. You can make up for this with a view.

create view node_support as
  select
    node.id,
    support_id,
    support_type,
    case when pylon.id is not null then pylon.height
         when wall.id is not null then wall.height
    end as height,
    case when pylon.id is not null then pylon.max_load
         when wall.id is not null then wall.max_load
    end as max_load
  from node
  left join wall on node.support_type = 'wall' and wall.id = node.support_id
  left join pylon on node.support_type = 'pylon' and pylon.id = node.support_id

Demonstration.

---

If your database supports it, you can use table inheritance.

create table support (
  id serial primary key,
  height integer,
  max_load integer
);

create table wall (
  color text
) inherits(support);

create table pylon (
  shape text
) inherits(support);

create table node (
  -- Referential integrity must be handled with a trigger.
  support_id integer not null
);

You can easily query all supports, or only walls, or only pylons.

Table inheritance has caveats, and it is Postgres-specific.

The biggest caveat is that if you try support_id integer references support(id) that does not work for walls or pylons. So you're back to enforcing referential integrity via trigger.

Demonstration.

---

You can make a join table for each type of support.

create table wall (
  id serial primary key,
  height integer,
  max_load integer
  color text
);

create table pylon (
  id serial primary key,
  height integer,
  max_load integer
  shape text
);

create table node_wall (
  node_id integer not null references node(id),
  wall_id integer not null references wall(id)
);

create table node_pylon (
  node_id integer not null references node(id),
  pylon_id integer not null references pylon(id)
);

The downsides are...

• N join tables for every type of thing which wants a support
• Common support columns must be maintained between tables.
• Each node having only one support has to be enforced with triggers.

You can create a view to query a node and its "support".

create view node_support as
  select
    node.id,
    coalesce(pylon_id, wall_id) as support_id,
    case when pylon_id is not null then 'pylon'
         when wall_id is not null then 'wall'
    end as type,
    case when pylon_id is not null then pylon.height
         when wall_id is not null then wall.height
    end as height,
    case when pylon_id is not null then pylon.max_load
         when wall_id is not null then wall.max_load
    end as max_load
  from node
  left join node_wall on node_wall.node_id = node.id
  left join node_pylon on node_pylon.node_id = node.id

Demonstration.

---

You can have a support table and then specialized tables.

create table support (
  id integer primary key
  height integer,
  max_load integer
);

create table wall (
  support_id integer not null references support(id),
  color text
);

create table pylon (
  support_id integer not null references support(id),
  shape text
);

Now node can reference support. Queries for generic support information are easy.

The downsides are:

• If you want to find out the type of support you need to search all tables.
  • This can be mitigated with a view.
• Enforcing that a support can only be one type must be done with triggers (ie. a support can only be a wall or a pylon, not both)

---

You can have one support table with every possible column.

create type support_type as enum('wall', 'pylon');

create table support (
  id integer primary key,
  type support_type,
  height integer not null,
  max_load integer not null,
  color text,
  shape text
);

The advantage is one table.

A disadvantage is if there are a lot of supports and a lot of extra columns you can waste a lot of space. This can be mitigated by using a single jsonb column for the extra data, but now you've added the complexity of jsonb.

Another disadvantage is if any support-specific column is mandatory, you cannot use not null. You must instead write a trigger to, for example, ensure that all pylons have a shape, and to ensure that walls do not have a shape.