How to constraint a foreign key to a subset

Question

I have a table employee, and each of them can fulfill different roles, like inspector and constructor. I also have another table with a column where I can store what inspector is on the job.

People can play different roles, but roles don't have to be added dynamic; a column for every role on the employee would be sufficient. This would also be handy, because we are resources-constrained at the moment, and our users use Postico for maintaining data (for the moment), so a simple data-model is preferable. With a foreign key we can use the 1:N selector of Postico.

I tried to create a foreign key constraint to a (materialized) view, but that's not supported by Postgresql. Also the following foreign key is not allowed:

FOREIGN KEY("inspector_id", TRUE) REFERENCES employee("id", "is_inspector")

Do I have any option, other than duplicating data in multiple tables?

Answer 1

First, you don't have to duplicate data.

If you add an inspector table, that's not duplicating. You need an "inspectors" entity in your model, you add an "inspector" table. Only the inspector_id values will be duplicated which is fine. So, that's solution A.

Solution B might be to add a an is_inspector column in the table you want to have the foreign key and restrict that column to TRUE:

is_inspector BOOLEAN DEFAULT TRUE,
CHECK (is_inspector),
FOREIGN KEY (inspector_id, is_inspector) 
  REFERENCES employee (id, is_inspector)

I would go for solution A every time but if solution B simplifies your application code for some reason, it still solves the issue and enforces the same requirements. But if you later need other tables to reference "inspectors", you'll need a boolean column in every one of them as well. So, solution A is the more simple approach as it would not require any such boolean columns at all (not even at the employee table, you can find the inspectors by joining to the inspector table).