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 :
... 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!
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.