Forum: Ruby on Rails 'Code table' best practices?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
25e11a00a89683f7e01e425a1a6e305c?d=identicon&s=25 Wilson Bilkovich (Guest)
on 2005-12-16 17:24
(Received via mailing list)
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.
Dcc2d3a8038cd47219af0eebe0a8a78e?d=identicon&s=25 Carl Fyffe (Guest)
on 2005-12-16 17:39
(Received via mailing list)
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.
2dd904ec5981c31e7bb7a5743a53caf8?d=identicon&s=25 Bruce Balmer (brucebalmer)
on 2005-12-16 18:00
(Received via mailing list)
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
7c4087d053eb02d099a17d91ba5e33b5?d=identicon&s=25 Brian V. Hughes (Guest)
on 2005-12-16 18:12
(Received via mailing list)
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
A2c85dc5ee81b12e3cc0a6522e8d079d?d=identicon&s=25 Chris Hall (Guest)
on 2005-12-16 19:13
(Received via mailing list)
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
Dcc2d3a8038cd47219af0eebe0a8a78e?d=identicon&s=25 Carl Fyffe (Guest)
on 2005-12-16 20:10
(Received via mailing list)
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/scope...

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

Carl
7c4087d053eb02d099a17d91ba5e33b5?d=identicon&s=25 Brian V. Hughes (Guest)
on 2005-12-16 21:22
(Received via mailing list)
On 12/16/05 Chris Hall 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. :)

-Brian
4ff6490c2aa9a893b1c63cbf26b0ca84?d=identicon&s=25 Jay Levitt (Guest)
on 2005-12-16 21:59
(Received via mailing list)
In article
<d4e4955b0512160824g34656a5dg5ad0a7e9e1d24eae@mail.gmail.com>, wilsonb-
Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org 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...
3203ed0e608d3bfae1e31282e629ffa2?d=identicon&s=25 Peter Fitzgibbons (Guest)
on 2005-12-16 22:03
(Received via mailing list)
On 12/16/05, Carl Fyffe <carl.fyffe@gmail.com> 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
> >
> Rails@lists.rubyonrails.org
> 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.

--
59de94a56fd2c198f33d9515d1c05961?d=identicon&s=25 Tom Mornini (Guest)
on 2005-12-16 23:57
(Received via mailing list)
On Dec 16, 2005, at 11:59 AM, Bruce Balmer 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 Mornini
25e11a00a89683f7e01e425a1a6e305c?d=identicon&s=25 Wilson Bilkovich (Guest)
on 2005-12-17 03:32
(Received via mailing list)
On 12/16/05, Tom Mornini <tmornini@infomania.com> wrote:
> On Dec 16, 2005, at 11:59 AM, Bruce Balmer 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.
This topic is locked and can not be replied to.