Rails - Jan 2006 - Re: DB Modelling the Rails way - solution by Chris Hall

Hi Chris,
Yesterday you kindly submitted an answer to my question on modelling 
many to many relationships. I''ve briefly tried the proposed solution
but
so far with no luck. Before I get too involved - I''d like to know if 
mapping three way :join_tables is "legal" in a Rails sense. I only ask
since you put (untested) in your mail and I can''t find any docs on it. 
BTW, forgive me if this is a duplicate post - I had some mailing 
problems yesterday.
Kind Regards,
Eric.

Chris Hall wrote:
> 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 Smith"
> 
> # all projects assocated with "John Smith"
> person.projects
> 
> # all roles assocated with "John Smith"
> 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 
> <http://manager.id>)
> project.roles.push_with_attributes(manager, :person_id => john.id 
> <http://john.id>)
> john.projects.push_with_attributes(project, :role_id => manager.id 
> <http://manager.id>)
> john.roles.push_with_attributes(manager, :project_id => project.id 
> <http://project.id>)
> role.projects.push_with_attributes(project, :person_id => john.id 
> <http://john.id>)
> role.people.push_with_attributes(john, :project_id => project.id 
> <http://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.
>