Enforcing type correctness for foreign keys into a generic typed table

Question

We currently have a single table which represents a bunch of equipment (of different types). Since the fields are pretty similar, they're stored in the same table.

select * from equipment;

id | name | type

----+--------+----------

1 | item 1 | CONVEYOR

2 | item 2 | PICKER

3 | item 3 | CONVEYOR

I want to create another table, which represents combinations of exactly 1 x PICKER and 1 x CONVEYOR. Something like:

CREATE TABLE "picker_conveyor_combo" (
  "id" serial,
  "picker_id" integer not null,
  "conveyor_id" integer not null
);

picker_id and conveyor_id would be foreign key references into the main equipment table.

However, I'd like to enforce that picker_id always references a row where the type is "PICKER" and similarly with conveyor.

I know that triggers are commonly used for this, but triggers don't typically validate historical data. So the fact the trigger exists won't guarantee that the data is valid.

I'm thinking of an alternative approach with composite foreign keys:

create table "picker_conveyor_combo" (
  id serial,
  "picker_id" integer not null,
  "picker_type" text not null,
  "conveyor_id" integer not null,
  "conveyor_type" integer not null
);

alter table "picker_conveyor_combo"
  add constraint "check_types"
  check ("picker_type" = 'PICKER' and "conveyor_type" = 'CONVEYOR');
alter table "picker_conveyor_combo"
  add constraint "picker_foreign"
  foreign key ("picker_id", "picker_type")
  references "equipment" ("id", "type");
alter table "picker_conveyor_combo"
  add constraint "conveyor_foreign"
  foreign key ("conveyor_id", "conveyor_type")
  references "equipment" ("id", "type");

In this method, there are some extra columns. However, all data will be validated based on constraints - so that adds some extra confidence.

Is this a valid approach, or is there something fundamentally wrong with the schema design?

Answer 1

Your approach is the one I would use.

You forgot a unique constraint:

ALTER TABLE equipment ADD UNIQUE(id, type);

That will be the target of the foreign key constraints.

I would not store type as a string. Rather, I would use a lookup table for the types and store the numeric identifier. This will prevent typos, and it will reduce the required storage space.