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?