Four design relationship tables

Question

I have come across this scenario several times in different projects. Here is a chart of four tables marked with letters:

A 1 / \ 1 / \ * / \ * BC 1 \ / 1 \ / * \ / * D 

In this case, the data may become inconsistent if the keys from B to A and C to A do not match for the given D

For a specific (compiled) example, imagine A is Company , B is Employee , C is Project , and D is WorkItem . In this case, there is nothing that could stop the creation of a work item that claims to be assigned to a person who does not even work for the company that owns the project.

I'm basically just curious if there is a design solution to this problem? I know that in real applications, when it matters, you can use triggers or some other protection. I did not find a way to modify the tables to make such inconsistency impossible. Is there any way?

Please note that only disconnecting one of the connections, for example from C to A , does not work, because if there is no D for C , you will not have a way to track connections to A

Answer 1

Use compound keys (i.e., a key containing multiple fields) for child tables. Then in D you can use only one field to hold the A key:

[EDIT: Fixed stupid copy and paste bug in D 2nd FK!]

 CREATE TABLE A ( A_ID INTEGER PRIMARY KEY -- Any other fields you want... ); CREATE TABLE B ( A_ID INTEGER REFERENCES A.A_ID, B_ID INTEGER, -- Any other fields you want... PRIMARY KEY (A_ID, B_ID) ); CREATE TABLE C ( A_ID INTEGER REFERENCES A.A_ID, C_ID INTEGER, -- Any other fields you want... PRIMARY KEY (A_ID, C_ID) ); CREATE TABLE D ( A_ID INTEGER, -- This field forms part of the FK for BOTH B and C B_ID INTEGER, C_ID INTEGER, D_ID INTEGER, -- Any other fields you want... PRIMARY KEY (A_ID, B_ID, C_ID, D_ID), FOREIGN KEY (A_ID, B_ID) REFERENCES B (A_ID, B_ID), FOREIGN KEY (A_ID, C_ID) REFERENCES C (A_ID, C_ID) ); 

I have not tested the above SQL, but you are hoping to get this idea. Note that D does not require a third FK constraint for A, because it is already implied by the other FKs (in fact, it is implied separately by each of them).

Referential integrity checking is always better than triggers - at least with PostgreSQL, and I suspect this is true with all RDBMSs.