« CherryPy 3 directionsHow to use "require group" with Trac and SSPI »

Single Table Inheritance certainly *sounds* evil

04/05/06

Permalink 11:03:42 pm, by fumanchu Email , 225 words   English (US)
Categories: Dejavu

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

Comment from: Tim Parkin [Visitor] · http://www.pollenation.net

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.

04/06/06 @ 06:49
Comment from: Todd Blanchard [Visitor] · http://www.blackbagops.net

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.

05/11/06 @ 22:34
Comment from: fumanchu [Member] Email

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.

05/11/06 @ 23:12

Leave a comment


Your email address will not be revealed on this site.

Your URL will be displayed.

Please enter the phrase "I am a real human." in the textbox above.
(Line breaks become <br />)
(Name, email & website)
(Allow users to contact you through a message form (your email will not be revealed.)
September 2014
Sun Mon Tue Wed Thu Fri Sat
 << <   > >>
  1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30        

Search

The requested Blog doesn't exist any more!

XML Feeds

blogging tool