Odd model structure, what associations?

rubyonrails-talk
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 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

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 Yandell