# Odd model structure, what associations?

**URL:** <https://www.ruby-forum.com/t/odd-model-structure-what-associations/104003>\
**Category:** Rails\
**Created:** [May 12, 2007, 7:42pm UTC](https://www.ruby-forum.com/t/odd-model-structure-what-associations/104003 "2007-05-12T19:42:10Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![adambeazley](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/5f9b8f/32.png) [@adambeazley](https://www.ruby-forum.com/u/adambeazley)\
**Post date:** [May 12, 2007, 7:42pm UTC](https://www.ruby-forum.com/t/odd-model-structure-what-associations/104003/1 "2007-05-12T19:42:10Z")

</div>

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. 🙂

Thanks,

Adam

---

<div class="post-metadata">

**Author:** ![adambeazley](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/5f9b8f/32.png) [@adambeazley](https://www.ruby-forum.com/u/adambeazley)\
**Post date:** [May 13, 2007, 6:35am UTC](https://www.ruby-forum.com/t/odd-model-structure-what-associations/104003/2 "2007-05-13T06:35:51Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![adambeazley](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/5f9b8f/32.png) [@adambeazley](https://www.ruby-forum.com/u/adambeazley)\
**Post date:** [May 14, 2007, 2:17am UTC](https://www.ruby-forum.com/t/odd-model-structure-what-associations/104003/3 "2007-05-14T02:17:46Z")

</div>

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 Y.

> **[Pete Yandell — Principal Engineer / Architect](https://notahat.com/)**
>
> Pete Yandell is an experienced Principal Software Engineer and Architect based in Melbourne, Australia.