Rails - Dec 2008 - how to reference lookup data in DB table - by ID? (or should the ID not be relied upon)

Hi,
What''s a good approach for referencing lookup/reference data (static)
that
one has loaded in a reference table.  Say for example "tax codes",
which
pretty much have just the database ID and then a description field.  Some
options that come to mind:

1 - By ID - however this makes an assumption that the data always loads into
a database and gets the same IDs (i.e. when you''re loading your static
reference data)?

2 - Search by description to identify the record - but obviously if one
changes the description this isn''t very good.

3 - Perhaps create a specific identify/key as a new column that you use, but
is not the database Rails ID?  How''s this sound?


Ideas/comments?
Tks

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Sun, Dec 21, 2008 at 5:39 PM, Greg Hauptmann
<greg.hauptmann.ruby-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> Hi,
> What''s a good approach for referencing lookup/reference data
(static) that
> one has loaded in a reference table.  Say for example "tax
codes", which
> pretty much have just the database ID and then a description field.  Some
> options that come to mind:
> 1 - By ID - however this makes an assumption that the data always loads
into
> a database and gets the same IDs (i.e. when you''re loading your
static
> reference data)?
If the descriptions change, you should do it just like this.
> 2 - Search by description to identify the record - but obviously if one
> changes the description this isn''t very good.
That''s the simpler way to solve the issue, if the descriptions do not
change. But this isn''t really "beautiful" from the database
point of
view, as your data isn''t going to be normalized and I usually prefer
to work with normalized data unless denormalization is needed.
> 3 - Perhaps create a specific identify/key as a new column that you use,
but
> is not the database Rails ID?  How''s this sound?
>
I think this is a way to complex way to solve a simple problem :)

-
Maurício Linhares
http://alinhavado.wordpress.com/ (pt-br) | http://blog.codevader.com/ (en)

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

if one uses the ID''s then can I assume you should then
"force" the ID''s to
be set as you want them in the migration?   i.e. can''t rely on a
standard
rails migration where the database uses it auto-increment?


On Mon, Dec 22, 2008 at 7:25 AM, Maurício Linhares <
mauricio.linhares-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> On Sun, Dec 21, 2008 at 5:39 PM, Greg Hauptmann
> <greg.hauptmann.ruby-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > Hi,
> > What''s a good approach for referencing lookup/reference data
(static)
> that
> > one has loaded in a reference table.  Say for example "tax
codes", which
> > pretty much have just the database ID and then a description field. 
Some
> > options that come to mind:
> > 1 - By ID - however this makes an assumption that the data always
loads
> into
> > a database and gets the same IDs (i.e. when you''re loading
your static
> > reference data)?
>
> If the descriptions change, you should do it just like this.
>
> > 2 - Search by description to identify the record - but obviously if
one
> > changes the description this isn''t very good.
>
> That''s the simpler way to solve the issue, if the descriptions do
not
> change. But this isn''t really "beautiful" from the
database point of
> view, as your data isn''t going to be normalized and I usually
prefer
> to work with normalized data unless denormalization is needed.
>
> > 3 - Perhaps create a specific identify/key as a new column that you
use,
> but
> > is not the database Rails ID?  How''s this sound?
> >
>
> I think this is a way to complex way to solve a simple problem :)
>
> -
> Maurício Linhares
> http://alinhavado.wordpress.com/ (pt-br) | http://blog.codevader.com/ (en)
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Dec 21, 4:25 pm, "Maurício Linhares"
<mauricio.linha...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
[...]
> > 3 - Perhaps create a specific identify/key as a new column that you
use, but
> > is not the database Rails ID?  How''s this sound?
>
> I think this is a way to complex way to solve a simple problem :)
I totally disagree.  I think this is probably the best way to do it in
certain cases -- namely, when you *know* that the key is not going to
change.  And then you might as well use that key as the primary key,
instead of an autogenerated key.

A good example of this might be a table of countries.  If the data is
like this:

countries:
  -
    code: US
    name: United States
  -
    code: CA
    name: Canada
  -
    code: MX
    name: Mexico

...then it is perfectly reasonable, in order to avoid the
unpredictability of an autogenerated primary key, to find countries by
code (method 3), and it will work far more reliably than method 2 and
more readably than method 1.  It''s not overcomplicated at all.  It may
even be worthwhile to use code as the primary key column, forgoing an
autogenerated key.  (In this case, I think I''d still use the
autogenerated key -- country abbreviations *can* change -- but you
need to look at your data and decide what suits it best.)
>
> -
> Maurício Linhareshttp://alinhavado.wordpress.com/(pt-br)
|http://blog.codevader.com/(en)
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Greg Hauptmann wrote:
> Hi,
> 
> What''s a good approach for referencing lookup/reference data
(static)
> that one has loaded in a reference table.  Say for example "tax
codes",
> which pretty much have just the database ID and then a description 
> field.  Some options that come to mind:
> 
> 1 - By ID - however this makes an assumption that the data always loads 
> into a database and gets the same IDs (i.e. when you''re loading
your
> static reference data)?
> 
> 2 - Search by description to identify the record - but obviously if one 
> changes the description this isn''t very good.
> 
> 3 - Perhaps create a specific identify/key as a new column that you use, 
> but is not the database Rails ID?  How''s this sound?
I''d recommend the enumerations_mixin plugin that both caches the
table and allows lookup and by id and (unique) name. e.g.

self.tax_code = :exempt
if tax_code === :exempt
   ...

http://svn.protocool.com/public/plugins/enumerations_mixin/

-- 
Rails Wheels - Find Plugins, List & Sell Plugins - http://railswheels.com

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---