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