I'm fairly new to hibernate (just a few weeks) and thought I'd short circuit a few days experimentation by asking what most people find is the best way to do inheritance mapping.

I suppose the important considerations for me are (in order of priority):
(don't worry if you can't cover all points - any info would be valuable)

1. Ability to support complex multilevel inheritance hierachies ie., a depth of more than 2 (not multiple inheritance).

eg., (please excuse the Asciigram)

A
| \
B C
| | \
D E F

2. Full and transparent support for polymorphism.

3. Ability for subclasses to have associations with other classes (or even themselves)

4. Query Performance

In your list of criteria, nothing speaks against table-per-class-hierarchy. The polymorphism and performance criteria even strongly speak for this method. The only drawback might be that attributes, including foreign keys for associations, of subclasses have to be nullable, so you can't use the database mechanisms for checking attributes that are logically non-nullable.

This nullability argument, however, is of limited importance. If your database is only updated via your application (in contrast to spontaneous sql update or insert commands that some user might issue), your software has to check for not-null values, including association cardinality, anyway. If the user enters something that would lead to an illegal null value, you want to catch this error before hitting a database exception, don't you? Therefore, having the database checking null values is just a double check for your software correctness.

Therefore, the only case in which you have to think twice is when the database is updated via different pieces of software that you don't have under your control. In this case, that nullability argument might be so strong that you have to sacrifice some performance and software simplicity for it.

As this is a methodological question for which no yes-no answer exists, I also encourage other experts to comment on this.

Thanks for the info. I've heard other people say that the table-per-subclass is a bit more OO like. After investigating further there are a few things against the table-per-class-hierarchy that I have discovered:

1. Redundant data. The set of table columns is a union of all the fields representing every attribute in every subclass. When objects of the superclass are stored (assuming its not abstract) then it takes up a row that has many columns that it will never use.

2. Schema changes. As we add new subclasses we must change the schema so that the table includes the new columns mapped from the new class' attributes. Not sure how much of a problem this is. Hibernate probably does good job of updating in this case. Table per subclass allows you to add a new subclass without affecting the superclass' table.

3. Have to use a discriminator.

I can see how table-per-hierachy may give a performance boost but is this boost overwhelming or is it just a 5-10% boost.

Syntactically, it looks oo-like, because one table reflects excactly those attributes declared in one class. Semantically, it isn't, because in OO, when an instance of a subclass is created, the instance will also contain all attributes of the superclass. This is reflected better in the table-per-class-hierarchy approach, where you always get all data of one object.

There is nothing like a really oo-like relational database structure. This is called impedance mismatch between OO and RDBM, and it was the original reason that people started thinking about ORM ;-))



Logically true. Physically, every serious database shouldn't use space for empty attributes.



That's true. If this happens a lot, it may be a nuisance. However, adding new subclasses is a major software change. If such a change occurs, isn't it likely that new attributes will be created, apart from new subclasses? In that case, you would have to touch your table structure anyway.



Where's the problem? This is handled completely transparently by Hibernate.

"table-per-class-hierarchy" is natural way to express generalization in relational model, it is the best for performance reasons too. There are a few problems with this mapping but all of them have solutions:

1. "NOT NULL constraint". It becomes CHECK ( NOT(discriminator = 'mySubclass' AND myField IS NULL) ) in subclasses.
2. "Redundant data". Declare nullable fields last, database will optimize null storage.
3. "Single large table and indexes". Create discriminator based partition.

Why are you concerned if it is "OO-like" in a relational database? The whole point of Hibernate is to leverage the relational model on the database side and the OO model on the application end. You should build your tables such that it makes sense relationally on the DB end (which, I would suggest, is table-per-class-hierarchy).

Uhm, the normalized model is joined-subclass. Everything else is a hack, even worse than doing the class==table we are doing.

Yes that's what I was thinking. Even though my experience over many years has been probably 90% OO modeling and coding and 10% RDBMS I did enough RDBMS to know good normalization when I see it and I agree with Christian that table-per-subclass is the way to go.

Having said that, many of the legacy schemas that I worked on invariably expressed inheritance using 'table per hierarchy' but I think this is only because it is a lot easier to implement it that way when you are mapping "by hand" because you end up writing less queries with joins.

Things change though, now with hibernate we can manage all the queries with joins. The effort involved in developing a normalized schema is no more than making a less normalized schema.

I don't think there would be too much of a performance penalty even though there are extra joins - they only involve a single common primary key and besides RDBMS are "pretty darn good" at joining.

Performance is a single reason to denormalize database, if you do not have problems with performance then you do not need denormalization. "table-per-class-hierarchy" violates second normal form and there is redundancy for this reason, but redundancy is always "NULL" and denormalization is not a problem in this case. Normal forms is a very good guidelines for database design, but denormalization is not wrong in exceptional cases like this too.

Just to keep an interesting discussion alive -- does table-per-hierarchy really violate 2nd normal form? When reading this the first time, I mentally agreed, because having all those null values for the subclass the current instance is not a member of in the same table intuitively looks denormalized. To be sure, I looked up the formal definition of 2NF, and I can't find the violation.

When my only key (candidate) is an artificial key, there is no way to violate 2NF at all. Even the most scrupulous normalizers wouldn't take issue with a table like

Now, if some inheritance aficionado comes along and puts Java classes on top of that table like this:

-- then the table suddenly gets a hierarchy-per-table implementation of the class hierarchy. Silly example, but you get the idea, and I can't see any normalization fault with this. Btw, even the necessary introduction of a discriminator into the table doesn't change this, as, with the real-world semantics of persons, person-id still remains the only key candidate.