Re: Inheritance, Primary Keys and Foreign Keys

Albert Cervera Areny wrote:
> Of course, that's an option for my case. Just wanted to know if this solution
> could be useful for PostgreSQL in general. Mainly because I'll add some
> triggers to check what maybe PostgreSQL should do itself but it's
> unimplemented.
>
> If that's not interesting or a proper solution for PostgreSQL I'll add it
> using the existing DDL in my application and that's all.
>
> What do you think?
>
I think that if you want the database to improve its current inheritance behavior, then this
trigger set is too limited. You need triggers that maintain both unique and primary keys and
triggers that maintain cascade behavior.

In order to make it really good, you would also need to add some functionality to the
mechanisms that maintain references. Today, they don't recognize inheritance at all.

Personally, I use Hibernate. It tries to compensate for the lack of these features but since
it is a middle-tier (or client) solution, it's not ideal. Another client can still violate
the rules and to maintain integrity in the client is negative from a performance standpoint.
I think it would be great if PostgreSQL could provide a more complete set of features that
would enable inheritance. A good start would be to extend it with the functionality needed
to maintain references, cascade actions, and enforce unique constraints.

On the other hand, inheritance is a tricky business and a good OO-RDB mapper will give you
several choices of how it should be mapped. There's no "one size fits all". The best
solution is probably if someone (you perhaps?) writes an external OO-RDB mapper module that
executes in the backend. The author of such a tool would of course need some new nifty
backend API's in order to do whats needed with references etc.

I actually wrote something similar using Oracle a couple of years ago. It was based on type
inheritance and views rather then tables and used 'instead of' actions on all views (Oracles
own mechanisms where far to limited). In some respect, I think that is a better solution.
Inheritance and all that comes with it is more a 'type' thing then a 'table' thing in my
world. A view is then used to _map_ the types to persistent storage, i.e. the 'tables'.

Regards,
Thomas Hallgren