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?
What's the best way to handle a union in a relational database?
database
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));