Forum: Ruby on Rails Odd model structure, what associations?

Announcement (2017-05-07): is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see and for other Rails- und Ruby-related community platforms.
6ec1e89eef5ce09fbfa56be5b6dd17b5?d=identicon&s=25 Adam Bloom (admanb)
on 2007-05-12 19:42
(Received via mailing list)
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. :)


6ec1e89eef5ce09fbfa56be5b6dd17b5?d=identicon&s=25 Adam Bloom (admanb)
on 2007-05-13 06:35
(Received via mailing list)
I'm thinking something like this, but I don't know how to implement

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

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

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

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

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?


C9c7a65848f13e2b1a226bbe43bc3672?d=identicon&s=25 Pete Yandell (pete)
on 2007-05-14 02:17
(Received via mailing list)
Ok, step back, take a deep're overcomplicating things
here. :)

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'

(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'

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!


Pete Yandell
This topic is locked and can not be replied to.