Help with Associations in Rails 3

Question

Ruby: 1.9.2
Rails: 3.0beta3

I need some help with associations in Rails 3.

I have the following models (see excerpts below):

School, State, SchoolLocale

The schools table has the following fields:

id, name, state_id, school_locale_id

The states table has the following fields:

id, abbr, name

The school_locales table has the following fields:

id, code, name

Unfortunately, my data-source didn't have IDs for school_locales. Thus, the data stored in the 'school_locale_id' field in the schools table actually maps to the 'code' field in the school_locales table.

school.rb:

class School < ActiveRecord::Base
    belongs_to :state
    belongs_to :school_locale
end

state.rb:

class State < ActiveRecord::Base
    has_many :schools
end

school_locale.rb:

class SchoolLocale < ActiveRecord::Base
    has_many :schools
end

I would like a query for a given school, let's say School.find(1), that would output the school name, the state name and the school-locale name. I assume that I need to add an index to the 'code' field in the school_locales table and somehow specify it as a foreign key, but I'm not certain. Any help would be appreciated.

Answer 1

This doesn't exactly answer your question, but I think it is a useful bit of information. Regarding your use of a states table, let me refer to Surrogate Vs. Natural/Business Keys.

@Ted says here:

Remember there is nothing special about a primary key, except that it is labelled as such. It is nothing more than a NOT NULL UNIQUE constraint, and a table can have more than one.

If you use a surrogate key, you still want a business key to ensure uniqueness according to the business rules.

There's no point in having a state_id foreign key that links to a states table. Each state already has a unique id; its 2-letter abbreviation. This unique id is just as good as a numeric one. And because this data doesn't change often, there's no harm in having it statically defined within your application somewhere.

Answer 2

I'm not really sure about it, but you could try using this:

class SchoolLocale < ActiveRecord::Base
    has_many :schools, :primary_key => :code
end

Let me know if it works :]