Odd model structure, what associations?

Hey all,

I have this (from my limited perspective at least) somewhat
complicated data structure, and I'm having trouble wrapping my head
around the associations I need.

Here's how it works, I'm trying to model a tree of skills. For anyone
who's ever played RPGs this should be familiar. But you can imagine
something like this:

Skill: Programmer
Skill: Java Programmer: requires Programmer at level 1
Skill: Ruby Programmer: requires Programmer at level 3
Skill: Rails Programmer: requires Ruby Programmer at level 3 and
Programmer at level 4.

So the skill requirements are self-referential associations, but with
extra data. A skill can have any number of skill requirements.

This is what I envisioned:

Model: Skill, has_many RequiredSkill, belongs_to RequiredSkill (other
data: name, rank)
Model: RequiredSkill, belongs_to Skill, has_one Skill (other data:
required level)

The problem is that a Skill can belong to any number of
RequiredSkills, so the simple belongs_to association doesn't work. On
the other hand, a has_and_belongs_to_many association can't work,
because a RequiredSkill doesn't belong_to any Skill that it has, nor
does a Skill belong_to any RequiredSkill that it has.

The solution may very well be a simple one, but I don't have enough
experience with join tables to figure it out. :slight_smile:

Thanks,

Adam

I'm thinking something like this, but I don't know how to implement
it:

class RequiredSkill
     has_many :skills #some association data
     belongs_to :skill #more association data
end

class Skill
     has_many :required_skills #some association data
     belongs_to :required_skill #more association data
end

Then in addition to the regular tables (but without foreign_keys), we
have two join tables:
(psudocode)
The first one holds the associations between a required_skill and it's
skill.
table required_skills_skill
   column "required_skill_id", :integer
   column "skill_id", :integer
end

This table would be accessed when it calls a_reqskill.skills, but
never when it calls a_skill.required_skills

Then the second one holds associations between a skill and it's
required skills:
table skill_required_skills
     column "skill_id", :integer
     column "required_skill_id", :integer
end

Similar to the last, this table would be accessed when it calls
a_skill.required_skills, but never when it calls a_reqskill.skills.

I think the theory is sound, but I have no idea if this is possible to
implement. Any thoughts?

Thanks,

Adam

Ok, step back, take a deep breath...you're overcomplicating things here. :slight_smile:

What you have is an example of a self-referential many-to-many relationship. A skill can require a number of other skills, and any skill can be required by a number of other skills.

Let's start by pulling the idea of a requirement out into its own table:

     class Requirement
         column :skill_id, :integer
         column :required_skill_id, :integer
         column :required_level, :integer

         belongs_to :skill
         belongs_to :required_skill, :class_name => 'Skill', :foreign_key => 'required_skill_id'
     end

(I'm mixing migration and model code here, which you can't do, but I hope my intent is clear.)

So each requirement has a skill doing the requiring, a skill being required, and the level of the required skill.

Now you can do:

     class Skill
         has_many :requirements
         has_many :required_by, :class_name => 'Requirement', :foreign_key => 'required_skill_id'
     end

Easy, huh?

You can then try fun queries like:

     skill.requirements.find(:all, :include => :required_skill)

to pull out all of a skill's requirements, and their associated skills, with a single query.

Hope that helps!

Cheers,

Pete Yandell
http://notahat.com/