Odd model structure, what associations?

Rails
#1

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 P.mer: requires Programmer at level 3
Skill: Rails Programmer: requires Ruby P.mer 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

#2

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

#3

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 Y.
http://notahat.com/