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 [email protected] 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.