# Need advice on code-like columns

**URL:** <https://www.ruby-forum.com/t/need-advice-on-code-like-columns/97283>\
**Category:** Rails\
**Created:** [July 6, 2006, 3:05am UTC](https://www.ruby-forum.com/t/need-advice-on-code-like-columns/97283 "2006-07-06T03:05:12Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![ssk](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/82dd89/32.png) [@ssk](https://www.ruby-forum.com/u/ssk)\
**Post date:** [July 6, 2006, 3:05am UTC](https://www.ruby-forum.com/t/need-advice-on-code-like-columns/97283/1 "2006-07-06T03:05:12Z")

</div>

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.

1. 

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?

1. 

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

---

<div class="post-metadata">

**Author:** ![ssk](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/82dd89/32.png) [@ssk](https://www.ruby-forum.com/u/ssk)\
**Post date:** [July 6, 2006, 3:21am UTC](https://www.ruby-forum.com/t/need-advice-on-code-like-columns/97283/2 "2006-07-06T03:21:54Z")

</div>

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](http://www.craigambrose.com)

---

<div class="post-metadata">

**Author:** ![ssk](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/82dd89/32.png) [@ssk](https://www.ruby-forum.com/u/ssk)\
**Post date:** [July 6, 2006, 5:40am UTC](https://www.ruby-forum.com/t/need-advice-on-code-like-columns/97283/3 "2006-07-06T05:40:47Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ssk](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/82dd89/32.png) [@ssk](https://www.ruby-forum.com/u/ssk)\
**Post date:** [July 6, 2006, 6:54am UTC](https://www.ruby-forum.com/t/need-advice-on-code-like-columns/97283/4 "2006-07-06T06:54:21Z")

</div>

Hey Sam,

take a look at: [http://tinyurl.com/zr4xo](http://tinyurl.com/zr4xo)

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

## Regards, Trevor

Trevor S.  
[http://somethinglearned.com](http://somethinglearned.com)

---

<div class="post-metadata">

**Author:** ![ssk](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/82dd89/32.png) [@ssk](https://www.ruby-forum.com/u/ssk)\
**Post date:** [July 6, 2006, 7:00am UTC](https://www.ruby-forum.com/t/need-advice-on-code-like-columns/97283/5 "2006-07-06T07:00:31Z")

</div>

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

> [http://somethinglearned.com](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 😉

-Ezra