3

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?

4

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).

  • So if I understand you correctly, solution A is to add a table for every role with one column which is both primary key and foreign key to the employee table. The disadvantage to this solution in my situation: in Postico you only see a list of numbers in the 1:N selector... Solution B seems OK for now. Thanks. – doekman Jul 8 '16 at 11:48
  • 1
    Yes, exactly. . – ypercubeᵀᴹ Jul 8 '16 at 11:51
  • 2
    I'd set the DEFAULT to false - surely only a minority of employees are inspectors. I'm giving a +1 to this question (and answer) because it raises an interesting point: why can't there be PARTIAL FOREIGN KEYs? – Vérace Jul 8 '16 at 11:54
  • 1
    Solution B also needs a unique constraint on the composite foreign key. – doekman Jul 8 '16 at 13:43
  • @doekman you are absolutely right. I'll edit and correct. – ypercubeᵀᴹ Jul 8 '16 at 17:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.