I have a table
employee, and each of them can fulfill different roles, like
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?