Development

Single Table Inheritance + Hstore = Lovely Combination

Joe Hirn — Tandem Alum

published March 26, 2013

update January 29, 2014: We’ve released a gem that packages up the techniques described in this post, check it out: hstore_accessor

Single table inheritance is an awesome concept with an unfortunate drawback; the fear of an unnecessarily wide and sparse table. Sure you started out with one or even zero columns that aren’t shared across subclasses, but there’s nothing stopping a future need from requiring a column 80% of the table doesn’t care about.

An Example

Let’s take Martin Fowler’s description of Single Table Inheritance as a canonical example.

STI diagram

In this simple example above we’ve got a single table Players, which contains information for three specific subclasses. They all share a common attribute of name, but that’s where the beauty ends. A Cricket Bowler does not need a column for club and Footballers do not require a batting or bowling average. Wouldn’t it be nice if these columns didn’t exist for subclasses that don’t require them? Wouldn’t it be nice to not fear adding additional sportsball player types with any mixture of random attributes specific to their sportsballery?

Hstore to the Rescue

Thanks to PostgreSQL and HStore, this is not only possible but incredibly easy and elegant.

I’m going to gloss over the basics of getting hstore running. Plenty of blogs about that. If you have specific questions feel free to leave me a comment below.

Let’s take the same example above and model it in code using ActiveRecord STI. To create the table and models, this is all we need.

# db/migration/_create_players_table.rb
class CreatePlayersTable < ActiveRecord::Migration
  def up
    create_table :players do |t|
      t.string :name
      t.string :type
      t.hstore :data
    end
  end

  def down
    drop_table :players
  end
end

# app/models/player.rb
class Player < ActiveRecord::Base
  serialize :data, ActiveRecord::Coders::Hstore
end

# app/models/players/footballer.rb
class Footballer < Player
end

# app/models/players/cricketer.rb
class Cricketer < Player
end

# app/models/players/bowler.rb
class Bowler < Cricketer
end

Wow piece of cake. Let’s do something with it.

Bowler.create(name: "Sweet Lou",
  data: {'batting_average' => 59.23, 'bowling_average' => 18.75})
bowler = Bowler.find(bowler.id)
bowler.data['batting_average'] # =>  "59.23"
bowler.data['bowling_average'] # =>  "18.75"
ap bowler
# {
         :id => 1,
       :type => "Bowler",
       :name => "Sweet Lou",
       :data => {
                 "batting_average" => "59.23",
                 "bowling_average" => "18.75"
  }
}

Using Awesome Print we see that bowling and batting average are stored in the data column. Bowlers know nothing about the club attribute that is (curiously) specific to footballers.

Improving the Awesome

At this point we’re functional. However, this is Ruby and we can do better with regard to the hash style syntax of accessing the data. I had started writing something similar before I found this gist by Josh Goodall in issue 57 of the activerecord-postgres-hstore gem

# config/initializers/hstore_acessor.rb
module HstoreAccessor
  def self.included(base)
    base.extend(ClassMethods)
  end

  module ClassMethods
    def hstore_accessor(hstore_attribute, *keys)
      Array(keys).flatten.each do |key|
        define_method("#{key}=") do |value|
          send("#{hstore_attribute}=", (send(hstore_attribute) || {}).merge(key.to_s => value))
          send("#{hstore_attribute}_will_change!")
        end
        define_method(key) do
          send(hstore_attribute) && send(hstore_attribute)[key.to_s]
        end
      end
    end
  end
end

ActiveRecord::Base.send(:include, HstoreAccessor)

Now after declaring what attributes are inside of our hash we can talk to attributes in a much cleaner way.

# app/models/players/cricketer.rb
class Cricketer < Player
  hstore_accessor :data, :batting_average
end

# app/models/players/bowler.rb
class Bowler < Cricketer hstore_accessor :data, :bowling_average end bowler = Bowler.create(batting_average: 59.23, bowling_average: 18.75) bowler.batting_average # => "59.23"
bowler.bowling_average # => "18.75"
Cricketer.new.bowling_average # => NoMethodError

Notice calling bowling_average on Cricketer will raise an error because that attribute is specific to bowlers.

Validations

The real sweet part is we can now use the majority of validations on our models.

# app/models/players/cricketer.rb
class Cricketer < Player hstore_accessor :data, :batting_average validates_presence_of :batting_average end Cricketer.new.valid? # => false

You’ve Got Documents in Your Columns

Well it’s not the full power of documents, but among its other uses hstore is a wonderful complement to STI. It does away with the fear of ending up with a sparse table. In the example above we could add any number of attributes to a subclass of player and it does not affect other classes which do not need them. Combine that with the indexing PostgreSQL allows and you’ve got quite a powerful dynamic schema without the tradeoffs of full blown NoSQL.

Time provided I intend to improve upon hstore_accessor to accept type declarations and do the necessary casting. I’m also curious to see what features Rails 4 gives you out of the box. I hear good things and will not deny myself the power of PostgreSQL specific features. You shouldn’t either.

Thanks for reading.

Tandem is custom software development company in Chicago and San Francisco with practice areas in digital strategy, UI/UX, and custom mobile and web application development.

Joe Hirn