Rails 3, jQuery and multiselect dependencies part 2.

This article is a follow up to another article on this website which focuses on using jQuery to manage the processing of multiple inter-dependent select lists on a single web page. I had a number of requests to explain the data model in a bit more detail so here goes.

I have worked on a number of internationalised (in the US, that’s spelt internationalized) applications where the selection of a ‘home country’ by a user means the display of their address fields should change to reflect their selection of a country. I’ve used this example as the basis of the multi-select article.

MySQL Data Structure

The relevant tables are in a MySQL database and are as follows:

  • locales
  • countries
  • countrynames
  • regionnames
  • regions
  • cities
  • citynames
  • users

The structure of each of the tables is as follows. Note, this is by no means a strict design criteria for your tables, it’s just a design decision that I made because it made sense to me and my warped view of the world.

The locales table contains a listing of a possible 29 locales that can be used to tailor how your application displays and interprets data

mysql> desc locales;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  | UNI | NULL    |       |
| locale   | char(5)     | NO   | PRI | NULL    |       |
| name     | varchar(60) | NO   |     | NULL    |       |
| fullname | varchar(60) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

The countries table contains core country information including their names in English, their international code and their latitude and longitude.

mysql> desc countries;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int(11)     | YES  | UNI | NULL    |       |
| code      | char(2)     | NO   | PRI | NULL    |       |
| code3     | char(3)     | YES  |     | NULL    |       |
| numcode   | smallint(6) | YES  |     | NULL    |       |
| url       | varchar(50) | NO   | MUL | NULL    |       |
| name      | varchar(50) | NO   | UNI | NULL    |       |
| latitude  | double      | NO   |     | NULL    |       |
| longitude | double      | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

The countrynames table contains the names of countries as it is interpreted in each locale. For example the country Andorra is the same in most locales ‘Andorra’ but in Russian it is ‘Андорра’.

mysql> desc countrynames;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | YES  | UNI | NULL    |       |
| country_id | int(11)     | NO   |     | NULL    |       |
| locale_id  | int(11)     | YES  |     | NULL    |       |
| name       | varchar(60) | NO   |     | NULL    |       |
| fullname   | varchar(60) | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

Now we’re into the messy bit. If you live in the United States, well it’s like it says on the box…it’s united ‘States’; however if you live in the United Kingdom it’s all about counties. If like me you live in Australia, we have both States and Territories (it’s a long story..look up the difference on google). Regardless of the type of country divisions, the name I’ve used is ‘Region’.

The regions table defines the region names within countries.

mysql> desc regions;
+------------+-----------------+------+-----+---------+----------------+
| Field      | Type            | Null | Key | Default | Extra          |
+------------+-----------------+------+-----+---------+----------------+
| id         | int(4) unsigned | NO   | PRI | NULL    | auto_increment |
| country_id | int(11)         | YES  |     | NULL    |                |
| code       | char(3)         | NO   |     | NULL    |                |
| url        | varchar(50)     | NO   |     | NULL    |                |
| name       | varchar(50)     | NO   |     | NULL    |                |
| latitude   | double          | NO   |     | NULL    |                |
| longitude  | double          | NO   |     | NULL    |                |
+------------+-----------------+------+-----+---------+----------------+

The localised version of the regions table is regionnames.

mysql> desc regionnames;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int(11)     | NO   | PRI | 0       |       |
| region_id | int(11)     | NO   |     | NULL    |       |
| locale_id | int(11)     | NO   |     | NULL    |       |
| name      | varchar(60) | NO   |     | NULL    |       |
| fullname  | varchar(60) | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

Next is the cities within the regions and their localised versions. These use the tables ‘cities’ and ‘citynames’ respectively. You can see the foreign key link to the country and region tables below.

The cities table

mysql> desc cities;
+------------+-----------------+------+-----+---------+----------------+
| Field      | Type            | Null | Key | Default | Extra          |
+------------+-----------------+------+-----+---------+----------------+
| id         | int(8) unsigned | NO   | PRI | NULL    | auto_increment |
| country_id | int(11)         | NO   |     | NULL    |                |
| region_id  | int(11)         | YES  |     | NULL    |                |
| url        | varchar(50)     | NO   |     | NULL    |                |
| name       | varchar(50)     | NO   |     | NULL    |                |
| latitude   | double          | NO   |     | NULL    |                |
| longitude  | double          | NO   |     | NULL    |                |
+------------+-----------------+------+-----+---------+----------------+

The citynames table holds the localised version of city names.

mysql> desc citynames;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int(11)     | YES  | UNI | NULL    |       |
| city_id   | int(11)     | NO   |     | NULL    |       |
| locale_id | int(11)     | YES  |     | NULL    |       |
| name      | varchar(60) | NO   |     | NULL    |       |
| fullname  | varchar(60) | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

There are other tables in the system including a users’ table with a locale_id which the user sets by selecting from a drop-down list on their profile-page. I haven’t included that table her because there’s also a bundle of application specific stuff in there too.

The Rails Schema

Now, like all good rails developers, you’re using schema migrations. If you’re not, go stand in the corner for twenty minutes and think about spending the same twenty minutes every week wasted looking at the conjoin between two walls. Rails database migrations are the way to go. They take a little bit of getting used to but well worth it to keep all your team on the same page, or if you’re doing your bit as a solo programmer it means you can screw it up and rewind to the last working version with ease.

As an example, I’ll show you how to create a new schema file for the cities entity. I’ll leave the remaining entities for your own interpretation.

In Rails 2, you’d use the following line

$ ruby script/generate migration create_cities

In Rails 3, it’s more DRY

$ rails g migration create_cities

This will create a new file in you db/migrate folder ending in ‘create cities.rb’. Edit this accordingly and add the fields to the migration that reflect the structure of the table we want to create. My version looks like the following:

class CreateCities < ActiveRecord::Migration
  def self.up
    create_table :cities do |t|
      t.integer :country_id
      t.integer :region_id
      t.string :url, :limit => 50,  :default => "",    :null => true
      t.string :name, :limit => 50,  :default => "",    :null => false
      t.float :latitude
      t.float :longitude
    end
  end
  def self.down
    drop_table :cities
  end
end

The Models

In the root of your application, create the necessary models. Under Rails 3, that can be done by entering

$ rails g model city

A new file is generated under the app/models folder called city.rb.

The full listing of my models folder is as follows:

locale.rb  
city.rb
country.rb
region.rb
cityname.rb
countryname.rb
regionname.rb  
profile.rb
user.rb
role.rb
admin.rb

For the purposes of this discussion we’re interested in everything but the last 4 files. These are used to store user permissions, roles, and profile data.

The content of the relevant model files is used to implement the relationships between the different entities and some field level validation rules. There is nothing whiz-bang about this, just the use of the Rails convention over configuration principle at work.

Taking it from the top, my files look like the following:

$ app/models/locale.rb
class Locale < ActiveRecord::Base
  validates_presence_of :name
end
$ app/models/city.rb
class City < ActiveRecord::Base
  validates_presence_of :name

  belongs_to :country
  has_many :citynames
end
$ app/models/country.rb
class Country < ActiveRecord::Base
  validates_presence_of :name
  validates_presence_of :code

  has_many :regions
  has_many :citys
end
$ app/models/region.rb
class Region < ActiveRecord::Base
  validates_presence_of :name

  belongs_to :country
  has_many :regionnames
end
$ app/models/cityname.rb
class Cityname < ActiveRecord::Base
  belongs_to:city
  belongs_to:locale
end
$ app/models/countryname.rb
class Countryname < ActiveRecord::Base
  belongs_to :country
  belongs_to :locale
end
$ app/models/regionname.rb
class Regionname < ActiveRecord::Base
  belongs_to :region
  belongs_to :locale
end

So, there you have it, fairly simple really, a couple of tables, a couple of model files and the material from this article and the previous article should have you up and running. As always, any queries, comments or improvements, please let me know.

Share

2 Responses to “Rails 3, jQuery and multiselect dependencies part 2.”

  1. Alex @ April 29th, 2011

    Wonderful article! Any chance you may know a seed for the US cities and locales or do I have to do all this manually? Thanks, Alex

  2. william @ May 27th, 2011

    very good, thank you very much.
    fails to put it to work with simple_form. would be good for you to do that version as well. be helpful.

Leave a Reply



CAPTCHA Image
*

Spam protection by WP Captcha-Free