Greetings.

PostgreSQL allows tables to inherit columns from other tables in a similar way it is in OO languages. See my question below.

I have the following database schema. There is a table Resources, which has integer ID and varchar TYPE column. I also have a table Cars, which INHERITs from table Resources. Naturally, the Cars table will have "Cars" string in the TYPE column, so when I run a query on the resources table I want it to return records of the Cars type when it finds a matching id.

Is it possible to map this kind of table inheritance with Hibernate?
Any hints, links, or ideas would be appreciated.

Thanks.

It is not supported by hibernate, but it is not a problem. Store both types in the same table and map it using 'discriminator'. There is no reasons to have inheritance in realational model anyway, you can use views to 'hide' this physical representation.

Actually, I think that table inheritance is a neat and useful feature. I understand that you can represent this with views, but most of the databases do not support updateable views.

Here is an example. Suppose you want to store appointment information in a database. So, you would have an appointment table and a many-to-many relation that maps appointments to resources. You can enforce ref. constraints in this many-to-many mapping table. Now, what if you want to have different types of resources, like Person, Car, Location, etc. You can store these resources in tables with different schemas as long as they EXTEND the top resources table.

So, you would have the following schema:

Appointment <- AppRes -> Resources

And then Car and Person tables would extend Resources table and have different schemas. This provides for a very flexible and extendable DB design.

Using discriminator field will not really work in this situation because SELECT query from the Resources table will only return columns that Resources table contains even though, the actual record is stored, say in a Car table, which has a different schema.

So, it would be nice if Hibernate supported the notion of table inheritance.

Does anybody have any ideas on how to use table inheritance with Hibernate?

Thanks.

No, it is not supported by hibernate. Just store this stuff in single table with 'classifier' field, all databases I have ever saw are designed this way and do not have any problems with inheritance.

Putting everything in one table with a classifier field is not a feasible solution, because you would end up having columns, such as "License Plate" and "Last Name" in the same table. It doesn't make any sense, it's a waste of space and it doesn't conform to any normal form.

Putting everything in one table with a classifier field is not a feasible solution, because you would end up having columns, such as "License Plate" and "Last Name" in the same table. It doesn't make any sense, it's a waste of space and it doesn't conform to any normal form.

It doe's not waste disk space, declare nullable fields last in 'CREATE TABLE' command to optimize storage format and use views to make data 'meaningfull'. Probably all popular databases support table inheritance (it is included in SQL standard too), but I have never saw use case for this feature and any problem ralated to inheritance in relational databases.

then just map each class to it's own specific table (I assume selectin'g and updating these tables is just like any other table)

The fact that past solutions have worked fine without the use of table inheritance does not mean that a better solution cannot be achieved by the use of this technique.

Imagine the following REAL use case of inheritance, having many advantages the most significant being auditing and replication. For these examples keep in mind that SELECTing from a parent table selects the common fields in all child tables too unless specifically told not to(this is done with normal postgres SQL).

I have a base table called LoggedRecords, it has a GUID, a user, and a date.

Almost ALL tables in the database are descendant from this master table. This incredibly simplifies replication. I simply select all new rows in all tables with a single:

SELECT *, tableid FROM LoggedRecords where date > lastReplicationDate
(the select returns a table id among the other fields explicitly defined)

I immediately know what needs to be replicated in all tables.

With regards to auditing changes (which helps with data collision handling when replicating too), I'll use the example of a Persons table.

I inherit PersonsLog from LoggedRecord and Persons from PersonsLog.
Triggers in the database automatically insert old records into PersonsLog on updates/deletes to Persons. (And I avoid having duplicate column definitions for Persons and PersonsLog, when I update PersonsLog with new columns, they are automatically added to Persons)

Postgres does this under the hood, by addding tables, but in a different way compared to hibernate join-inheritance. Each child table physically has the space for all columns defined in the super-table and the supertable only stores rows that are specifically of the super-table class. SELECTs are actually done by an implicitl UNION between all tables.

Other advantages are simplified table maintenance and selects/update/deletes and less duplication of definition code among similar tables.

It could help this kind of architecture if hibernate could handle this postgres specific functionality (whose metadata can be accessed through JDBC by looking up the propietary schema tables) because this makes some batch updates possible, instead of using join-inheritance (although I could be wrong with respect to the Hibernate part, I'm pretty new to the API)

Sebastian Ferreyra

Unfortunately PostgreSQL table inheritance is completely useless for ORM. (I had this idea too, a couple of years ago, and was very disappointed.)

The problem is that a query against the superclass returns only attributes of the superclass. Which is the opposite of what you need in ORM. You need all attibutes of all subclasses, padded with nulls.

Sad, but true.

Although you only mention what I would call 'flat' inheritance (I'm not familiar with the hibernate terms yet) I guess you also mean that even in join-inheritance it's the parent table that points to the rows in the 'descendant' tables and in which of these child tables the rows with the extra data are.

Therefore for all inheritance mechanisms in Hibernate the type is always deducible even from selecting the master table. You're right, the way inheritance is done in postgres just doesn't give this information by simply selecting the parent table. A mapping could be done but would not enjoy good performance and require complicated queries.

Too bad really.

Sebastian

Actually, I take some of that back, you can know what the complete type of a row is in postgres by simply selecting rows in the parent table, you have to ask that it also return the tableid (fundamentally the type) of each row.

However, since postgres handles this kind of UNIONed select without indexes in the child tables (it actually does a scan of each child presently) it would still be slow when selecting for the more generic types.

Things have been said however about adding multitable indexing in future versions of postgres.

Sebastian