Type Inheritance

• Is Inheritance ORM's Ugly Duckling?
• SQL3 Type Inheritance
• Table Inheritance & Postgres
• Virtuoso Table Inheritance

Is Inheritance ORM's Ugly Duckling?

With JPA there are 3 strategies for implementing Inheritance. These 3 strategies may work out to be very good for most simple inheritance requirements. They work against plain Relational Databases (as opposed to ones with Object Relational features) and with simple Inheritance Hierarchies should work reasonably well.

What probably needs to be looked at is that if you are using a SQL3 database (or Postgres) then you have the option of putting the inheritance in the database right now using SQL3 Type Inheritance. This will need extra work to support this.

I would think that over time as more support of SQL3 (Postgres Table Inheritance) is available people we get benefits from having the inheritance handled by the Database.

The downsides of missing not null constraints and using joins and unions are fairly manageable for small hierarchies but as a hierarchy gets bigger these downsides will be more significant.

SQL3 Type Inheritance

  CREATE TABLE person (
    id INTEGER,
    name VARCHAR(20),
    sex VARCHAR(1),
	age INTEGER); 

  CREATE TABLE employee UNDER person (
    salary FLOAT); 

  CREATE TABLE customer UNDER person (
    account INTEGER);

Now Type Inheritance covers more than just Table Inheritance but I think its fair to say there may well be benefits in using DDL like the above so that Inheritance is handled natively by the database (where possible).

If anyone out there using Type Inheritance I'd like here about it.

Table Inheritance & Postgres

CREATE TABLE cities (
  id              int,
  name            text,
  population      float,
  altitude        int 
);

CREATE TABLE capitals (
  state           char(2)
) INHERITS (cities);

If you are using Postgres then you can use the CREATE TABLE INHERITS syntax to do what looks to me the same job.

The documentation suggests there is some difference to the SQL3 specification so I'd be interested to know the Postgres side of this story if anyone knows.

Again, if anyone has used this feature in Postgres I'd be interested to here about it.

Virtuoso Table Inheritance

It looks to me that Virtuoso supports Table Inheritance with the UNDER Clause for defining subbtables. I have not had time to test out this database but I'd be very interested to here about it.

As a throw away comment this database looks to have gone open source in April 2006 and its feature set is really quite amazing. Is this the best keep secret in the Industry?