views:

134

answers:

1

Hi, I study about today about 2 database design inheritance approaches:

1. Single Table Inheritance

2. Class Table Inheritance

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

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

My Question is:

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

thanks, Yosef

+2  A: 

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 at paraphrasing him - that I'm quoting below:

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.

Pascal Thivent
Thank you very much Paskal!now i understand much more
Yosef
Thank you very much for good explanation and great article!
Yosef