Best way to store constants referenced in the DB?

Question

In my database, I have a model which has a field which should be selected from one of a list of options. As an example, consider a model which needs to store a measurement, such as 5ft or 13cm or 12.24m3. The obvious way to achieve this is to have a decimal field and then some other field to store the unit of measurement.

So what is the best way to store the unit of measurement? I've used a couple of approaches in the past:

1) Storing the various options in another DB table (and associated model), and linking the two with a standard foreign key (and usually eager loading the associated model). This seems like overkill, as you are forcing the DB to perform a join on every query.

2) Storing the options as a constant Hash, loaded in one of the initializers, where the key into the Hash is stored in the unit of measurement field. This way, you effectively do the join in Ruby (which may or may not be a performance increase), but you lose the ability to query from the "unit of measurement" side. This wouldn't be a problem provided it's unlikely you'd need to do queries like "find me all measurements with units of cm".

Neither of these feel particularly elegant to me.. can anyone suggest something better?

Answer 1

I would go with option one. How large will it be the UnitOfMeasurement table? And, if using an integer primary key, why do you worry so much about speed?

Option 1 is the way to go for design reasons. Just declare it with an integer (even smallint) primary key and a field for the unit description.

Answer 2

Has ActiveRecord gotten support for natural keys, yet? If it has, you can just make the name (or whatever) column of the UnitOfMeasure table the PK, that way the value of the FK column has all the info you need, and you still have a fully normalized DB with a canonical set of UnitOfMeasurement values.

Answer 3

Do you need to perform lookups on these values? If not, you could as well store them as a string and parse the string later on in the application that reads the values. While you risk storing unparseable data, you gain speed and reduce DB complexity. Sometimes normalizing a database is not helpful. In the end /something/ within your system needs to know that "cm" is a length measure and "m3" is a room measure and comparing "3cm" to "1m3" doesn't make any sense anyway. So you just as well can put all that knowledge in code.

Let's say you are only going to display that data anyway, what is normalizing good for here?

Answer 4

Have you seen constant_cache? It's sort of the combination of the best of 1 and 2 - lookup data is stored in the DB, but it's exposed as class constants on the lookup model and only loaded at application start, so you don't suffer the join penalties constantly. The following example comes from the README:

migration:

create_table :account_statuses do |t|
  t.string :name, :description
end

AccountStatus.create!(:name => 'Active',   :description => 'Active user account')
AccountStatus.create!(:name => 'Pending',  :description => 'Pending user account')
AccountStatus.create!(:name => 'Disabled', :description => 'Disabled user account')

model:

class AccountStatus < ActiveRecord::Base
  caches_constants
end

using it:

Account.new(:username => 'preagan', :status => AccountStatus::PENDING)