How best to implement lookup table?

Rails
1

Hi all -

I’d like to implement a lookup table in my app that contains some
reference data, just some status codes and their descriptions. What’s
the most appropriate approach according to the “rails way” of doing
things? Would I just implement a has_many relationship? Let’s say
these are Order status codes, would the StatusCode have many Orders?
This seems right from a database POV (normalizing the code and
description into a separate table), but sort of goofy from a coding
POV. “Order belongs_to :order_status_code” seems a little off to me.
It would be nice if I could somehow automatically have the status
description automatically be added as a read-only attribute to the Order
class.

Suppose the above approach is the correct one. Would I still use the
standard id column as my primary key, or just use the status code as I
normally would in a non-rails app? It seems like it would make sense to
do the latter, but would I regret going against rails conventions when
it is just as easy to go along with it?

Thanks for any insight into this. I’m having a blast learning Rails.

Ken

2

Just put a hash in your Order model, together with an accessor function
if you want one:

class Order < ActiveRecord::Base
belongs_to :customer
has_many :line_item

ORDER_STATUS = {
‘OP’ => ‘Open’,
‘SH’ => ‘Shipped’,
‘CN’ => ‘Cancelled’
}.freeze # freeze to make this hash constant

def status_description {
return ORDER_STATUS[self.status]
}
end

3

On 5/10/06, Kenneth L. [email protected] wrote:

It would be nice if I could somehow automatically have the status

Mick’s suggestion is good. Another way is to turn the status code
into a regular Ruby class that you can add additional methods and data
to, while still keeping it as a single column in your db.

Assuming your table has a status_code column…

class Status
def initialize(code)
@code = code
end

def code
# something to return a useful status from the numeric value in the
DB
end
end

class Order < ActiveRecord::Base
composed_of :status, :mapping => %w(status_code status)
end

4

I personally like to use separate tables for lookup tables, rather than
hash or class oriented approaches. The big reason: you can edit the
values in the lookup table down the road, even if its only from
something as simple as a generated scaffold. I think the code is a
terrible place to define what possible values there should be for
something like status.

5

Thanks everyone for your replies! They’re all very well thought out.

I think I’ll go with the table and has_many approach, simply because it
will be easier for my client to edit the descriptions.

Another drawback with the pure-ruby approaches is that not storing the
descriptions in the database will make it difficult for other
applications (say a reporting app) to use the data, as they won’t have
the descriptions available. (unless you want to get all philosophical
and say that other applications shouldn’t access the data directly and
you should use web services)

Now the question is, is it ok to use the code as the primary key in the
lookup table? Are there any gotchas to using something besides the
automagical “id” column?

Ken

6

I think these methods work well unless you want the lookup values to be
easy
for someone to edit (e.g. admin). In that case, the best solution I
have
come up with is to create a table for each lookup and use the has_many
relationship. I saw a writeup somewhere where someone used a single
lookup
table for all lookups in their application, but that requires a column
to
distinguish which lookup each entry belongs to and seems little messy to
me. Of course having a dozen lookup tables also feels messy at times.

7

On 5/10/06, Bryan D. [email protected] wrote:

I personally like to use separate tables for lookup tables, rather than
hash or class oriented approaches. The big reason: you can edit the
values in the lookup table down the road, even if its only from
something as simple as a generated scaffold. I think the code is a
terrible place to define what possible values there should be for
something like status.

Agreed, definitely, when you need to edit the codes frequently, or
without restarting the app.

I feel that full-on tables with models are overkill for simple status
columns, though.
For example, what if you only have:
0 => AWAITING_REVIEW
1 => ACTIVE
2 => SOMETHING_ELSE
3 => CLOSED

…or something similar. Four rows maybe wouldn’t be worth a whole
table, but you need a numeric code for faster indexing, etc, etc.

8

Why bother trying to use a different key format? It’ll “just work” with
autonumbering ids, and you can still put an index on that field for
faster lookups if you need it. If you’re worried about having to do two
queries to get an item and its status, use the preloading feature that
AR provides (:include => …).

9

Hey,

I wrote a plugin for this very thing. Rather than has_many and
belongs_to you have has_enumerated and acts_as_enumerated.

It gives you extra goodness like caching and being able to write code
that looks (imho) better:

class Foo < ActiveRecord::Base
has_enumerated :status
end

class Status < ActiveRecord::Base
acts_as_enumerated, :order => ‘position ASC’
end

foo = Foo.new()
foo.status = :open
foo.status = Status[:open]

You can get details for the plugin at http://
www.agilewebdevelopment.com/plugins/show/4

It allows separate tables for each enumeration or you can put them
all in the same table (it uses STI in that case).

It also has ‘virtual’ enumeration classes - so if you have 20
different enumeration types you don’t need to have 20 different files
in your models directory.

The only thing which might make it unsuitable for you is that it
caches the values in-memory only - and I make it purposefully hard to
change the record values as a result (don’t want 3 different fcgi
listeners getting out of sync). So, if you plan on frequently
changing the values for Status records then you should look at
another solution.

Personally, I only change these sorts of records (like status codes)
during a deploy anyhow.

Hope this helps,
Trevor

–
Trevor S.
http://somethinglearned.com

10

On 10-May-06, at 4:49 PM, Eden B. wrote:

Do you have any views on this idea?

There’s plenty of ways to do it, ranging from locking the whole user-
side of the app with a “down for maintenance” message, to versioning
your data so that when you’re happy with your changes you make the
new version ‘live’ (complicated but doesn’t involve user-perceived
downtime). It all boils down to what you and your users can live with.

In my experience, the only time I’ve ever done a redeployment that
just contained a change to a lookup value was because of a typo. All
other times I was changing logic at the same time so a redeployment
was on the cards anyhow.

Trev

11

I have looked at your plugin a couple of times, but always stayed away
because I thought it would make it difficult to change the lookup
values.
But I think you are right in noting that for many applications the
lookup
values won’t change that often. It would be ideal if there was a
command
you could issue to lock the application until changes to the lookups
through
an admin panel were complete. This way you could schedule your update,
lock
the application to prevent users from getting incorrect lookups during
the
update, make the changes through your application admin views, and then
unlock the application for normal use again.

Do you have any views on this idea?