Need advice on code-like columns

Rails
1

Hello!

I’m trying to move to rails from traditional web app.
As Rails has some conventions about table schema,
I met some obstacles.

Questions.

This is a sort of general question. Do you make a code table for things
like the following?

Activity Status Codes
01001: Open
01002: Pending
01003: Delayed
01004: Cancelled
01005: Closed

(The first 2 digits are code category.)

Would you make a code table for these codes or would you input the
description directly into the column?
If the codes are flexible (editable by users), the answer must be “use
code table”.
But what if the codes are fixed?

When you need to have a code table, would you still use codes like
‘01001’ or just use a sequential id or both (id is primary key and code
is non-primary key)?

Table: code_categories
Columns: id, description

Table: codes
Columns: id, category_id, description

I guess that I’d better use id instead of codes like ‘01001’.

But for some extreme case, it’s not easy to determine.
For example, zip code table?
You don’t want to make sequential ids for zip codes, right?

I want to hear about your opinions and advices.

Thanks.

Sam

2

Hi Sam,

I’d suggest using a database table for this, because then you get to
leverage the active record to help you out with it. Keep in mind though
that
the id column for an active record table is auto incremented. So, if I
were
you I’d create a table called “status_codes” with three columns: “id”,
“category”, “code”, “name”. Note that I split your number into two
columns,
since you said that the first two digits represented something by
themselves. It’s always easier to put data back together than it is to
split
it up.

Then your object that uses these status codes would have a
“status_code_id”
column (referencing the “id” column from “status_codes”), and a “belongs
to
:status_code” declaration in the model class.

Regarding splitting category out into a separate table; There’s nothing
wrong with this idea. However that doesn’t mean you have to do it all at
once. If I were you, I’d try it in one table like the example above, and
then if you decided that categories needed a bit of functionality of
their
own, of if you wanted to reference them seperatelly, of if you found
yourself writing code specific to categories in your StatusCode class,
then
I’d move them to a new table (rails migrations make this very easy.

Hope that helps,

Craig
www.craigambrose.com

3

On Jul 5, 2006, at 6:05 PM, Sam K. wrote:

things
like the following?

Activity Status Codes
01001: Open
01002: Pending
01003: Delayed
01004: Cancelled
01005: Closed

Hey Sam-

I would store it in the db, you never know when you will need to add

or remove an option from a configuration table like that. But sine it
is just simple configuration you could make a DBHash that uses the
database for storage but acts like a hash:

migration to create the table you probably want

to put an index on the key column

create_table “configurations”, :force => true do |t|
t.column “key”, :string, :limit => 40, :default => “”, :null =>
false
t.column “value”, :string, :default => “”
end

in your model file:

class DbHash < ActiveRecord::Base

class << self
def
pair = find_by_key(key.to_s)
pair.value unless pair.nil?
end

 def []=(key, value)
   pair = find_by_key(key.to_s)
   if pair
     pair.value = value
     pair.save
   else
     pair = new
     pair.key, pair.value = key.to_s, value
     pair.save
   end
   value
 end

 def to_hash
   Hash[ *find(:all).map { |pair| [pair.key, pair.value] }.flatten ]
 end

end

end

Then you can use it like this:

Configuration[:open] = ‘01001’
=> “01001”

Configuration[:pending] = ‘01002’
=> “01002”

Configuration[:delayed] = ‘01003’
=> “01003”

Configuration[:cancelled] = ‘01004’
=> “01004”

Configuration[:closed] = ‘01005’
=> “01005”

Configuration.to_hash
=> {“pending”=>“01002”, “closed”=>“01005”, “delayed”=>“01003”,
“cancelled”=>“01004”, “open”=>“01001”}

Configuration[:open]
=> “01001”

Cheers-
-Ezra

4

Hey Sam,

take a look at: http://tinyurl.com/zr4xo

it’s a plugin that probably does what you’re looking for.

Regards,
Trevor

Trevor S.
http://somethinglearned.com

5

On Jul 5, 2006, at 9:50 PM, Trevor S. wrote:

http://somethinglearned.com

I’m trying to move to rails from traditional web app.
Activity Status Codes
add or remove an option from a configuration table like that. But
t.column “value”, :string, :default => “”
end
end

Then you can use it like this:

Oops! Configuration should be DBHash below in case i confused you :wink:

-Ezra