| « CherryPy 3 directions | How to use "require group" with Trac and SSPI » |
Single Table Inheritance certainly *sounds* evil
04/05/06
Single Table Inheritance certainly *sounds* evil
Jeff Shell (who needs to turn on comments) wrote:
...By my understanding of Single Table Inheritance, the flight leg, ground leg, and scheduled meeting legs data would all be mashed up in one table. If I were designing tables in an RDBMS, I would never design that way – and I’m no genius Relational designer.
I guess, after thinking about it, I would write the three legs as separate tables/objects and write one legs action to combine them (in Rails)?
That's how I'd do it in Dejavu (three different tables). But Dejavu allows you to recall objects from those three tables either individually or collectively, without having to write your own "combine action". If you recall the subclass, you get just that subclass. If you recall the superclass, you get objects from all subclasses together in the same result set (you also get objects from the superclass, although quite often it's abstract and there aren't any).
I've never worked with Rails' inheritance, but I have been horrified to see mashup tables in plenty of databases. You know the ones: three columns common to all records, 28 columns that only apply to 50% of the rows, and 34 additional columns that only apply to the other 50%. Pick larger column-counts and smaller percentages if you're into mental masochism. 
3 comments
You could always always use table inheritance (if you don't mind using features from a single database). It works pretty well for us and we've managed to use a general 'pickle' column for meta data and have a concept of 'upgrading' attributes from the pickle column to real columns and then even out into dedicated tables.. The idea being that you can start with an 'orm'ish data access paradigm and then gradually (for performance issues perhaps) migrate your data store to a fully relational one without having to rewrite all your code. We're not totally happy with what we've done (postgres's inheritance has a few rough edges) but it's proven really nice for moving from prototype persistent systems to full relational db deployment.
Actually, single table inheritance is the most efficient technique. You don't have to join that way and the queries are easier to write. Of course, it is also the least "clean" from a conceptual standpoint. But that is par for the course with a database. You either have clean well normalized data that requires more effort to join/process - or you have messy denormalized data that can be queried more quickly with fewer joins.
FWIW, on the rare occasion that I use inheritance in the DB - I always opt for single table inheritance.
Hey, Todd, good to hear from you!
I think I was talking more about unions than joins, but your point is well-taken. Maybe I've just been unlucky; most of my experience with multiple sibling classes has required so much post-processing that the question of whether to use a single table or not has never been about query execution speed. The benefits of multiple tables are cleaner and faster post-processing code.
Comment feed for this postLeave a comment
