Managing MySQL Table Relations with Rails' Active Record Association Types

In last week's article, I showed you how Rails' powerful migrations feature eliminates the long-standing chasm separating the way databases and code are maintained by offering a way to programatically manage table structures. Combined with Rails' Active Record implementation, programmers are able to more effectively integrate databases into applications because the need to switch from "programmer" mode to "database administrator" mode has been practically eliminated.

In this installment, I'll delve deeper these features, showing you how Active Record can help you to manage table relations more effectively than ever. By using association types and a simple way to assign foreign keys to other tables, you can automate what is otherwise often a tedious and confusing process when creating table relationships. Along the way, I'll introduce a variety of other database concepts as applied to Rails, which should be of great help if you're new to Rails and are trying to figure out how to create a simple database-driven Rails application.

As always, the discussion will be based around a real-world theme, namely a web-based address book. For purposes of demonstration, the database will consist of just two tables: contacts and states. The contacts table will contain the names, email addresses, cities, and states of your respective contacts. Because you don't want to encounter a data corruption problem by potentially entering an incorrect two-letter state abbreviation, you'll populate the states table with a list of allowable abbreviations, and force the user to choose from that list by way of a drop-down list.

To begin, go ahead and create the new Rails project:

%>rails addressbook

Creating the Models

Next, you'll need to create the application models, which will also configure the migration skeletons. Only two models are needed: Contact and State.

%>ruby script/generate model Contact
   exists  app/models/
   exists  test/unit/
   exists  test/fixtures/
   create  app/models/contact.rb
   create  test/unit/contact_test.rb
   create  test/fixtures/contacts.yml
   create  db/migrate
   create  db/migrate/001_create_contacts.rb

%>ruby script/generate model State
   exists  app/models/
   exists  test/unit/
   exists  test/fixtures/
   create  app/models/state.rb
   create  test/unit/state_test.rb
   create  test/fixtures/states.yml
   exists  db/migrate
   create  db/migrate/002_create_states.rb

Creating the Migrations

Next, you'll need to use the migrations feature as introduced in the last article to create the two table schemas. I won't go into details regarding how to execute these migrations (because the concept was introduced in the aforementioned tutorial), and instead just show you the migration scripts for 001_create_contacts.rb and 002_create_states.rb:

001_create_contacts.rb

class CreateContacts > ActiveRecord::Migration
   def self.up
      create_table :contacts do |table|
         table.column :name,  :string, :limit => 100,
                      :null => false
         table.column :email, :string, :limit => 100,
                      :null => false
         table.column :city,  :string, :limit => 100,
                      :null => false
         table.column :state_id, :integer, :null => false
      end
   end

   def self.down
      drop_table :contacts
   end
end

Each of the contacts columns should be self-explanatory, except perhaps for state_id. You'll use <state_id> to map each contact to a corresponding state found in the states table. How this is done with Active Record will be explained later in the tutorial.

002_create_states.rb

class CreateStates < ActiveRecord::Migration
   def self.up
      create_table :states do |table|
         table.column :abbreviation, :string, :limit => 2,
                      :null => false
      end
   end

   def self.down
      drop_table :states
   end
end

Go ahead and execute these migrations, and you should see the following table schemas in your MySQL database:

%>DESCRIBE contacts;
+----------+--------------+------+-----+----------+----------------+
| Field    | Type         | Null | Key | Default  | Extra          |
+----------+--------------+------+-----+----------+----------------+
| id       | int(11)      | NO   | PRI | NULL     | auto_increment |
| name     | varchar(100) | NO   |     |          |                |
| email    | varchar(100) | NO   |     |          |                |
| city     | varchar(100) | NO   |     |          |                |
| state_id | int(11)      | NO   |     |          |                |
+----------+--------------+------+-----+----------+----------------+

%>DESCRIBE states;
+--------------+------------+------+-----+---------+----------------+
| Field        | Type       | Null | Key | Default | Extra          |
+--------------+------------+------+-----+---------+----------------+
| id           | int(11)    | NO   | PRI | NULL    | auto_increment |
| abbreviation | varchar(2) | NO   |     |         |                |
+--------------+------------+------+-----+---------+----------------+