When To Use Single Table Inheritance vs Multiple Table Inheritance
What is Single Table Inheritance
In the context of object oriented programming where objects are persisted in a relational database, single table inheritance (STI) is defined as multiple subclasses sharing a single database table. Rails supports STI right out of the box simply by inheriting subclasses from an ActiveRecord parent class which has a type column in its table.
For example, consider the modeling of different types of accounts which share common fields/methods, but also have subclass-specific behavior.
What is Multiple Table Inheritance
Multiple table inheritance (MTI) is defined as each subclass having its own table, but shares common behavior through a common parent class. For example, let’s re-model our accounts with MTI.
When To Use Single Table Inheritance
Use STI When The Subclasses Have The Same Fields/Columns But Different Behavior
A good indication that STI is right is when the different subclasses have the same fields/columns but different methods. In the accounts example above, we expect all the columns in the database to be used by each subclass. Otherwise, there will be a lot of null columns in the database.
Use STI When We Expect To Perform Queries Across All Subclasses
Another good indication STI is right is if we expect to perform queries across all classes. For example, if we want to find the top 10 accounts with the highest balances across all types, STI allows lets us use just one query, whereas MTI will require in memory manipulation.
When To Use Multiple Table Inheritance
Use MTI When The Subclasses Have Vastly Different Fields/Columns But Share Common Behavior
When subclasses have vastly different fields/columns, placing them all in a single table will have a lot of null columns, which is ultimately a waste of space, conceptually confusing, and reduces the effectiveness of indices. For example, modeling animals with STI would be a bad choice.
MTI would be a much better choice as the subclasses can still share a common Animal#eat method, while each having its own table with relevant fields/columns.
Use MTI If We Expect The Table To Get Huge
In general, smaller tables (meaning both fewer records and fewer columns) are better. Even if there is a moderately reasonable case to be made for using STI, the table will be much larger than MTI causing queries to be slower. While sharding is a way to handle large tables, at that point, you might as well have just used MTI instead.
Polymorphic Associations Instead
If you are using STI solely for an Active Record association, you can achieve the same by using Rails-supported polymorphic association instead. For example
When In Doubt, MTI over STI
Unless STI is an obvious knock-out choice (the subclasses have exactly the same fields/columns and we expect to frequently query across all subclasses) err on the side of MTI. STIs are more likely to become conceptually confusing and its table slow and bloated.
Feel free to leave comments, questions, suggestions, corrections below.
