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?
On 4/10/07, Geo P. [email protected] 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.
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.
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 P. [email protected]
Why store states in a table when a simple hash will do? Databases don’t
need
to be used for everything.
Robert W. 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 W. 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.
Brian H. 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?
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…
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 P. [email protected]
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