'Code table' best practices?

Rails
1

I’ve been going back and forth about this kind of thing for a while.
I reverse my opinion on it every project, and I’m wondering if anyone
has any advice/battle scars to share. As usual, please forgive my
long-windedness in advance.

Let’s say I have this:
Table: PLANS
ID int
CATEGORY_CODE_ID int
–Other junk removed for clarity

Table: CATEGORY_CODES
ID int
DESCRIPTION varchar(200)

“category codes”, to the user, are 6-digit numbers, sometimes with
leading zeroes, that have specific meanings.

My customers usually want a metric truckload full of reports. Often,
there are more reports than actual business logic. So much fun.

Reports get much, much easier when you don’t need to join over into a
lookup table constantly.
It would be nice to able able to simply say:
Plans.find_all_by_category_code ‘123456’

If I follow the ‘pure’ ActiveRecord intent for this, it would instead
look like:
Table: PLANS
ID int
CATEGORY_ID int

Table: CATEGORIES
ID int
CATEGORY_CODE char(6)
DESCRIPTION varchar(200)

…and I would then need to do:
Plans.find(:all, :conditions => “categories.category_code = ‘123456’”,
:include => :categories), which would be much slower.

…and then @some_plan.category.description whenever I needed to
display the description text.

In this design, ‘PLANS.CATEGORY_ID’ would be a non-semantic synthetic
value. Any find_by_sql queries would be much more tedious to write.

Am I going to run into trouble by having my ‘code table’ be an
association with a primary key that actually means something, rather
than being synthetic?
The code works either way, but I hate feeling like I’m going against
the grain with ActiveRecord.

Anyone have any comments or complaints?
Thanks,
–Wilson.

2

Having a primary key that has meaning is a “Bad Thing”. What happens
when they decide that 0003231 should actually be 0003322? It smells
bad. We have a system that has this problem and it hurts. Primary keys
are the unique identifiers for a row and should have no other meaning.

There are some really cool ways that you can make this work in
ActiveRecord that will keep things from being ugly. I will let others
tell you more about ActiveRecord use. I just wanted to chime in and
say: Don’t make the PK have meaning.

3

Just to lend my support to this argument - I can’t think of a reason
why anyone would ever use a meaningful primary key? Does it save
space? Probably not very much given how cheap space is these days
does it matter? Perhaps for a few specialist db’s that are vast.

Is it quicker? Well, there is one less column to deal with but since
computers this year are so much faster than last year, does it matter?

If you have to rebuild your db and code because you had to change the
primary key, is it going to matter? I think so.

Why take the chance. You gain so little and you lose so much
(potentially).

bruce

4

OK… first thing, take Carl’s advice (and my advice) and create an
actual,
auto_increment ID column for CATEGORY_CODES. You will save yourself a
lot of
pain in the end. I’ve tried to get around needing a Rails controlled ID
field,
and it just doesn’t work well.

But, doing that makes your life somewhat easier. Your Rails model should
look like:

class CategoryCode < ActiveRecord::Base
has_many plans
end

class Plan < ActiveRecord::Base
belongs_to category_code
end

Assuming you have a “code” column that holds the 6 digit code, which you
could
actually expose in your routes/URLs, to efficiently get all the Plans
for a
particular Category_Code you would do:

category = CategoryCode.find_by_code(mycode, :include => :plans)
plans = category.plans

I would say this is the “Rails way” to do what I think you’re trying to
do. Let
me know if any part of this doesn’t make sense.

-Brian

5

Chris, Thanks! That was the point I wanted to make.

Tobias also has an interesting example of what you can do with
ActiveRecord here:

http://blog.leetsoft.com/articles/2005/10/31/scoped-databases

I think this scoping idea will make it easier to create the reports.

Carl

6

On 12/16/05 Chris H. wrote:

and my $0.02 on the primary key issue…say your company/employer
wants to change those codes, say add dd- (2 digits and a dash) to the
beginning of all codes. since you’ve used this as a primary key, not
only will the codes have to change in the category_codes table, but
any other tables that reference that primary key will also have to
change…not something i would want to have to deal with.

A very good point here. I would put forth that a table’s primary key
(aka. ID)
should be immutable. And a very good way to accomplish that is to pyt it
totally
under the database’s control, so that all your app’s code can to is
refer to,
and refer through, it.

That might seem a little extreme, but it certainly makes Rails a whole
lot
happier when that’s the case. :slight_smile:

-Brian

7

that’s the thing, sometimes you have to look at things differently when
in
the rails universe.

i had to work this out myself when i started with rails coming from a
PHP
background.

my first thought would be to attack the problem from the ‘plans’
side…i
want a list of plans for a given code…

so i would try to implement something like

class Plan < ActiveRecord::Base
belongs_to :category_code

def self.find_by_category_code(code)
find :all, :joins => “as p, category_codes as c”, :conditions =>
[“c.id=
plan.category_code_id and c.code = ?”, code]
end
end

plans = Plan.find_by_category_code(“000123”)

which works perfectly fine…but rails makes you think about it from a
different viewpoint (using your example)

plans = CategoryCode.find_by_code(“000123”, :include => :plans).plans

which, now that i have learned to think in rails, is much easier and i
didn’t have to write any additional code!

and my $0.02 on the primary key issue…say your company/employer wants
to
change those codes, say add dd- (2 digits and a dash) to the beginning
of
all codes. since you’ve used this as a primary key, not only will the
codes
have to change in the category_codes table, but any other tables that
reference that primary key will also have to change…not something i
would
want to have to deal with.

Chris

8

In article
[email protected], wilsonb-
[email protected] says…

Reports get much, much easier when you don’t need to join over into a
lookup table constantly.
It would be nice to able able to simply say:
Plans.find_all_by_category_code ‘123456’

Hasn’t someone done at least one “lookup table” / “enumerated” plugin
recently? Maybe even two people, though one was pre-plugins. Both
implementations cached the lookup table and let you refer to it in some
syntactically nice way, if I recall…

9

On Dec 16, 2005, at 11:59 AM, Bruce B. wrote:

Just to lend my support to this argument - I can’t think of a
reason why anyone would ever use a meaningful primary key?

I’ll second (third?) that!

Does it save space? Probably not very much given how cheap space
is these days does it matter? Perhaps for a few specialist db’s
that are vast.

Is it quicker? Well, there is one less column to deal with but
since computers this year are so much faster than last year, does
it matter?

I think everyone could agree that it’s likely FAR FASTER to create an
index and lookup against an index for typical synthetic ID type (INT,
LONG INT)
than a typical real ID (strings, strings everywhere!).

–
– Tom M.

10

On 12/16/05, Carl F. [email protected] wrote:

CATEGORY_CODE_ID int
there are more reports than actual business logic. So much fun.
CATEGORY_ID int
…and then @some_plan.category.description whenever I needed to

[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

I seem to recall from my (incomlete) db theory… something like “4th
(or
5th) degree normalization”. On of the requirements for 4thN is that
there
are no “non-identifying” information in teh database… that means no
primary keys, and composite-column-keys is the only way you get FK’s to
work.

Somenoe correct me ?

I have to deal with one accounting db that is this way and it is Soo Soo
painful.

–

11

On 12/16/05, Tom M. [email protected] wrote:

On Dec 16, 2005, at 11:59 AM, Bruce B. wrote:

Just to lend my support to this argument - I can’t think of a
reason why anyone would ever use a meaningful primary key?

I’ll second (third?) that!

I think everyone could agree that it’s likely FAR FASTER to create an
index and lookup against an index for typical synthetic ID type (INT,
LONG INT)
than a typical real ID (strings, strings everywhere!).

Thanks, everyone. I think you’ve helped me break myself of the
senseless desire to avoid this join.
If I ever need it to work a different way in some other context, I’ll
just do a materialized view for it in the DBMS.

–Wilson.