thr3ads.net - Rails - How to Work with Tables That Don''t Have a Numeric PK? [Apr 2007]

If this information is useful, please help other people find it:
Share via:

Geo Pappas

2007-Apr-10 00:36 UTC

How to Work with Tables That Don''t Have a Numeric PK?

My understanding is that RoR needs a primary key (PK) that is numeric.
The default column name for the table is ID.

I am a big advocate for using a "dumb" or non-intelligent primary key
for transaction tables.  But it doesn''t make as much sense for many
lookup/reference tables.

For example, if you have a lookup table for the States of the U.S., you
would usually define a State table as follows:

CREATE TABLE states
(
   CODE  VARCHAR(2), -- This would be the primary key
   DESCRIP  VARCHAR(50),
   PRIMARY KEY (CODE)
);

This table would contain data such as:

NY, New York
CA, California
FL, Florida
...

Then, in a transaction table, you would use the CODE as the foreign key.

For example, in a table that contained an address, a user would enter in
the state code.

Using a state code like this enables the transaction table to contain
data that is easily identifiable.

Does RoR handle this type of situation automatically?  If so, how?  If
not, how can you work around this issue?

-- 
Posted via http://www.ruby-forum.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
-~----------~----~----~----~------~----~------~--~---

Leonard Chin

2007-Apr-10 09:24 UTC

head link

Re: How to Work with Tables That Don''t Have a Numeric PK?

On 4/10/07, Geo Pappas
<rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:>
> Does RoR handle this type of situation automatically?  If so, how?  If
> not, how can you work around this issue?
One quick work around is to use Dr Nic''s composite primary keys plugin
http://compositekeys.rubyforge.org/

Not only does it give you the option of utilizing composite keys, but
it also actually allows you to use (singular) string primary keys by
way of the set_primary_keys class method.
e.g.
set_primary_keys :code

Most rails features are supported (finders, associations, and so forth).

One caveat is that this does not cover migrations - though you could
probably fudge things by adding PK constraints manually.

--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---

Robert Walker

2007-Apr-10 15:26 UTC

head link

Re: How to Work with Tables That Don''t Have a Numeric PK?

> I am a big advocate for using a "dumb" or non-intelligent primary
key
> for transaction tables.  But it doesn''t make as much sense for
many
> lookup/reference tables.
Personally speaking I, wholeheartedly, disagree.  It always "makes
sense" to use simple primary key to manage relationships in any object-
relational framework.  If you want a key to be used by the users of
the system, give them a separate column in the database.

There are many techniques for providing both a key for the system, and
a separate key for the users of the system.  Many of these even base
the user''s key on the auto-generated key.  Doing anything else
you''ll
be fighting the Rails (or any other object-relational) system and
that''s never much fun.

Stop thinking like a DBA and start thinking like an OOP programmer and
this begins to make a whole lot of sense.

On Apr 9, 8:36 pm, Geo Pappas
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:> My understanding is that RoR needs a primary key (PK) that is numeric.
> The default column name for the table is ID.
>
> I am a big advocate for using a "dumb" or non-intelligent primary
key
> for transaction tables.  But it doesn''t make as much sense for
many
> lookup/reference tables.
>
> For example, if you have a lookup table for the States of the U.S., you
> would usually define a State table as follows:
>
> CREATE TABLE states
> (
>    CODE  VARCHAR(2), -- This would be the primary key
>    DESCRIP  VARCHAR(50),
>    PRIMARY KEY (CODE)
> );
>
> This table would contain data such as:
>
> NY, New York
> CA, California
> FL, Florida
> ...
>
> Then, in a transaction table, you would use the CODE as the foreign key.
>
> For example, in a table that contained an address, a user would enter in
> the state code.
>
> Using a state code like this enables the transaction table to contain
> data that is easily identifiable.
>
> Does RoR handle this type of situation automatically?  If so, how?  If
> not, how can you work around this issue?
>
> --
> Posted viahttp://www.ruby-forum.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
-~----------~----~----~----~------~----~------~--~---

Robert Walker

2007-Apr-10 15:34 UTC

head link

Re: How to Work with Tables That Don''t Have a Numeric PK?

> CREATE TABLE states
> (
>    CODE  VARCHAR(2), -- This would be the primary key
>    DESCRIP  VARCHAR(50),
>    PRIMARY KEY (CODE)
> );
One other noteworthy thing to mention about this design is this:  What
happens when your client comes to you and asks to change a "CODE"
value to something different?  I know this should never happen, but in
the real world it happens all the time.  If you manage relationships
using a system generated simple PK then this situation suddenly
becomes a lot less painful.

On Apr 10, 11:26 am, "Robert Walker"
<rwalker...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> > I am a big advocate for using a "dumb" or non-intelligent
primary key
> > for transaction tables.  But it doesn''t make as much sense
for many
> > lookup/reference tables.
>
> Personally speaking I, wholeheartedly, disagree.  It always "makes
> sense" to use simple primary key to manage relationships in any
object-
> relational framework.  If you want a key to be used by the users of
> the system, give them a separate column in the database.
>
> There are many techniques for providing both a key for the system, and
> a separate key for the users of the system.  Many of these even base
> the user''s key on the auto-generated key.  Doing anything else
you''ll
> be fighting the Rails (or any other object-relational) system and
> that''s never much fun.
>
> Stop thinking like a DBA and start thinking like an OOP programmer and
> this begins to make a whole lot of sense.
>
> On Apr 9, 8:36 pm, Geo Pappas
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
> wrote:
>
> > My understanding is that RoR needs a primary key (PK) that is numeric.
> > The default column name for the table is ID.
>
> > I am a big advocate for using a "dumb" or non-intelligent
primary key
> > for transaction tables.  But it doesn''t make as much sense
for many
> > lookup/reference tables.
>
> > For example, if you have a lookup table for the States of the U.S.,
you
> > would usually define a State table as follows:
>
> > CREATE TABLE states
> > (
> >    CODE  VARCHAR(2), -- This would be the primary key
> >    DESCRIP  VARCHAR(50),
> >    PRIMARY KEY (CODE)
> > );
>
> > This table would contain data such as:
>
> > NY, New York
> > CA, California
> > FL, Florida
> > ...
>
> > Then, in a transaction table, you would use the CODE as the foreign
key.
>
> > For example, in a table that contained an address, a user would enter
in
> > the state code.
>
> > Using a state code like this enables the transaction table to contain
> > data that is easily identifiable.
>
> > Does RoR handle this type of situation automatically?  If so, how?  If
> > not, how can you work around this issue?
>
> > --
> > Posted viahttp://www.ruby-forum.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
-~----------~----~----~----~------~----~------~--~---

Geo Pappas

2007-Apr-10 16:12 UTC

head link

Re: How to Work with Tables That Don''t Have a Numeric PK?

Robert Walker wrote:>> CREATE TABLE states
>> (
>>    CODE  VARCHAR(2), -- This would be the primary key
>>    DESCRIP  VARCHAR(50),
>>    PRIMARY KEY (CODE)
>> );
> 
> One other noteworthy thing to mention about this design is this:  What
> happens when your client comes to you and asks to change a "CODE"
> value to something different?  I know this should never happen, but in
> the real world it happens all the time.  If you manage relationships
> using a system generated simple PK then this situation suddenly
> becomes a lot less painful.
Robert Walker wrote:>> CREATE TABLE states
>> (
>>    CODE  VARCHAR(2), -- This would be the primary key
>>    DESCRIP  VARCHAR(50),
>>    PRIMARY KEY (CODE)
>> );
> 
> One other noteworthy thing to mention about this design is this:  What
> happens when your client comes to you and asks to change a "CODE"
> value to something different?  I know this should never happen, but in
> the real world it happens all the time.  If you manage relationships
> using a system generated simple PK then this situation suddenly
> becomes a lot less painful.
In the example I gave above (for a STATES lookup table), I doubt that
there will be another state added to the U.S. (at least in my lifetime),
or that they will change the state abbreviation for any of the existing
states.

Adding a non-intelligent primary key to this sort of lookup table adds
another level of complexity that isn''t needed and decreases database
performance.  Storing a code (such as NY, CA, FL, etc) gives meaning to
the data that is stored, whereas storing a number removes meaning.  In
my career, I have never seen a state stored in a transaction table as a
number.

This also seems to goes against one of RoR''s claims, which is that the
code should work around the client and not vice-versa.

Finally, there is such a thing as over-normalizing data.

-- 
Posted via http://www.ruby-forum.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
-~----------~----~----~----~------~----~------~--~---

Brian Hogan

2007-Apr-10 20:03 UTC

head link

Re: How to Work with Tables That Don''t Have a Numeric PK?

Why store states in a table when a simple hash will do? Databases don''t
need
to be used for everything.

On 4/10/07, Geo Pappas
<rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:>
>
> Robert Walker wrote:
> >> CREATE TABLE states
> >> (
> >>    CODE  VARCHAR(2), -- This would be the primary key
> >>    DESCRIP  VARCHAR(50),
> >>    PRIMARY KEY (CODE)
> >> );
> >
> > One other noteworthy thing to mention about this design is this:  What
> > happens when your client comes to you and asks to change a
"CODE"
> > value to something different?  I know this should never happen, but in
> > the real world it happens all the time.  If you manage relationships
> > using a system generated simple PK then this situation suddenly
> > becomes a lot less painful.
>
> Robert Walker wrote:
> >> CREATE TABLE states
> >> (
> >>    CODE  VARCHAR(2), -- This would be the primary key
> >>    DESCRIP  VARCHAR(50),
> >>    PRIMARY KEY (CODE)
> >> );
> >
> > One other noteworthy thing to mention about this design is this:  What
> > happens when your client comes to you and asks to change a
"CODE"
> > value to something different?  I know this should never happen, but in
> > the real world it happens all the time.  If you manage relationships
> > using a system generated simple PK then this situation suddenly
> > becomes a lot less painful.
>
> In the example I gave above (for a STATES lookup table), I doubt that
> there will be another state added to the U.S. (at least in my lifetime),
> or that they will change the state abbreviation for any of the existing
> states.
>
> Adding a non-intelligent primary key to this sort of lookup table adds
> another level of complexity that isn''t needed and decreases
database
> performance.  Storing a code (such as NY, CA, FL, etc) gives meaning to
> the data that is stored, whereas storing a number removes meaning.  In
> my career, I have never seen a state stored in a transaction table as a
> number.
>
> This also seems to goes against one of RoR''s claims, which is that
the
> code should work around the client and not vice-versa.
>
> Finally, there is such a thing as over-normalizing data.
>
> --
> Posted via http://www.ruby-forum.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
-~----------~----~----~----~------~----~------~--~---

Geo Pappas

2007-Apr-10 20:35 UTC

head link

Re: How to Work with Tables That Don''t Have a Numeric PK?

Brian Hogan wrote:> Why store states in a table when a simple hash will do? Databases
don''t
> need
> to be used for everything.
Can we please move out of the philosophical realm of why something is 
done and answer the question at hand:

How can RoR be used when natural keys are used in a table?

-- 
Posted via http://www.ruby-forum.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
-~----------~----~----~----~------~----~------~--~---

Robert Walker

2007-Apr-10 21:32 UTC

head link

Re: How to Work with Tables That Don''t Have a Numeric PK?

I believe the question was answered.  If you decide to go against the
convension of the framework then you need to step outside the
framework and either code a solution yourself or find something that
has already done so for you.

On Apr 10, 4:35 pm, Geo Pappas
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:> Brian Hogan wrote:
> > Why store states in a table when a simple hash will do? Databases
don''t
> > need
> > to be used for everything.
>
> Can we please move out of the philosophical realm of why something is
> done and answer the question at hand:
>
> How can RoR be used when natural keys are used in a table?
>
> --
> Posted viahttp://www.ruby-forum.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
-~----------~----~----~----~------~----~------~--~---

Geo Pappas

2007-Apr-10 21:55 UTC

head link

Re: How to Work with Tables That Don''t Have a Numeric PK?

Can someone please point me to an example of how to use a 
natural/intelligent key through code?

Or if you have nothing better to do, provide some code for me to start 
with...

-- 
Posted via http://www.ruby-forum.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
-~----------~----~----~----~------~----~------~--~---

Lee

2007-Apr-11 02:33 UTC

head link

Re: How to Work with Tables That Don''t Have a Numeric PK?

State Migration:

class CreateStates < ActiveRecord::Migration
  def self.up
    create_table :states, :id => false do |t|  # Turns off the id
field for primary key
      t.column :code, :string, :limit => 2
      t.column :name, :string
    end
  end

  def self.down
    drop_table :states
  end
end

State Model:

class State < ActiveRecord::Base
  self.primary_key = "code"
end

Instantiate Wisconsin:

wi = State.new
wi.id = "WI"
wi.name = "Wisconsin"
wi.save

Instantiation Note:  You can only access wi.code after the object has
been saved.

You can now call:  State.find("WI")  and it returns the proper state
object.

AR Associations:

Company Migration:

class CreateCompanies < ActiveRecord::Migration
  def self.up
    create_table :companies do |t|
      t.column :state_id, :string, :limit => 2  #
''state_id'' is the
Rails convention for fk
      t.column :name, :string
    end
  end

  def self.down
    drop_table :companies
  end
end

Company Model:

class Company < ActiveRecord::Base
  belongs_to :state  # Note: this is assuming you used "state_id"
rails convention
end

Coding:

bk = Company.new
bk.name = "Burger King"
bk.state = State.find("WI")
bk.save

You can call:

bk.state.code #=> "WI"
bk.state.name #=> "Wisconsin"

I have not tested other Rails functionality, but all of these examples
will work for you.  However, I agree with everyone else that the best
practice is just to use the integer primary keys or to follow Brian''s
suggestion and create a non-ActiveRecord State model.  Brian''s
suggestion would save you database hits for data that never changes
and if put in the models folder will be auto-loaded for you.  I would
guess asking the DBMS to compare simple integers is more efficient
than comparing strings.  Or you can save database hits altogether by
keeping the states list out of the database and just storing the state
code in the table.

Goodluck,

Lee Hericks


--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---

Rails - Apr 2007 - How to Work with Tables That Don't Have a Numeric PK?

How to Work with Tables That Don''t Have a Numeric PK?

Re: How to Work with Tables That Don''t Have a Numeric PK?

Re: How to Work with Tables That Don''t Have a Numeric PK?

Re: How to Work with Tables That Don''t Have a Numeric PK?

Re: How to Work with Tables That Don''t Have a Numeric PK?

Re: How to Work with Tables That Don''t Have a Numeric PK?

Re: How to Work with Tables That Don''t Have a Numeric PK?

Re: How to Work with Tables That Don''t Have a Numeric PK?

Re: How to Work with Tables That Don''t Have a Numeric PK?

Re: How to Work with Tables That Don''t Have a Numeric PK?