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

[Dave Thompson]

I haven't actually used Postgres's inheritance features, and I don't know
how they are implemented behind the scenes, but I imagine the advantage over
a 3 table schema is that you can use a single insert, update, or delete
statement, where as with a 3 table setup, you would have to insert, delete,
and sometimes update multiple tables to properly update your record.

If I had the choice between a 3 table setup, 2 table setup, and 1 table
setup, I would choose the 3 table setup, because it is properly normalized
and will be the easiest to change when your data requirements change in the
future.

I would stay away from a two table setup, duplicating fields in two places
seems like a big no-go to me. If you need to change the schema, you'll have
to remember to change both tables, and keep the data types of each field
exactly the same between the two tables. Plus, what happens when you need
another table (let's call it 'child') in the future. Then you have the same
fields duplicated in 3 different tables. In addition, if you are using
Postgres's sequence generation to create primary keys, you will have to use
the same sequence for all tables to ensure that id is unique in both the
'man' and 'woman' tables.

I would prefer the single table option over the 2 table option, just one
un-normalized table with nullable fields for the attributes that only apply
to a single object type.

Multiple records with the same name shouldn't cause any problems in any of
these schemas as long as your primary key is something unique, and not the
person's name (such as an integer generated by a Postgres sequence).

You should Google around a bit. I don't have any specific links, but I've
seen several blog posts with extensive discussions about this very topic,
and the pros and cons of each schema type.

-Dave

On Sun, Aug 9, 2009 at 5:53 PM, Bexley Hall <bexley401 at yahoo.com> wrote:

> 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
>
>
>
>
> _______________________________________________
> Tucson Free Unix Group - tfug at tfug.org
> Subscription Options:
> http://www.tfug.org/mailman/listinfo/tfug_tfug.org
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://tfug.org/pipermail/tfug_tfug.org/attachments/20090809/6fb9871a/attachment-0002.html>