[Tfug] Inheritance in RDBMS's (PostgreSQL, Oracle, et al.)

[Bexley Hall]

Hi Dave,

> Inheritence implementation is vendor
> specific, so if you decide to use it, you may be locked in
> to a specific database software.

Ah, good point!  Though I can probably live with that (since
I can't see moving away from PostgreSQL)
 
> I would go for three tables: 'person',
> 'man', and 'woman'. 'man' and
> 'woman' have foreign keys that point to
> 'parent'. This schema is simple, database
> independent, and it even works for the
> 'hermaphrodite' corner case (you'd put records
> into all three tables for a hermaphrodite).

Isn't this what (effectively) inheritance does?

But, as I think about this and the "naive" approach of
just a "man" and a "woman" table (each having several
columns that are duplicated in the other table), does
*either* approach (inheritance vs. manually linking
the three tables) screw me when it comes to duplicate
entires in the "person" table?

E.g., imagine two "John Q Public"s which represent
different individuals differentiated by (for silly
example) their "chest hair color" (i.e., an attribute
that would not belong in the "person" table as it
is not shared by "woman"s.

In the naive man vs woman table (no "person"), these
two entries are unique and distinguishable.  However,
when I pull the name column(s) into a "person" table
(either explicitly in your three table case or
implicitly in the inheritance case), I end up with
two person-s having the same name and mapping
to two different "man" table entries.

Of course, I can *make* this work by having a single
"person" table entry and have the two (unique) "man"
table entries both *point* to this single "person".

With three tables, this is very visible.  And, I can
also address the issue of "what if John Q Public #1
changes his name to John Q Private" (i.e., the "right"
solution is to create a new "John Q Private" person
and link the old "man" data for "John Q Public #1"
to this new person).

[rethink the example with *Jane* Q Public and see a
more realistic scenario -- marriage/divorce/etc.]

I'm just wondering what the machinery for inheritance
does to make this work (?).

Hmmm... simple examples often have hidden subtleties...

--don