Enforcing type correctness for foreign keys into a generic typed table

by J3Y   Last Updated October 09, 2019 23:06 PM - source

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?

Related Questions

PostgreSQL values to unique dictionary

Updated May 19, 2016 07:46 AM

Custom data type vs. extra table

Updated February 16, 2018 17:06 PM