2

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?

|improve this question
  • What does it mean for table X to be a sub-collection of the other tables? I've never heard of a table being a collection or a sub-collection of any other. Does X have columns of its own besides the FKs? Also an ER digram would help. – Tulains Córdova Jul 14 '17 at 20:14
  • @TulainsCórdova "subcollections" here does not refer to tables themselves, but to entities stored in them., i.e. entities in X can be members of collections of types A, B, C. – scriptin Jul 14 '17 at 20:21
  • @X.L.Ant That would introduce a lot of duplication – gardenhead Jul 15 '17 at 1:38
  • @TulainsCórdova I mean that each entity a in A has a list of entities in X. For example, A could represent people and X could represent pets. Each person could have an indeterminate number of pets. – gardenhead Jul 15 '17 at 1:39
  • Have you considered adding join tables which map relations between the entities? A table for a2x, b2x, .... – BlueWizard Jul 15 '17 at 21:51
4

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));
|improve this answer
  • I usually use case expression returning 1 if it's non-null and 0 if it's null, and then sum up the case expressions returning an integer telling how many fields are non-null. Then the integer is compared with 1. But the answer is good enough, so you got my upvote. – juhist Jul 15 '17 at 10:29
3

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.

|improve this answer
  • The one caveat with this is the possibility of having orphan records in X, which have no junction records in X_A, X_B, or X_C. This can be handled with triggers and/or stored procedures. – scriptin Jul 14 '17 at 20:17
  • That solution is not equivalent to what OP wants. – Tulains Córdova Jul 14 '17 at 20:30
  • @TulainsCórdova: ... because? – Doc Brown Jul 14 '17 at 20:57
  • With that solution, how would you manage the fact that the relationships X->A and X->B have to be mutually exclusive? – Tulains Córdova Jul 14 '17 at 21:57
  • The issue is this would create many-to-many relationships. As soon as you add association tables, there's nothing preventing one x object from being attached to multiple A objets. That's not a deal breaker, but it's less than ideal. – gardenhead Jul 15 '17 at 1:41
2

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.

|improve this answer
  • I want a one-to-many relationship from A to X, not one-to-one. – gardenhead Jul 15 '17 at 1:42
  • No problem. The solution will still work, but you can't use xid as the primary key on A, B, C, or D is all. – John Wu Jul 15 '17 at 13:30
  • ah, that is interesting, I've never considered that approach. All the foreign keys I've used have always been to primary key, unique columns. – gardenhead Jul 15 '17 at 19:18
  • Hm actually, it sounds like you're talking about a many-to-one relationship from A to X. I want the other way: one-to-many. – gardenhead Jul 16 '17 at 3:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.