Inheritance and foreign keys in Postgres
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


Using table inheritance instead of mapping tables