Since I wrote my post Better Single-Table Inheritance, I've gotten several emails with questions and comments, as follows:
How does it perform?
Christian Tietze emailed me to ask about performance, saying:
I wonder how your delegation mechanism works performance-wise. I read just a few days ago that NULL'ed columns aren't a performance problem since you can write efficient SQL indexes. (Can't find the article though, so that's not a reliable argument.)
So now I wonder how Rails performs fetching an Entity which is split into two tables for database representation.
I replied:
...in our case, the performance is fine.
The times when we want efficient queries are when we're doing joins: "find me all the business for contracts with client X". This query can join through the single contracts table without pulling any single contract's details. If we want to split things out by business type, we can GROUP BY the
typecolumn or something. This kind of joining would be awful if we had a completely separate contracts table for each type.The times when we want the details are when we're working with a single contract, so it's just one extra query.
As far as NULL not being a problem, our concern there isn't performance but data integrity. This particular application is a Rails successor to the PHP successor to an older app (AS/400?). The pattern we see is that the database our app maintains is likely to outlive the Rails app and be transitioned someday to Future Technology X.
With that in mind, we're bucking somewhat the Rails mentality of "let the database be dumb and the app be smart." We want to declare most columns NOT NULL, use true foreign keys, etc, so that the database itself can guarantee, as a last resort, that it's getting valid data.
Normal STI makes that impossible because the database must allow NULLs for non-applicable attributes. This split-table approach lets us say that every column in the details table is required by the DB.
It's not "single table inheritance"
I got several emails from people saying that my technique has been invented before. I was thrilled! For such a mundane-seeming need, if I'd invented something truly new, it would probably mean I was Doing It Wrong. But apparently it's a pattern that goes by some of the following names:
Class-Table Inheritance
Christian Tietze emailed to say that this pattern is actually in Martin Fowler's "Patterns of Enterprise Architecture", called "Class-Table Inheritance". That makes me very happy, because that's the book that defines patterns like Active Record.
Vertical Mapping
Jonathan Rochkind wrote to tell me that he'd seen this pattern 10 years ago in Apple's EnterpriseObjectsFramework.
He commented on Reddit that:
It calls the three approaches:
"Vertical Mapping": "each class has a separate table associated with it. There is a Person table, an Employee table, and a Customer table;" (what OP has just re-discovered)
"Horizontal Mapping": "you have separate tables for Employee and Customer that each contain columns for Person."
"Single Table Mapping" -- what AR supports now, single-table inheritance, NOT what OP describes as improved.
Extension
Clifford Heath of Data Constellation wrote to say he'd seen this approach in "Information Modeling and Relational Databases" by Terry Halpin. Heath tweeted that "the three patterns of subtype mapping are Absorption (=STI), Partition and Extension (=yours)"
There's a gem for that
Finally, I got a couple of emails about related gems.
- Mark Starkman wrote me to say that his gem called "CanBe" does something similar to what I described. It's not exactly the same, but give it a look.
- Michaël Van Damme wrote to say that his gem called dbview_cti does Class-Table Inheritance using PostgresSQL's database views. He notes that it's new and hasn't been proven in production yet, but again, give it a look.
Update 9/23/2013 - Post by Christian Tietze
Christian Tietze has written a helpful post, "Solve database mapping problems and the impedance problem of ActiveRecord", which discusses, STI, class-table inheritance.