Rails - May 2007 - 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. :)

Thanks,

Adam


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Ok, step back, take a deep breath...you''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''
     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/

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---