Four table relationship design

Question

I've run into this scenario multiple times in different projects recently. Here is a diagram of four tables, labeled with the letters:

     A
  1 / \ 1
   /   \
* /     \ *
 B       C
1 \     / 1
   \   /
  * \ / *
     D

In this scenario, it is possible for the data to become inconsistent if the keys from B to A and C to A don't match for a given D.

For a specific (made up) example, imagine A is Company, B is Employee, C is Project, and D is WorkItem. In this case, there is nothing stopping a work item from being created that claims to be assigned to a person who doesn`t even work for the company that owns the project.

I'm mainly just curious, is there a design solution to this problem? I know in real applications where this matters you could use triggers or some other safeguard. I haven't found a way to change the tables to make such an inconsistency impossible. Is there a way?

Note that just severing one of the connections, like from C to A doesn't work, because if no D's exist for that C you would have no way of tracing the connections back to A.

Answer 1

Use composite keys (i.e. a key comprising multiple fields) for the downstream tables. Then in D, you can use just one field to hold A's key:

[EDIT: Fixed stupid copy & paste error in D's 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 haven't tested the above SQL, but you get the idea hopefully. Notice that D doesn't need a third FK constraint back to A, because this is already implied by its other FKs (in fact it's implied separately by each of them).

Referential integrity checking is always better than triggers -- at least with PostgreSQL it is, and I suspect that's true with all RDBMS.