# DB Modelling the Rails way - Opinions?

**URL:** <https://www.ruby-forum.com/t/db-modelling-the-rails-way-opinions/55706>\
**Category:** Rails\
**Created:** [January 2, 2006, 2:43am UTC](https://www.ruby-forum.com/t/db-modelling-the-rails-way-opinions/55706 "2006-01-02T02:43:04Z")\
**Posts on this page:** 6\
**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 2, 2006, 2:43am UTC](https://www.ruby-forum.com/t/db-modelling-the-rails-way-opinions/55706/1 "2006-01-02T02:43:04Z")

</div>

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.

---

<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 3, 2006, 12:40pm UTC](https://www.ruby-forum.com/t/db-modelling-the-rails-way-opinions/55706/2 "2006-01-03T12:40:46Z")

</div>

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:

> A person may perform multiple roles,  
> An Organisation Belongs\_to a Stakeholder  
> A Stakeholder has\_many Roles in a Project.  
> Eric.
> 
> * * *
> 
> Rails mailing list  
> removed\_email\_address@domain.invalid  
> [http://lists.rubyonrails.org/mailman/listinfo/rails](http://lists.rubyonrails.org/mailman/listinfo/rails)

–  
“Who cares if it doesn’t do anything? It was made with our new  
Triple-Iso-Bifurcated-Krypton-Gate-MOS process …”

My $Grtz =~ Gerard;  
~  
:wq!

---

<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 3, 2006, 2:40pm UTC](https://www.ruby-forum.com/t/db-modelling-the-rails-way-opinions/55706/3 "2006-01-03T14:40:11Z")

</div>

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 4, 2006, 12:33am UTC](https://www.ruby-forum.com/t/db-modelling-the-rails-way-opinions/55706/4 "2006-01-04T00:33:00Z")

</div>

Eric / Chris,

@Chris: Cudo’s to you Chris!

@Eric: Having gone through Chris’ email. I must say now it doesn’t just  
look  
scary on paper … 🙂

Being a Rails fan for a couple of weeks I’m not fully up to speed on the  
proper, and more important failsafe, OO programming. Nevertheless … I  
did  
another post on a model I’m chewing on, on which I would like some  
reponse. I  
think, IMHO, I’ve got the model covered for now (never now what’s around  
the  
corner). I’m just somewhat insecure on programming around it. But I  
guess  
there’s just one lesson … Practice!

Should you or Chris have some comments, they would be appreciated.

Regards,

Gerard.

On Tuesday 03 January 2006 22:49, Eric S. tried to type something  
like:

> Thanks Again,
> 
> > person\_id  
> > “people\_projects\_roles” has\_and\_belongs\_to\_many :roles, :join\_table =\>
> > 
> > project.people.push\_with\_attributes(john, :role\_id =\> manager.id  
> > [http://project.id](http://project.id))  
> > many organisations (stakeholders)? if so, then i would setup another
> > 
> > ```
> > (started working for another company) that the data is stil
> > as well on the relationships you want to maintain.
> > 
> > > Hi,
> > > A project has many roles,
> > > So one way I have
> > > Projects HABTM People,
> > > People
> > > _______________________________________________
> > 
> > ```
> 
> Rails mailing list  
> removed\_email\_address@domain.invalid  
> [http://lists.rubyonrails.org/mailman/listinfo/rails](http://lists.rubyonrails.org/mailman/listinfo/rails)

–  
“Who cares if it doesn’t do anything? It was made with our new  
Triple-Iso-Bifurcated-Krypton-Gate-MOS process …”

My $Grtz =~ Gerard;  
~  
:wq!

---

<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 3, 2006, 10:53pm UTC](https://www.ruby-forum.com/t/db-modelling-the-rails-way-opinions/55706/5 "2006-01-03T22:53:48Z")

</div>

Hi Chris,  
Thanks for your input. You’ve hit the nail squarely on the head (ouch).  
The relationship between Projects, Roles and People is to be unlimited  
for exactly the reasons you have pointed out - brilliantly I might add.  
As to Organisations - the relationship there is simply to define a  
broader role - Client, Contractor, etc. that People act\_for. In practice  
this level of flexibility might be overkill - but I want it there - just  
in case :~) As I said in response to Gerard(to whom I replied before  
reading your post) - on paper this looks kinda scary

Thanks Again,  
Eric.

---

<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 19, 2006, 12:32pm UTC](https://www.ruby-forum.com/t/db-modelling-the-rails-way-opinions/55706/6 "2006-01-19T12:32:50Z")

</div>

Hi,

If using proposed solution by Chris, have can you paginate for example  
person.projects with a condition like roles = ‘editors’?

Rebus