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?