logo-new mail facebook Dribble Social Icon Linkedin Social Icon Twitter Social Icon Github Social Icon Instagram Social Icon Arrow_element diagonal-decor rectangle-decor search arrow circle-flat

Welcome to Tandem. We were formerly known as DevMynd. Read more about our new identity.

March 26, 2013

Single Table Inheritance + Hstore = Lovely Combination

Chief Technology Officer

UPDATE 1/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

  def down
    drop_table :players

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

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

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

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

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)

  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))
        define_method(key) do
          send(hstore_attribute) && send(hstore_attribute)[key.to_s]

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

# app/models/players/bowler.rb
class Bowler < Cricketer
  hstore_accessor :data, :bowling_average

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.


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

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.

Let’s do something great together

We do our best work in close collaboration with our clients. Let’s find some time for you to chat with a member of our team.

Contact Us