# N-way joins

**URL:** <https://www.ruby-forum.com/t/n-way-joins/95892>\
**Category:** Rails\
**Created:** [January 21, 2006, 9:23am UTC](https://www.ruby-forum.com/t/n-way-joins/95892 "2006-01-21T09:23:31Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![brian](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/97f17d/32.png) [@brian](https://www.ruby-forum.com/u/brian)\
**Post date:** [January 21, 2006, 9:23am UTC](https://www.ruby-forum.com/t/n-way-joins/95892/1 "2006-01-21T09:23:31Z")

</div>

Hi,

I’m somewhat of a Rails newbie and am trying to understand how to  
formulate n-way (3 or 4 way) joins in Rails (where the join tables  
contain extra data as well.)

Let me give you my basic entities:

foos  
id - pk  
name - unique

bars  
id - pk  
name - unique

bazs  
id - pk  
name - unique

frozs  
id - pk  
name - unique

then i have two separate join tables:

foos\_bars\_bazs - 3 way join  
foo\_id  
bar\_id \> primary key/unique  
baz\_id/  
value

foos\_bars\_bazs\_frozs - 4 way join  
froz\_id  
foo\_id   
bar\_id \ primary key/unique  
baz\_id /  
value

Now, my use cases for access are:

1. Given a foo, find all { bar, baz, value } associated with it (i’d  
really like to get back bars and bazs, not just their ids)
2. Given a froz and froo, find all { bar, baz, values } associated with  
them (again, i’d like to get bar and baz back as objects, not id’s }
3. Given a foo, insert a new {foo,bar,baz,value} tuple into the 3 way  
join.
4. Given a froz and a food, insert a new {froz,foo,bar,baz,value} tuple  
into the 4 way join.

I’ll omit my attempts to model this so far, as they’ve failed. Can  
someone give me a hand in understanding how to model this using  
ActiveRecord?

Thanks!  
Brian

---

<div class="post-metadata">

**Author:** ![brian](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/97f17d/32.png) [@brian](https://www.ruby-forum.com/u/brian)\
**Post date:** [January 21, 2006, 5:59pm UTC](https://www.ruby-forum.com/t/n-way-joins/95892/2 "2006-01-21T17:59:35Z")

</div>

Hmm, the only FK’s are in the join table. The other tables are all  
domain tables.

-movies  
id (PK)  
name (unique)

-actors  
id (PK)  
name (unique)

-magazines  
id (PK)  
name (unique)

-writers  
id (PK)  
name (unique)

So suppose we’re trying to track media coverage of movies, artists, and  
movies+artists. We’ll always have a magazine + writer, but articles can  
either be about movies, artists, or artists in the context of a movie.  
So our tables would be:

movies\_actors\_magazines\_writers  
movie\_id (FK) |  
actor\_id (FK) | PK  
magazine\_id (FK) |  
writer\_id (FK) |  
excerpt

movies\_magazines\_writers  
movie\_id (FK) |  
magazine\_id (FK) |  
writer\_id (FK) |  
excerpt

actors\_magazines\_writers  
actor\_id (FK) | PK  
magazine\_id (FK) |  
writer\_id (FK) |  
excerpt

So my retrieval use cases are:

1. Given an actor, find me all excerpts and then display a list of  
excerpts with the magazine name and writer name displayed alongside.

2. Given a movie, find me all excerpts and then display a list of  
excerpts with the magazine name and writer name displayed alongside.

3. Given an actor in the context of a movie, find me all excerpts and  
then display a list of excerpts with the magazine name and writer name  
displayed alongside.

In terms of update/create/delete, it will also be oriented towards the  
actors, movies, and (actor,movie)s. The user can perform creation,  
updates, and deletion of magazine/writer/excerpts ON an actor, movie, or  
(actor,movie).

I appreciate any help you can offer with how to model this with objects.  
If you have criticisms of the schema, however, I ask that you please  
suspend them, as I’m really just trying to learn about the ORM aspects  
or Rails.

Thanks!  
Brian

---

<div class="post-metadata">

**Author:** ![brian](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/97f17d/32.png) [@brian](https://www.ruby-forum.com/u/brian)\
**Post date:** [January 21, 2006, 2:23pm UTC](https://www.ruby-forum.com/t/n-way-joins/95892/3 "2006-01-21T14:23:12Z")

</div>

Where are foreign keys? For example, is it:

foos  
id  
name

bars  
id  
foo\_id  
name

or

foos  
id  
bar\_id  
name

bars  
id  
name

Also, this might be easier to understand if you use the real names of  
the  
objects you are talking about

---

<div class="post-metadata">

**Author:** ![brian](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/97f17d/32.png) [@brian](https://www.ruby-forum.com/u/brian)\
**Post date:** [January 21, 2006, 6:19pm UTC](https://www.ruby-forum.com/t/n-way-joins/95892/4 "2006-01-21T18:19:54Z")

</div>

Brian,

I’m no expert about db normalization. I’m not sure exactly what you  
want to do, of course. Given my qualifications on your problem, I  
don’t think you want tables like movies\_actors\_magazines\_writers _and_  
movies\_magazines\_writers because all the information in the latter is  
contained by the former.

Don’t you really want to relate movies and actors to articles which  
have a magazine and a author?

Wouldn’t these five two-way join tables would be the most flexible?

actors\_movies  
articles\_movies  
actors\_articles  
articles\_authors  
articles\_magazines

This way you can have muliple authors per article easily.

Also if you want to add director to the mix you only need to add one  
table called directors\_movies. (you don’t need many complicated tables  
like movies\_directors\_actors\_magazines\_authors)

Hope this helps.

Peter

---

<div class="post-metadata">

**Author:** ![brian](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/97f17d/32.png) [@brian](https://www.ruby-forum.com/u/brian)\
**Post date:** [January 21, 2006, 6:29pm UTC](https://www.ruby-forum.com/t/n-way-joins/95892/5 "2006-01-21T18:29:14Z")

</div>

In the rails book DHH talks about join tables that want to be models  
(page 241). This discussion relates to the case where the join model  
has extra data. This also prepares you for the has\_many :through  
feature that is upcomming in rails 1.1. This is instead of using the  
has\_and\_belongs\_to\_many feature which has shortcomings.

Naming the join tables so that they seam like models is something I’m  
not good at yet. You could try things like

actors\_movies -\> performances  
articles\_movies -\> movie\_publicities\*  
actors\_articles -\> actor\_publicities\*  
articles\_authors -\> authorship  
articles\_magazines -\> magazine\_articles or article\_publications

Another thought. Only the articles\_movies and actors\_articles join  
tables need to have the excerpts you listed before.

I still could be barking up the wrong tree for you.

-Peter

- maybe publicities is not a word

---

<div class="post-metadata">

**Author:** ![brian](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/97f17d/32.png) [@brian](https://www.ruby-forum.com/u/brian)\
**Post date:** [January 21, 2006, 6:59pm UTC](https://www.ruby-forum.com/t/n-way-joins/95892/6 "2006-01-21T18:59:54Z")

</div>

> If you have criticisms of the schema, however, I ask that you please  
> suspend them

I must read to the bottom of the post. Too early in the day.

> I do not have the ability to refactor

bummer

Peter

---

<div class="post-metadata">

**Author:** ![brian](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/97f17d/32.png) [@brian](https://www.ruby-forum.com/u/brian)\
**Post date:** [January 21, 2006, 6:39pm UTC](https://www.ruby-forum.com/t/n-way-joins/95892/7 "2006-01-21T18:39:11Z")

</div>

Hi Peter,

Thanks for the reply. The tables are really just examples. I have some  
legacy tables with an essentially identical schema that I do not have  
the ability to refactor and I’m wondering if/how they could be modeled  
in Rails objects.

Thanks,  
Brian

---

<div class="post-metadata">

**Author:** ![brian](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/97f17d/32.png) [@brian](https://www.ruby-forum.com/u/brian)\
**Post date:** [January 22, 2006, 5:52am UTC](https://www.ruby-forum.com/t/n-way-joins/95892/8 "2006-01-22T05:52:47Z")

</div>

Brian,  
I had a similar(ish) requirement a few weeks ago and followed a model  
given by Chris H. as follows;

I might suggest the following (and I am making the asusmption that the  
relationships between projects, people and roles is unlimited

(untested)

## people\_projects\_roles (join table between people and projects and roles)

person\_id  
role\_id  
project\_id

class Project \< ActiveRecord::Base  
has\_and\_belongs\_to\_many :people, :join\_table =\>  
“people\_projects\_roles”  
has\_and\_belongs\_to\_many :roles, :join\_table =\>  
“people\_projects\_roles”  
end

class Person \< ActiveRecord::Base  
has\_and\_belongs\_to\_many :projects, :join\_table =\>  
“people\_projects\_roles”  
has\_and\_belongs\_to\_many :roles, :join\_table =\>  
“people\_projects\_roles”  
end

class Role \< ActiveRecord::Base  
has\_and\_belongs\_to\_many :people, :join\_table =\>  
“people\_projects\_roles”  
has\_and\_belongs\_to\_many :projects, :join\_table =\>  
“people\_projects\_roles”  
end

now, this allows you to do things such as

project = Project.find(1) # “my project”

# all people who are associated with “my project” (any role)

project.people

# all roles associated with “my project”

project.roles

person = Person.find(1) # “John S.”

# all projects assocated with “John S.”

person.projects

# all roles assocated with “John S.”

person.roles

role = Role.find(1) # “programmer”

# all projects with a “programmer” role

role.projects

# all people with a “progammer” role

role.people

now say you want to add John as a “manager” (id = 2) role to Project 10

john = Person.find\_by\_name(“John”)  
manager = Role.find\_by\_name(“Manager”)  
project.find(10)

with this information, you could do it several different  
ways…depending on the situation

project.people.push\_with\_attributes(john, :role\_id =\> manager.id)  
project.roles.push\_with\_attributes(manager, :person\_id =\> john.id)  
john.projects.push\_with\_attributes(project, :role\_id =\> manager.id)  
john.roles.push\_with\_attributes(manager, :project\_id =\> project.id)  
role.projects.push\_with\_attributes(project, :person\_id =\> john.id)  
role.people.push\_with\_attributes(john, :project\_id =\> project.id)

each of these accomplish the same thing, they add John as a Manager to  
Project 10

now, as far as organisation/projects/roles go, that sounds strange (not  
being critical)…can an organisation have the same roles as a person?  
i would assume that organisations have different roles than people so  
you will want to setup a separate “org roles” table to manage those.

can an organisation be involved in many projects and can a project have  
many organisations (stakeholders)? if so, then i would setup another  
join table between organisations/projects/org roles and follow the same  
idea as above

now, one thing i would be concerned about is corss referencing (not sure  
the proper term). you have people associated with projects,  
organisations assocated with projects and people associated with  
organisations…this can get messy when you want to start limiting who  
can do based upon their other associations (ie, given a  
project/organisation association, can only people associated with the  
same organisation be assocated with that project?)

hope this helps.

On 1/3/06, Gerard [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

```
 Eric,

 I don't comletely understand the definition of the entity Role.

```

Possibly  
usefull is to remember that when a project is closed or a person is  
removed  
(started working for another company) that the data is stil  
accessible. To  
simplify this. When a qoute or invoice is generated there’s always  
a copy  
used of the contacts data. Because when that person doesn’t work  
for a  
company anymore and is removed I would still want to be able to  
view the  
quote/invoice/whatever without getting ‘nil’ object errors.

```
 Furthermore a look on what historical info you might save could

```

possibly help  
as well on the relationships you want to maintain.

```
 Looks like the 2nd one seems good (couldn't say why though). But

```

how does a  
project have many roles. Doesn’t a project have many people who, in  
there  
turn, have many roles within a project?

```
 I'm on the virge of diving into a similar mather on my internal

```

administration  
appliation and I must admit this fun stuff to chew on … 🙂

```
 Hope (wonder if) it helps.

 Regards,

 Gerard.

 On Monday 02 January 2006 02:10, Eric S. tried to type

```

something like:  
\> Hi,  
\> I’m trying to figure the most efficient way to model the  
following. I  
\> can think of at least two ways to relate the tables but from a  
\> client/server perspective! I’m wondering how to best (and  
\> elegantly)relate them from an AR perspective.  
\>  
\> A project has many people,  
\> A person can work on many projects at any time,  
\> A project has many roles,  
\> A role is performed by a person,  
\> A person may perform multiple roles,  
\> An organisation has many people,  
\> An organisation is a stakeholder (God, I hate that word - makes  
me feel  
\> like Dracula surrounded!)in one or more projects,  
\> A stakeholder has many roles within a project.  
\>  
\> So one way I have  
\>  
\> Projects HABTM Roles  
\> Roles HABTM People  
\> Organisation Has\_Many People  
\> An Organisation Belongs\_to a Stakeholder  
\> A Stakeholder HABTM Projects  
\> A Stakeholder Has\_Many Roles  
\>  
\> Or  
\>  
\> Projects HABTM People,  
\> A Project has\_many roles,  
\> People HABTM Roles,  
\> An Organisation has\_many People,  
\> An Organisation is a Stakeholder in a Project,  
\> A Stakeholder has\_many Roles in a Project.  
\>  
\> I guess the outcome I’m after is a way to view this data from  
various  
\> perspectives. For example, I have a project view that presents  
static  
\> project data at the head of the screen with a set of tabs  
containing  
\> partials with forms for editing stuff like e.g. People  
\> Acting\_For(Stakeholder), Acting\_As (Role). Other perspectives  
would be  
\> like seeing which organisations are doing what within any number  
of  
\> projects - that sorta thing.  
\>  
\> Whatya think?  
\> Eric.

What Chris suggested certainly provided a lot of information in one hit

- possibly way too much. There is some further need to eliminate  
join\_table records that do not belong to the current job record cos when  
you query this you get all records that fit any of the three criteria.

Anyways,  
Hope that helps  
Eric.