How to implement status codes in DB and Rails

Rails
1

This is more of a design issue I’m wrestling with, and was wondering
what other people are doing.

I have a products table with status values of In Stock, Out of Stork or
Discontinued. This is unlikely to change any time soon.

Approach A, is to have thses actual values in the status column, at
least the meaning is very apparent, yet querying for products with a
specific status could be problematic if due to a business decision the
description had to be changed, e.g. Out of Stock became Not in Stock.

Approach B, is to have a code that represents the status, such as I, O,
D, querying would work, and their descriptions could change. But when
the data was displayed the code would have to be translated to a
description every time.

Approach C, have a new status look up table, which would have an id
column and a description, and the products table would then have a
status id, which if the description was required would include the
status table in the find. This seems a bit over the top but maybe is the
cleanest way and status can be added and descriptions changed without
any changes to application code.

Approach D, have a new table with status codes I, O, D but that would
mean hard coding a lot of the queries as find_by_sql, to have the table
join, as there is no id column on the status table.

Andrew

2

Andrew C. wrote:

Approach C, have a new status look up table, which would have an id
column and a description, and the products table would then have a
status id, which if the description was required would include the
status table in the find. This seems a bit over the top but maybe is the
cleanest way and status can be added and descriptions changed without
any changes to application code.

This works well in conjunction with Trevor S.’ acts_as_enumerated
plugin. Using this, statuses are stored in the DB as ids, tested in
code using symbolic name fields, and displayed in views as either the
name field or a separate description field:

http://svn.protocool.com/rails/plugins/enumerations_mixin/trunk/

–
We develop, watch us RoR, in numbers too big to ignore.

3

On Nov 8, 2006, at 3:46 AM, Andrew C. wrote:

specific status could be problematic if due to a business decision the
status id, which if the description was required would include the
Andrew
You can use a state pattern for this and it works out pretty clean.
There is also the acts_as_state_machine plugin you may want to check
out. But here is a simple example of the state pattern used in credit
card settlements.

id :integer(11) not null, primary key

name :string(80) default(), not null

description :text

class SettlementState < ActiveRecord::Base
has_many :settlements,
:order => ‘settlements.id’
end

class Settlement < ActiveRecord::Base

belongs_to :settlement_state
belongs_to :ledger

validates_presence_of :xaction
validates_presence_of :message

def authorized
self.settlement_state = SettlementState.find_by_name(‘authorized’)
end

def captured
self.settlement_state = SettlementState.find_by_name(‘captured’)
end

def voided
self.settlement_state = SettlementState.find_by_name(‘voided’)
end

def credited
self.settlement_state = SettlementState.find_by_name(‘credited’)
end

def errored
self.settlement_state = SettlementState.find_by_name(‘errored’)
end

def settled
self.settlement_state = SettlementState.find_by_name(‘settled’)
end

def is_authorized?
self.settlement_state.name == ‘authorized’
end

def is_captured?
self.settlement_state.name == ‘captured’
end

def is_voided?
self.settlement_state.name == ‘voided’
end

def is_credited?
self.settlement_state.name == ‘credited’
end

def is_errored?
self.settlement_state.name == ‘errored’
end

def is_settled?
self.settlement_state.name == ‘settled’
end
end

Cheers-
– Ezra Z.
– Lead Rails Evangelist
– [email protected]
– Engine Y., Serious Rails Hosting
– (866) 518-YARD (9273)

4

Andrew C. wrote:

Approach C, have a new status look up table, which would have an id
column and a description, and the products table would then have a
status id, which if the description was required would include the
status table in the find. This seems a bit over the top but maybe is the
cleanest way and status can be added and descriptions changed without
any changes to application code.

Approach C is the only way that conforms to 3NF.

Joe