Inacom Tech Blog

While working on a presentation for the upcoming Twin Cities Code Camp. For the presentation I am using the Northwind database provided from Microsoft.  I have found a unique situation where they are using a 5 character string as the primary key.  Since the goal of this presentation is to develop a rails site around an existing database I took on this challenge to overcome rails standards.

First you want to set up your model to reflect the database structure

class Customer < ActiveRecord::Base
   set_table_name "Customers"      
      set_primary_key "CustomerID"    
        validates_uniqueness_of :CompanyName ## This is to eliminate duplicate primary keys.  
end

Next to create the primary key you have to do some hacking. Rails likes to use database incrementing primary keys and does not let you set the primary key id so you have to create a "before_create" method as seen below.

def before_create
  ## CustomerID needs to be a 5 char all upcase string, to match current DB layout
  self.CustomerID = (self.CompanyName.gsub(" ", "")[0,5]).upcase
end

In this method I decided to use a pattern that I noticed the primary keys following. My observation was that they were using the first 5 alpha characters of the company name and uppercasing them. So this method reflects setting the primary key "CustomerID" to the company name minus out the whitespace and taking the first 5 characters. The reason for using a before create is you should not have to update the primary key ever as many records may be linked to that, so using a "before_create" keeps our code safe and dry. Below is a test case for the customer table of the Northwind database using the before create method.

def test_should_be_able_to_create_customer
  @customer = Customer.new(
                           :CompanyName => "Inacom", 
                           :ContactName => "Chris Johnson", 
                           :ContactTitle => "Solutions Developer",
                           :Address => "123 My Street",
                           :City => "Madison",
                           :Country => "USA",
                           :Phone => "608.555.5555",
                           :Fax => "608.555.5555")
  assert @customer.save
  
  assert_equal(@customer.CustomerID, Customer.find_by_CompanyName("Inacom").CustomerID)
  assert_equal("INACO", Customer.find_by_CompanyName("Inacom").CustomerID)
end