life.i.think

So look up tables are fun. Well, maybe not fun, but they are good for database integrity and all that jive. But how to do them cleanly in Rails?

Firstly, lets talk about database schemas. There are 2 primary ways to do lookup tables. One is to have a lookup table for each field. You end up with something like:

  lookup_genders
  -------------
  int id
  varchar description

  lookup_states
  -----------
  int id
  varchar description

This is a bit messy, as you can end up with a gazillion of these suckers in your database. Now, if you aren’t a normal form nazi, you can break the rules a bit and do something like:

  lookups
  -------
  int id
  varchar description
  varchar field_name

Now, this is populated with data like:

  [1, 'Male', 'gender']
  [2, 'Female', 'gender']
  [3, 'KY', 'states']

I’m using the latter for my rails app. Now when displaying the data in pure text form, I want the value of the id to be translated into its cooresponding description. So in the rails model that contains a lookup, I override the method to return that field with this (I’ll use gender as my example from here on):

  # for reading
  def gender
    # first we get the name of the function we're operating within.
    # this is done with the ruby Kernel#caller() method.
    db_field = caller(0)[0].match(/`(.*)'/)[1]

    @lookup = Lookup.find(:all, :conditions  => [ "field_name = ?", db_field])
    for i in @lookup
      if self[db_field] == i.id
        return i.description
      end
    end
    return 'lookup error!'
  end

Lookup.find just searches through our lookup table (it’s an empty model in rails).

Cool right? Now what about putting the data back in the database? Well the best way to deal with lookup data is with a dropdown. So I have the following.

In /app/helps/application_helper.rb:

  def lookup_dropdown(parent_record, lookup_field)
    name = parent_record + "[" + lookup_field + "]"
    lookup = Lookup.find(:all, :conditions  => [ "field_name = ?", lookup_field])
    render_partial("shared/lookup_dropdown", {:name => name, :values => lookup})
  end

In /app/views/shared, add the file _lookup_dropdown.rhtml with the following contents:

  <select name=<%= name %>>
    <% for i in values %>
      <option value="<%= i.id %>"><%= i.description %></option>
    <% end %>
  </select>

Now, anywhere where you want a dropdown for this lookup/field you can use:

<%= lookup_dropdown "inmate", "gender" %>

Tada!