Thursday, October 18, 2007

How to create a lookup table in Ruby on Rails

I'm a big fan of the Rails way, but sometimes the simple things get you. I like to set up "lookup tables" in Rails, ie. tables in the database that hold commonly used, (often) fixed values. For example, if I have a Car model, I might want to define a Car type model. This would have two purposes:

  1. Give me the ability to associate a type with the Car model (ie. Car.type => 'sport')

  2. Pre-load common values into the database (ie. sport, coupe, sedan, etc)

I can accomplish this through a few steps. First, lets generate the lookup table model:

$ ruby script/generate model CarType

We'll need to edit the migration under db/migrate/XXX_create_car_types.rb

class CreateCarTypes < ActiveRecord::Migration
def self.up
create_table :car_types do |t|
t.column :name, :string

CarType.create(:name => 'sedan')
CarType.create(:name => 'sport')
CarType.create(:name => 'coupe')
CarType.create(:name => 'truck')
CarType.create(:name => 'van')


def self.down
drop_table :car_types

You'll note that I created the name field, and then I just used the create method to produce sample values in the database. This can be an immensely useful technique, especially when deploying a production website - just run your migrations and those lookup tables are already populated.

Note: The other technique for populating the database automatically is fixtures. While I think they are great for creating test development data, fixtures fall behind in a production environment. Typically, fixtures will define data like "Test car 1" and "Test car 2". This is very useful to during development, but if you have a lot of test data you can crowd your production database pretty quickly. Since there is no mechanism to conditionally load fixture data based on the environment, fixtures lose their appeal. If you must load fixture data into your production database, you may use

$ rake RAILS_ENV=production db:fixtures:load

Second, create the Car model and associate it with a CarType:

$ ruby script/generate model Car

Car < ActiveRecord::Base
belongs_to :car_type

Your Car migration must hold the appropriate car_type_id:

class CreateCars < ActiveRecord::Migration
def self.up
create_table :cars do |t|
t.column :name, :string
t.column :car_type_id, :int

def self.down
drop_table :cars

Then, you can write code like this:

c = Car.find(1)
=> #"sedan", "id"=>"1"}>

I like to simply even more, so I generally add a method like this to the Car model:

def type

Resulting in the following:

=> "sedan"

And thats it!

You might be interested to know that you can add a corresponding has_many to the CarType model to easily find all Cars of a particular type:

CarType < ActiveRecord::Base
has_many :cars

And then call something like:

=> [#"Test car 1", "id"=>"1", "car_type_id"=>"1"}>]


  1. You are my hero. This is the best example I've found of lookup tables and how the Rails way is a little confusing to beginners. Awesome.

  2. Dude this is so simple and useful. I was looking for the best way of setting up a simple lookup table in Rails and after seeing your solution, I can't believe I didn't think of it myself.