M Sach M Sach - 6 months ago 58
SQL Question

Table Per Subclass Vs Table Per concrete class in hibernate?

In most of the web application, i have seen one base class consisting common properties and number of subclasses extending base class . So my question here is which strategy we should go for among Table Per Subclass Vs Table Per concrete class. I personally feel we should go for table per subclass because in future if we want to introduce the common column we can do it at one place but in case of concrete class we have to do it in multiple tables. Right?

But yes if we want to fetch all deatils from all child tables i think Table per concrete class will be helpful Because we have to simply union the records from all tables but in case of Table per Sub class along with union we have to introduce the join with parent table which will be extra costlier .Right?

Answer

You might be interested in Section 2.12 "Inheritance Mapping Strategies" of the JPA 2.0 specification, as it sums up all possbible inheritance types as well as their advantages and drawbacks. Let me pull out just the most interesting fragments:

2.12.1 Single Table per Class Hierarchy Strategy

This mapping strategy provides good support for polymorphic relationships between entities and for queries that range over the class hierarchy. It has the drawback, however, that it requires that the columns that correspond to state specific to the subclasses be nullable.

2.12.3 Table per Concrete Class Strategy

This strategy has the following drawbacks:
- It provides poor support for polymorphic relationships.
- It typically requires that SQL UNION queries (or a separate SQL query per subclass) be issued for queries that are intended to range over the class hierarchy.

2.12.2 Joined Subclass Strategy

It has the drawback that it requires that one or more join operations be performed to instantiate instances of a subclass. In deep class hierarchies, this may lead to unacceptable performance. Queries that range over the class hierarchy likewise require joins.

Also, if you're planning to be JPA-compatible, remember that the JPA-provider doesn't have to support TABLE_PER_CLASS strategy type.


I personally feel we should go for table per subclass because in future if we want to introduce the common column we can do it at one place but in case of concrete class we have to do it in multiple tables.

True, but JOINED strategy also provides you the same feature and allows to specify common properties in one table.

Hope that helps!