Re: Some thoughts on table inheritance (which is uniquely awesome on PostgreSQL)

On Aug 22, 2012, at 23:22, Chris Travers <chris(dot)travers(at)gmail(dot)com> wrote:

> * unique constraints being able to be marked INHERIT or NOINHERIT.
> A unique constraint that is marked INHERIT would be automatically
> created again on the child table. This could be documented to be
> domain-specific to each child table, and that if you need super-set
> unique constraints, you need to borrow techniques from table
> partitioning.
>
>
> * PRIMARY KEY inheritance would be documented as necessarily
> excluded by multiple inheritance. The concept simply doesn't make
> sense when a child table can have multiple parents. If it did, you'd
> have multiple primary keys. According to Oracle and DB2
> documentation, the best they offer regarding such things is an OID
> field anyway.....
>

late night thinking here...

An inherited PK constraint could be made into a unique/not-null constraint if a second PK constraint becomes inherited. In that case the table no longer has a PK constraint until the user creates one that makes sense.

This whole issue occurs due to surrogate keys being used as PK. In a partitioning scheme the partitioning field should be part of the natural key and thus cross-relation matching could not occur in the absence of a mis-allocation which a partition specific check constraint on that column would solve.

In an OO situation, in the absence of partitioning, a key is a concept of identity. Identity requires that the type of two entities matches; and the type of a child object will never match the type of an object of its parent. Thus regardless of single or multiple inheritance PK inheritance makes no sense in an OO situation.

Even with multiple inheritance you might want to inherit a PK from from parent but from the other parent(s) you might simply want to inherit their PK as a unique constraint. In so doing you assert that you are on the same level as the PK parent objects while you have different attributes than your siblings. Jack and Jill can both inherit PK from human being but could inherit phone_number and email from contact_info (not the best example I know...I tried making gender work here but my mind went blank if trying to rationalize why gender wouldn't just be a FK).

For FK, however, the question is whether I am referencing a specific instance or whether I simply am referencing an arbitrary set of properties that anything matching those properties could match. Currently the former is what we have, and since we are dealing with entities (as opposed to behavior) that makes sense. Ignoring partitioning if I define an FK relationship to "flying things" I supposedly do not care whether you store a bird-type flyer or an airplane-type flyer. If someone names their pet bird Polly and someone else names a plane Polly then what...Inheriting an FK to a target non-partitioned table makes sense but how does one deal with inheriting onto a target table that has children?

Just some thoughts as I have not, as became obvious writing this, thought through using the database in this fashion. Most inheritance I have used is behavioral in nature whereas a database deals with identity. Segregating between type composition and partitioning mentally, and ideally in the language, makes a lot of sense to me. It seems that currently both models are partially implemented and done so using the same syntactical foundation...

David J.