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

[Bexley Hall]

Hi Dave,

> 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

Understood.  I have't dug through the sources to see; nor
do I think it should matter in my decision (except efficiency
issues) as to whether or not to embrace that *feature*.

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

Agreed.

However, it also has some appeal in that -- assuming I am
good at defining my objects (forward looking) -- I could
later leverage any commonality that might otherwise have
to be manually recreated in a more "traditional" scheme.

E.g., building on the address book man/woman example, if
I later end up with a (different, un-related) table that
also happens to have "people" in it (e.g., list of previous
employers/clients), then being able to draw on the definition
of a "person" as an object could be a win as it forces me to
think of those *other* people as "people", too!

Of course, I could still do this manually by disciplining 
myself to *think* of the "person table" as an informal
object definition -- instead of just creating a similar
set of fields in the "previous employers" table (e.g.,
employer, last_work_date, first_name, last_name, sex, etc.)
 
> 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

Exactly!  It's a maintenance disaster just waiting to unfold.

> 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

Right.  And, it makes it hard to build forms and queries that
can seemlessly be applied to both tables.  E.g., if you wanted
to list the names of all *women*, you would need a different
query (accessing a different table, etc.) than if you were listing
*men* (or, as you go on to say below, "children")

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

Ah, I hadn't thought of that aspect!  (implementation detail)
 
> 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.

This just seems really "naive" to me.  I get the same feeling
when I think of this as I do when I see people using *spreadsheets*
in place of database(s)...  sure, you can make it work but it
is REALLY inelegant...
 
> 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).

Yes, the point is you would have to *add* some field that you
can guarantee to be unique and not simply rely on the entry
(name, e.g.) itself.
 
> 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.

Thanks, I'll do that!  I may also dig through the Oracle 
documentation to see what they say about it (though folks
usually aren't too critical about *features* that they
want to tout in their own products!  :< )

--don