Single Table Inheritance

Question

An answer to a question of mine on DB design suggested something called single table inheritance. I've done a bit of searching on that, but I can't seem to find that much clear information on it.

Basically what I seem to understand from it is that you have a big table with all the fields in it and also a type field - and then your ORM layer uses the type field to give you different object views. Is that correct?

More importantly, is single table inheritance an 'approved' database design technique? By that I mean is it 'sensible' to use it? Is it also safe to use it, or does it cause problems?

The other issue is how well this works in rails? I've found a few references to it from rails - but does it cause problems by doing things the non-conventional way?

Any help much appreciated.

Answer 1

Is it a good idea ? It depends. It breaks Normalization in that a table does not have a single purpose. What happens when you extend the base class for the nth time ? You will have to add columns to the table. Most modern DBs don't have a problem with that as you can modify the table but what about refactoring and removing a class. Now you have columns that don't have a purpose.

A rule of thumb - if most of design has been worked out, it's probably safe to use. If the design is changing frequently - you have other issues and you need to lock down your use cases/user requirements. (yes, not really XP friendly)

I don't know about the Rails effects.

Answer 2

STI is a way of dealing with a mismatch between object- and database-oriented thinking. It allows a reasonable representation of the information within the database and a different representation within the object model.

For example, I have an application where each of my products contains one or more fees, each calculated slightly differently. Within my object model I want to have subclasses of a Fee class, each of which knows how to calculate itself. I don't really want to have a table per fee type though: so I create Fee as the base class and fees as the table, which contains the union of all the fields needed across all the sub-types, plus a "type" column whose value corresponds to the name of the relevant sub-class. ActiveRecord handles the plumbing thereafter.

Answer 3

In short, Single Table Inheritance (STI) is a design pattern that allows a mapping of OOP inheritance relationships to the database. If you define any subclasses from your ActiveRecord model objects, then you should consider STI.

STI is (originally?) documented in Martin Fowler's 'Patterns of Enterprise Application Architecture' book, and is also described in DHH's canonical Rails book 'Agile Web Development with Rails' (section 18.4, I think.) I refer you to these books because they provide a much better explanation than I could hope to do in this space.

I strongly disagree with the sentiment expressed at www.matthewpaulmoore.com (linked by robintw in this thread), that STI is inherently a bad thing. This seems to be a somewhat naive view that discounts the usage of OOP inheritance. I have used STI to create some elegant solutions in Rails, but you can abuse any design pattern, I suppose.

Answer 4

Definitive reference. It allows a single table to store multiple objects that have a common base class.

Ruby on Rails uses a library called Active Record. This is a common Ruby framework that supports STI.

Answer 5

I can only speak from the (new) ADO Entity Framework perspective, which includes table-per-type (TPT) functionality.

Here is a good series of blog posts introducing the core concepts (using the Entity Framework) and there is an MSDN paper on it also here.

There also seems to be some guidance when using nHibernate for TPT, it's located here.

There's this link which explains table-per-type inheritance in SQL Server. This seems to have a pretty good summary and introduction to the concept.

I'm not sure what impact it would have on Rails.

Answer 6

I've just seen http://www.matthewpaulmoore.com/articles/1276-ruby-on-rails-code-quality-checklist#sti which suggests its a bad idea.

Answer 7

Generally I find it useful, but I've encountered some bugs

The example in that link may provide a useful picture of how you might use it.