Re: Best practice on inherited tables

In general, I find table inheritance most helpful when one wants to re-use
interfaces over multiple independent tables. For example instead of a
global notes table, a bunch of notes tables attached to things, but with
centralized schema management. In general in these cases you want
something identifiably unique across the entire tree (which is why tableoid
or a *_class_id field would be added).

For example in the case at hand, inheritance would not be a great tool if
you wanted to have a lot of foreign keys against the locations tree.
However if you wanted to store locations with geospacial data, and then
have a bunch of separate location tables (as full tables in their own
right), this would be fine. I think further that the foreign key issue is
going to need some attention at some point because of the way table
inheritance is used in table partitioning.

I actually think that bringing some object-oriented principles into
database design can result in some very useful things, provided that one
remembers that applications are modelling behavior while databases are
modelling information (and so the two behave quite differently). The OO
principles are helpful particularly when looking at derived data.

I have spent some time blogging about this issue. I don't think the fact
that these are different necessarily means that Object-relational modelling
in the db is not extremely useful.

Best Wishes,
Chris Travers