0

This is about using inheritance and foreign keys in Postgresql databases.

Consider the following simplistic example whose structure is based on what I am building at the moment (but the specifics were contrived in realtime just for this question, so please excuse any shortcomings!):

Parent table1: Person (columns: ID, Name). Child tables: Man, Woman.

Parent table2: Relationship (columns: ID, Partner1 and Partner2). Child tables: Gay, Lesbian.

Each table has a primary key set on the column ID.

The table Relationship has two foreign keys set on columns Partner1 and Partner2 which reference the table Person (column ID).

The (inherited) tables Gay and Lesbian also need to have foreign keys set on their Partner1 and Partner2 columns. The question is whether these foreign keys should reference the parent table Person, or whether they should reference (as appropriate) the child tables Man and Woman.

The questions comes up because, as stated in the manual for v9.6, section 5.9.1 :

Caveats

... A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single tables, not to their inheritance children ...... These deficiencies will probably be fixed in some future release ...

To me, this (the fact that foreign keys do not apply across inherited tables and must be done separately) is a feature, and not a limitation. And a very useful feature too, as can be seen in the case of the aforementioned example: When the child table Lesbian references the child table Woman (instead of the parent table Person), it is very easy to prevent errors of the sort where there's a lesbian relationship between two men!

enter image description here

Of course, constraints can very well be imposed to achieve this, but it seems to me as though what I wrote above is a more elegant way of doing things. But I am also concerned about what the manual states towards the end - that the development team sees this as a problem, and might get rid of it. So I am also worried if my design would totally break after a future upgrade.

Any tips on the design above and suggestions for alternate ways would be most appreciated.

I would also be very grateful if there's someone from the Postgres dev team lurking around here, and is kind enough to comment.

  • 2
    The comment was already in place for version 7.3 (year 2002!) (postgresql.org/docs/7.3/static/ddl-inherit.html) and it hasn't changed in a long long time. My guess is that inheritance was a feature added during the "everything must be OO" boom... and the feature faded with time. I haven't seen any changes on any significant feature related to inheritance for quite some time. I'd be surprised that this would be changed in the recent future. (Now is the "everything must be scalable, highly parallel... " boom; which I adhere to). – joanolo Aug 1 '17 at 8:08
  • Yes, I just checked it myself, and indeed it seems to be a long standing thing.. Many thanks for the reference. But still, these things go in cycles and I am not sure if relying that the dev team do not change what they already consider a problem would be a good strategy, especially when I am only starting to design the db, and can more easily change things now than later. – Yogesch Aug 1 '17 at 8:48
  • 1
    There are many people now discouraging the use of inheritance as not really fitting the relation model. Others find it all right. You can use some trickery, such as trying to use non-overlapping ID ranges for the different tables, if you know beforehand the maximum number of items they can hold. This way, you won't have any unique or FK clash. You've already prevented it. That's kind of a compromise solution. – joanolo Aug 1 '17 at 8:56
  • You're right, I've struggled a bit with justifying the use of inheritance. But in cases like this, it seems the most logical and elegant solution. The alternatives (in this instance) would be 1. creating new tables and use foreign keys appropriately - which would result in a lot of data duplication. 2. make sure application code never inserts wrong data, which is not the most reliable thing to do. 3. Have a lot of checks and constraints in place, which would make maintenance a hassle everytime some little thing changes.. – Yogesch Aug 1 '17 at 11:41
0
Parent table1: Person (columns: ID, Name). Child tables: Man, Woman.
Parent table2: Relationship (columns: ID, Partner1 and Partner2). Child tables: Gay, Lesbian.

I'm staunchly against the inheritance modeled being used by end users, but even here this isn't a valid use case of it. Your gender isn't a child table. It's an attribute on the table. The same can be said of your relationship.

Just add a column, gender and add another column relationship

To me, this (the fact that foreign keys do not apply across inherited tables and must be done separately) is a feature, and not a limitation. And a very useful feature too, as can be seen in the case of the aforementioned example: When the child table Lesbian references the child table Woman (instead of the parent table Person), it is very easy to prevent errors of the sort where there's a lesbian relationship between two men!

I think is going to devolve into a political question but if people's gender can't change (horrible assumption) then the relationship is known between the two of them by the gender of the people. Why would you even want an attribute on the relationship table. The class gay or lesbian would be inferred from the genders of the participants. Yes if you put it there you can have an error where there is a lesbian relationship between two men, but that's an error introduced because your data should be inferred.

Which of these two seems more logical?

  • f(p1,p2) = class of relationship
  • f(p1,p2,relationship_class) = class of relationship

See also

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.