# To Chris Hall - Re: DB Modelling the Rails way - Opinions?

**URL:** <https://www.ruby-forum.com/t/to-chris-hall-re-db-modelling-the-rails-way-opinions/59955>\
**Category:** Rails\
**Created:** [January 7, 2006, 3:16am UTC](https://www.ruby-forum.com/t/to-chris-hall-re-db-modelling-the-rails-way-opinions/59955 "2006-01-07T03:16:53Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![soderic](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/8baadc/32.png) [@soderic](https://www.ruby-forum.com/u/soderic)\
**Post date:** [January 7, 2006, 3:16am UTC](https://www.ruby-forum.com/t/to-chris-hall-re-db-modelling-the-rails-way-opinions/59955/1 "2006-01-07T03:16:53Z")

</div>

Chris,  
Finally got time to fully play with your suggestions. Had to re-code a  
bit of stuff but the concept works - just like a bought one :~). Many  
thanks for the solution,  
Kind Regards,  
Eric.

For those curious - here’s what it was about;

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.

TO which Chris replied;

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.

---

<div class="post-metadata">

**Author:** ![soderic](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/8baadc/32.png) [@soderic](https://www.ruby-forum.com/u/soderic)\
**Post date:** [January 7, 2006, 4:46am UTC](https://www.ruby-forum.com/t/to-chris-hall-re-db-modelling-the-rails-way-opinions/59955/2 "2006-01-07T04:46:25Z")

</div>

Not to get all semantic or anything, but what do you mean when you say a  
“project has many roles”?

I’ve always considered a role to be an attribute of a relationship, not  
a thing.

---

<div class="post-metadata">

**Author:** ![soderic](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/8baadc/32.png) [@soderic](https://www.ruby-forum.com/u/soderic)\
**Post date:** [January 7, 2006, 7:41am UTC](https://www.ruby-forum.com/t/to-chris-hall-re-db-modelling-the-rails-way-opinions/59955/3 "2006-01-07T07:41:22Z")

</div>

Kevin O. wrote:

> Not to get all semantic or anything, but what do you mean when you say a  
> “project has many roles”?
> 
> I’ve always considered a role to be an attribute of a relationship, not  
> a thing.

Yeah - And, your point is? It’s expressed this way for flexibility

---

<div class="post-metadata">

**Author:** ![soderic](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/8baadc/32.png) [@soderic](https://www.ruby-forum.com/u/soderic)\
**Post date:** [January 7, 2006, 11:26pm UTC](https://www.ruby-forum.com/t/to-chris-hall-re-db-modelling-the-rails-way-opinions/59955/4 "2006-01-07T23:26:03Z")

</div>

Kevin,  
I had a complex relationship to build between Projects, Roles and  
People. I wanted maximum flexibility in addressing each, any or all of  
those and I wanted to have uncluttered schema entities. So forgetting  
attributes and entities for the moment the solution proposed by Chris  
gave me that flexibility in terms of associations. Hey, I’m finding my  
way here, so I’ve put previous experience with C/S design on hold  
temporarily. Actually, I started down this path with a full blown BUD  
schema painstakingly drawn in DBDesigner - it no longer exists!  
Cheers,  
Eric.  
PS didn’t mean to bite.

---

<div class="post-metadata">

**Author:** ![soderic](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/8baadc/32.png) [@soderic](https://www.ruby-forum.com/u/soderic)\
**Post date:** [January 7, 2006, 8:20am UTC](https://www.ruby-forum.com/t/to-chris-hall-re-db-modelling-the-rails-way-opinions/59955/5 "2006-01-07T08:20:33Z")

</div>

Eric S. wrote:

> Kevin O. wrote:
> 
> > Not to get all semantic or anything, but what do you mean when you say a  
> > “project has many roles”?
> > 
> > I’ve always considered a role to be an attribute of a relationship, not  
> > a thing.
> 
> Yeah - And, your point is? It’s expressed this way for flexibility

I didn’t have a point, I had a question. I have no doubt there is  
reason for that setup, I just didn’t see the logic.