R – Single Table Inheritance


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.

Best Solution

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.