Dirty Little Secret

I’ve been doing more Rails development lately (yay!) but have something shameful to admit:

I don’t care what variant of many-to-many relationship the models have, I bulldoze with a same implementation everytime.

The nuances and gotchas are too much for my liking. So rather than spending time groking them and risk seeing them deprecated (not complaining, just note things are moving fast), I’d prefer to rely on simpler concepts which are hence more stable and has least-surprise behaviors. Use the time saved to think about real important stuff: business requirements.

Simplifying the concept will mean a tiny bit more code - but lesser wrestling with the framework overall, IMHO. To represent any many-to-many relationship, opt for a rich-association database setup everytime (aka include an auto_increment id in the join table), regardless of whether there’ll ultimately be extra attributes. Defer decisions. Hence, people.id == people_skills.person_id and  people_skills.skill_id == skills.id in the database will be represented in ActiveRecord as:

  class Person < ActiveRecord::Base
has_and_belongs_to_many :skills
has_many :people_skills, :dependent => :destroy
end

class Skill < ActiveRecord::Base
has_and_belongs_to_many :people
has_many :people_skills, :dependent => :destroy
end

class PeopleSkill < ActiveRecord::Base
set_primary_key :not_id # int auto_increment primary key, cannot be named 'id'
belongs_to :person
belongs_to :skill
end

There 2 lines more than rightfully necessary. But all the data will surely be accessible. If things turns out to be simple, your overhead is negligible. If requirements turns hairy, you’re still safe! No worrying over any potential has_many :through quirks, or lack of support with has_and_belongs_to_many. Just go with the above setup - get some code running! - and everything can be optimised later. Wrestling more and things will begin to feel like unnecessary situps to me…

[Update]
Fixed the sample code above, apparently PeopleSkill’s primary key should not be named :id. So its 3 additional lines instead of 2. A tour via ./script/console will be

  >> Person.delete_all
=> 0
>> Skill.delete_all
=> 0
>> PeopleSkill.delete_all
=> 0
>> s = Skill.create :name => "Swimming"
=> #<Skill:0xb7855b3c @new_record_before_save=true, @attributes={"name"=>"Swimming", "id"=>1}, @new_record=false, @errors=#<ActiveRecord::Errors:0xb78533a0 @errors={}, @base=#<Skill:0xb7855b3c ...>>>
>> jack = Person.create :name => "Jack"
=> #<Person:0xb784da04 @new_record_before_save=true, @attributes={"name"=>"Jack", "id"=>1}, @new_record=false, @errors=#<ActiveRecord::Errors:0xb784b268 @errors={}, @base=#<Person:0xb784da04 ...>>>
>> jill = Person.create :name => "Jill"
=> #<Person:0xb784181c @new_record_before_save=true, @attributes={"name"=>"Jill", "id"=>2}, @new_record=false, @errors=#<ActiveRecord::Errors:0xb783eb30 @errors={}, @base=#<Person:0xb784181c ...>>>
>> jack.skills << s
=> [#<Skill:0xb7855b3c @new_record_before_save=true, @attributes={"name"=>"Swimming", "id"=>1}, @new_record=false, @errors=#<ActiveRecord::Errors:0xb78533a0 @errors={}, @base=#<Skill:0xb7855b3c ...>>>]
>> jill.skills << s
=> [#<Skill:0xb7855b3c @new_record_before_save=true, @attributes={"name"=>"Swimming", "id"=>1}, @new_record=false, @errors=#<ActiveRecord::Errors:0xb78533a0 @errors={}, @base=#<Skill:0xb7855b3c ...>>>]
>> PeopleSkill.find(:all)
=> [#<PeopleSkill:0xb781206c @attributes={"not_id"=>"1", "skill_id"=>"1", "person_id"=>"1"}>, #<PeopleSkill:0xb7811ef0 @attributes={"not_id"=>"2", "skill_id"=>"1", "person_id"=>"2"}>]
>> jack.skills << Skill.create(:name => "Dancing")
=> [#<Skill:0xb7855b3c @new_record_before_save=true, @attributes={"name"=>"Swimming", "id"=>1}, @new_record=false, @errors=#<ActiveRecord::Errors:0xb78533a0 @errors={}, @base=#<Skill:0xb7855b3c ...>>>, #<Skill:0xb7809994 @new_record_before_save=true, @attributes={"name"=>"Dancing", "id"=>2}, @new_record=false, @errors=#<ActiveRecord::Errors:0xb7807f04 @errors={}, @base=#<Skill:0xb7809994 ...>>>]
>> jill.skills << Skill.create(:name => "Cooking")
=> [#<Skill:0xb7855b3c @new_record_before_save=true, @attributes={"name"=>"Swimming", "id"=>1}, @new_record=false, @errors=#<ActiveRecord::Errors:0xb78533a0 @errors={}, @base=#<Skill:0xb7855b3c ...>>>, #<Skill:0xb77f5930 @new_record_before_save=true, @attributes={"name"=>"Cooking", "id"=>3}, @new_record=false, @errors=#<ActiveRecord::Errors:0xb77f3b44 @errors={}, @base=#<Skill:0xb77f5930 ...>>>]
>> jack.skills(true)
=> [#<Skill:0xb77e2970 @attributes={"name"=>"Swimming", "id"=>"1", "not_id"=>"1", "skill_id"=>"1", "person_id"=>"1"}, @readonly=true>, #<Skill:0xb77e2934 @attributes={"name"=>"Dancing", "id"=>"2", "not_id"=>"3", "skill_id"=>"2", "person_id"=>"1"}, @readonly=true>]
>> jack.destroy
=> #<Person:0xb784da04 @new_record_before_save=true, @attributes={"name"=>"Jack", "id"=>1}, @skills=[], @new_record=false, @people_skills=[#<PeopleSkill:0xb77dce94 @attributes={"not_id"=>"1", "skill_id"=>"1", "person_id"=>"1"}>, #<PeopleSkill:0xb77dcd54 @attributes={"not_id"=>"3", "skill_id"=>"2", "person_id"=>"1"}>], @errors=#<ActiveRecord::Errors:0xb784b268 @errors={}, @base=#<Person:0xb784da04 ...>>>
>> PeopleSkill.find(:all)
=> [#<PeopleSkill:0xb77cef88 @attributes={"not_id"=>"2", "skill_id"=>"1", "person_id"=>"2"}>, #<PeopleSkill:0xb77cef38 @attributes={"not_id"=>"4", "skill_id"=>"3", "person_id"=>"2"}>]
>> quit

The reason the original sample code wasn’t working was not because of HABTM didn’t create the primary key (as mentioned by Josh in the comments), but rather its precisely because it did try to include the id column in the INSERT statement (with a wrong value) thus causing primary key clashes and blah blah blah. Strangely, by changing PeopleSkill’s primary key to be named something else, the INSERT statement would exclude it and insert only (`skill_id`, `person_id`), allowing MySQL to auto_generate the primary key.

As long as the primary keys of Person and Skill are not called "id", PeopleSkill can happily use "id" as its auto increment primary key (as is with my original work). My force is not strong enough to attempt a patch / further investigation for this HABTM issue. 

[Update 2]
Plugin available to make habtm behave: Download and unzip into your vendor/plugins folder.