What's the best way to handle a "union" in a relational database?

Question

I'm running into an annoying problem at work, where the domain model doesn't fit nicely into the relational model of our Postgres Database.

I have a bunch of primary tables A, B, C, etc. Then there's this other table X, that serves as basically a sub-collection for each of the primary tables. Thus, I want a one-to-many relation from each of the primary tables to X. Normally, a many-to-one relation is modeled by adding a foreign key from X to the primary table. However, in this case, I believe I would need a foreign key from X to each of the tables A, B, C, etc. This seems janky to me, especially since exactly one of these keys should be non-null at any time, e.g. a tuple in X should not belong to both A and B.

Is having a_id, b_id, c_id attributes in X the best solution, or is there an alternative approach?

Answer 1

If X has a 1:M relationship with A, B and C (being X in the M side) but you want the FK pointing to A and the one pointing to B to be mutually exclusive, you have to add a check constraint to the table.

ALTER TABLE A ADD CONSTRAINT check_001 CHECK 
    ((a_id IS NULL and b_id IS NOT NULL) OR 
    (a_id IS NOT NULL and b_id IS NULL));

Answer 2

The standard, normalized solution "by the book" is to add link tables X_A, X_B, X_C each one with two foreign keys. For example, in X_A:

• a_id referencing to A
• x_id referencing X, representing a 1:1 relationship!

However, if this is really worth to create these extra tables, or if you can live with a less normalized solution with a_id, b_id, c_id all in X, is heavily case dependent.

Answer 3

The simple answer is to flip the key dependency.

Table X should have a single primary key, perhaps named xid, that is unique across the entire table. No foreign keys are needed.

Each row in A, B, C, and D should also have a column named xid. If the relationship truly is 1:1, then xid could also serve as the primary key for A, B, C, and D as well.

To join them you'd use

SELECT *
FROM   X
LEFT JOIN A on A.xid = X.xid
LEFT JOIN B on B.xid = X.xid
LEFT JOIN C on C.xid = X.xid
LEFT JOIN D on D.xid = X.xid

The above is just a basic subtype/supertype database implementation, nothing particularly unusual about it.

If you are greatly concerned about those extra joins causing unnecessary I/O, and only one of {A, B, C, D} will ever be valid for a given X, you could also add an additional attribute to help your query find the right table. Just add an additional column to X, perhaps named xtype, containing a single character ("A","B","C", or "D"), and then write the joins like this:

SELECT *
FROM   X
LEFT JOIN A on A.xid = X.xid AND x.xtype = 'A'
LEFT JOIN B on B.xid = X.xid AND x.xtype = 'B'
LEFT JOIN C on C.xid = X.xid AND x.xtype = 'C'
LEFT JOIN D on D.xid = X.xid AND x.xtype = 'D' 

This will end up being slightly more efficient, because the query engine will not even bother trying to do a join to A, B, C, or D depending on xtype. The disadvantage here is that you need to keep xtype up to date, and there is no implicit constraint preventing you from setting an xid that points to A but an xtype that points to B. You'd have to implement that constraint yourself.