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.

   CODE VARCHAR(2), -- This would be the primary key

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.

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

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.

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

  def self.down
    drop_table :states

State Model:

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

Instantiate Wisconsin:

wi = = "WI" = "Wisconsin"

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

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

  def self.down
    drop_table :companies

Company Model:

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


bk = = "Burger King"
bk.state = State.find("WI")

You can call:

bk.state.code #=> "WI" #=> "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.


Lee Hericks