Single Table Inheritance (Database Inheritance design options) pros and cons and in which case it used?

Question

Today, I studied about about 2 database design inheritance approaches:

1. Single Table Inheritance
2. Class Table Inheritance

In my student opinion, Single Table Inheritance makes a database smaller vs other approaches because it uses only 1 table. But I read that the more favorable approach is Class Table Inheritance according to Bill Karwin.

What are the pros and cons of Single Table Inheritance and in which case should it be used?

Answer 1

In my student opinion Single Table Inheritance make database more smaller vs other approaches because she use only 1 table.

Not necessarily. If the entities of your hierarchy have not much attributes in common, this will result in many null columns and will waste a lot of space.

But I read that the more favorite approach is Class Table Inheritance according Bill Karwin.

IMHO, there is no single answer, the different strategies (one table per hierarchy, one table per concrete class, one table per class) have all strengths and weaknesses and choosing one or the other depend on the context.

Single Table Inheritance pros and cons and in which case it used?

This strategy is nice when you need "polymorphic" queries (no need of joins or unions) as long as you can minimize the number of nullable columns (and convince the DBA that a denormalized schema won't be a problem in the long run).

Actually, I suggest to check Mapping Objects to Relational Databases: O/R Mapping In Detail by Scott Ambler (the author of the reference paper about ORM) and especially the section 2.6 Comparing The Strategies—there is no point in paraphrasing him.

His summary of the Single Table Strategy:

Advantages:

• Simple approach.
• Easy to add new classes, you just need to add new columns for the additional data.
• Supports polymorphism by simply changing the type of the row.
• Data access is fast because the data is in one table.
• Ad-hoc reporting is very easy because all of the data is found in one table.

Disadvantages:

• Coupling within the class hierarchy is increased because all classes are directly coupled to the same table. A change in one class can affect the table which can then affect the other classes in the hierarchy.
• Space potentially wasted in the database.
• Indicating the type becomes complex when significant overlap between types exists.
• Table can grow quickly for large hierarchies.

When to use:

• This is a good strategy for simple and/or shallow class hierarchies where there is little or no overlap between the types within the hierarchy.

But I warmly recommend to read the whole paper.

Answer 2

Single table inheritance is a good approach when the subclasses don't have many attributes or associations with other classes. Otherwise the table would be full of attributes that only make sense for some tuples of the table and you´ll need to add all kinds of constraints to check that they only contain values for tuples of the appropriate "type".

Also, all queries that only need to deal with instances of one of the subclass will need the additional clause to select them based on the type value.