The official Rails Special Ops Task Force is in a dilly of a pickle.
Varying "Agents" are assigned to varying "Projects." Each
"Project" has,
for each "Agent," a varying number of "Duties."
Naturally, the RSOTF would like to make a Rails application to properly
manage these complex and very secret projects, their agents, and the
duties required.
The shell of a PostgreSQL schema is provided << END_SCHEMA
CREATE TABLE agents (
id SERIAL PRIMARY KEY,
name VARCHAR(40) NOT NULL
);
CREATE INDEX agent_name_idx ON agent (name);
CREATE TABLE projects (
id SERIAL PRIMARY KEY,
name VARCHAR(20) NOT NULL
);
CREATE INDEX project_name_idx ON projects (name);
CREATE TABLE duties (
id SERIAL PRIMARY KEY,
name VARCHAR(20) NOT NULL
);
CREATE INDEX duty_name_idx ON duties (name);
CREATE TABLE agents_projects (
agent_id INTEGER NOT NULL REFERENCES agents ON UPDATE CASCADE
ON DELETE CASCADE,
project_id INTEGER NOT NULL REFERENCES projects ON UPDATE CASCADE
ON DELETE CASCADE
);
END_SCHEMA
Now the RSOTF is confused. What is the best way to make a relation
between the agents_projects join table and duties?
The RSOTF feels, as this pattern could be applied to a complicated ACL
setup, that some documented consensus would be beneficial to the Rails
community at large.
On 08/04/2005, at 8:48 PM, Sam Goldman wrote:> The official Rails Special Ops Task Force is in a dilly of a pickle.Sounds like somebody is posting to mailing lists after a big night out on the town ;) - tim
To be honest, I thought the same =DDD On Apr 8, 2005 8:59 AM, Tim Lucas <t.lucas-l/qNJNvq70OzaBltdDZI6w@public.gmane.org> wrote:> > On 08/04/2005, at 8:48 PM, Sam Goldman wrote: > > The official Rails Special Ops Task Force is in a dilly of a pickle. > > Sounds like somebody is posting to mailing lists after a big night out > on the town ;) > > - tim > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- juraci krohling costa http://jkcosta.info _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Sam, Is there a situation when an agent should be connected to a project without any duty whatsoever? If not, you could ditch the whole agents_projects table and put agent_id and project_id in the duties table. Actually, I think you need those foreign keys in the duties table anyway. So whether you need the agents_projects table for some reason is irrelevant to this question, because duties will be connected to both agents and projects directly. In every case, you shouldn''t be relating duties with that join table, that makes things way too complicated both in relational sense and AR-wise. //jarkko On 8.4.2005, at 13:48, Sam Goldman wrote:> The official Rails Special Ops Task Force is in a dilly of a pickle. > Varying "Agents" are assigned to varying "Projects." Each "Project" > has, for each "Agent," a varying number of "Duties." > > Naturally, the RSOTF would like to make a Rails application to > properly manage these complex and very secret projects, their agents, > and the duties required. > > The shell of a PostgreSQL schema is provided << END_SCHEMA > > CREATE TABLE agents ( > id SERIAL PRIMARY KEY, > name VARCHAR(40) NOT NULL > ); > CREATE INDEX agent_name_idx ON agent (name); > > CREATE TABLE projects ( > id SERIAL PRIMARY KEY, > name VARCHAR(20) NOT NULL > ); > CREATE INDEX project_name_idx ON projects (name); > > CREATE TABLE duties ( > id SERIAL PRIMARY KEY, > name VARCHAR(20) NOT NULL > ); > CREATE INDEX duty_name_idx ON duties (name); > > CREATE TABLE agents_projects ( > agent_id INTEGER NOT NULL REFERENCES agents ON UPDATE CASCADE > ON DELETE CASCADE, > project_id INTEGER NOT NULL REFERENCES projects ON UPDATE CASCADE > ON DELETE CASCADE > ); > > > END_SCHEMA > > Now the RSOTF is confused. What is the best way to make a relation > between the agents_projects join table and duties? > > The RSOTF feels, as this pattern could be applied to a complicated ACL > setup, that some documented consensus would be beneficial to the Rails > community at large. > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Jarkko Laine http://jlaine.net http://odesign.fi _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
It''s weird how people think differently. Relating duties to the join table makes perfect sense to me. I can map it out and understand it in my head, whereas basing the relation from duties is throwing me mental curve balls. - Sam Jarkko Laine wrote:> Sam, > > Is there a situation when an agent should be connected to a project > without any duty whatsoever? If not, you could ditch the whole > agents_projects table and put agent_id and project_id in the duties table. > > Actually, I think you need those foreign keys in the duties table > anyway. So whether you need the agents_projects table for some reason is > irrelevant to this question, because duties will be connected to both > agents and projects directly. In every case, you shouldn''t be relating > duties with that join table, that makes things way too complicated both > in relational sense and AR-wise. > > //jarkko > > On 8.4.2005, at 13:48, Sam Goldman wrote: > >> The official Rails Special Ops Task Force is in a dilly of a pickle. >> Varying "Agents" are assigned to varying "Projects." Each "Project" >> has, for each "Agent," a varying number of "Duties." >> >> Naturally, the RSOTF would like to make a Rails application to >> properly manage these complex and very secret projects, their agents, >> and the duties required. >> >> The shell of a PostgreSQL schema is provided << END_SCHEMA >> >> CREATE TABLE agents ( >> id SERIAL PRIMARY KEY, >> name VARCHAR(40) NOT NULL >> ); >> CREATE INDEX agent_name_idx ON agent (name); >> >> CREATE TABLE projects ( >> id SERIAL PRIMARY KEY, >> name VARCHAR(20) NOT NULL >> ); >> CREATE INDEX project_name_idx ON projects (name); >> >> CREATE TABLE duties ( >> id SERIAL PRIMARY KEY, >> name VARCHAR(20) NOT NULL >> ); >> CREATE INDEX duty_name_idx ON duties (name); >> >> CREATE TABLE agents_projects ( >> agent_id INTEGER NOT NULL REFERENCES agents ON UPDATE CASCADE >> ON DELETE CASCADE, >> project_id INTEGER NOT NULL REFERENCES projects ON UPDATE CASCADE >> ON DELETE CASCADE >> ); >> >> >> END_SCHEMA >> >> Now the RSOTF is confused. What is the best way to make a relation >> between the agents_projects join table and duties? >> >> The RSOTF feels, as this pattern could be applied to a complicated ACL >> setup, that some documented consensus would be beneficial to the Rails >> community at large. >> _______________________________________________ >> Rails mailing list >> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >> http://lists.rubyonrails.org/mailman/listinfo/rails >> > -- > Jarkko Laine > http://jlaine.net > http://odesign.fi > > > ------------------------------------------------------------------------ > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails
Sam, It might feel like that first. To be honest, I didn''t think about the situation in very high level. I just proposed what would make the associations simplest in RDBMS level, and especially for use with ActiveRecord. If you use method I proposed, your Ar model will look something like this: class Agent < AR::Base has_many :duties end class Project < AR::Base has_many :duties end class Duty < AR::Base belongs_to :agent belongs_to :project end If, however, you''d like to connect duties to the join table, well, you can''t actually model that in active record really well. But that doesn''t mean there''s something wrong with AR. It''s IMHO a bit backwards to use a separate join table if duties can play that same role. BUT, if agent might be connected to a project without any duties, you should probably promote the agents_projects to a model of itself. Then you can give it an id field of itself (you can''t really do that with a pure join table) and relate duties table with that field. In that case the model would look like: class Agent < AR::Base has_many :projectconnections end class Project < AR::Base has_many :projectconnections end class Projectconnection < AR::Base belongs_to :agent belongs_to :project has_many :duties end class Duty < AR::Base belongs_to :projectconnection end You can probably figure out how to change the rdbms schema accordingly. Oh, and here''s a cool cheat sheet by Amy Hoy if AR associations are causing your head to ache: http://www.slash7.com/cheats/activerecord_cheatsheet.pdf //jarkko On 8.4.2005, at 15:21, Sam Goldman wrote:> It''s weird how people think differently. Relating duties to the join > table makes perfect sense to me. I can map it out and understand it in > my head, whereas basing the relation from duties is throwing me mental > curve balls. > > - Sam > > Jarkko Laine wrote: >> Sam, >> Is there a situation when an agent should be connected to a project >> without any duty whatsoever? If not, you could ditch the whole >> agents_projects table and put agent_id and project_id in the duties >> table. >> Actually, I think you need those foreign keys in the duties table >> anyway. So whether you need the agents_projects table for some reason >> is irrelevant to this question, because duties will be connected to >> both agents and projects directly. In every case, you shouldn''t be >> relating duties with that join table, that makes things way too >> complicated both in relational sense and AR-wise. >> //jarkko >> On 8.4.2005, at 13:48, Sam Goldman wrote: >>> The official Rails Special Ops Task Force is in a dilly of a pickle. >>> Varying "Agents" are assigned to varying "Projects." Each "Project" >>> has, for each "Agent," a varying number of "Duties." >>> >>> Naturally, the RSOTF would like to make a Rails application to >>> properly manage these complex and very secret projects, their >>> agents, and the duties required. >>> >>> The shell of a PostgreSQL schema is provided << END_SCHEMA >>> >>> CREATE TABLE agents ( >>> id SERIAL PRIMARY KEY, >>> name VARCHAR(40) NOT NULL >>> ); >>> CREATE INDEX agent_name_idx ON agent (name); >>> >>> CREATE TABLE projects ( >>> id SERIAL PRIMARY KEY, >>> name VARCHAR(20) NOT NULL >>> ); >>> CREATE INDEX project_name_idx ON projects (name); >>> >>> CREATE TABLE duties ( >>> id SERIAL PRIMARY KEY, >>> name VARCHAR(20) NOT NULL >>> ); >>> CREATE INDEX duty_name_idx ON duties (name); >>> >>> CREATE TABLE agents_projects ( >>> agent_id INTEGER NOT NULL REFERENCES agents ON UPDATE CASCADE >>> ON DELETE CASCADE, >>> project_id INTEGER NOT NULL REFERENCES projects ON UPDATE CASCADE >>> ON DELETE CASCADE >>> ); >>> >>> >>> END_SCHEMA >>> >>> Now the RSOTF is confused. What is the best way to make a relation >>> between the agents_projects join table and duties? >>> >>> The RSOTF feels, as this pattern could be applied to a complicated >>> ACL setup, that some documented consensus would be beneficial to the >>> Rails community at large. >>> _______________________________________________ >>> Rails mailing list >>> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >>> http://lists.rubyonrails.org/mailman/listinfo/rails >>> >> -- >> Jarkko Laine >> http://jlaine.net >> http://odesign.fi >> ---------------------------------------------------------------------- >> -- >> _______________________________________________ >> Rails mailing list >> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >> http://lists.rubyonrails.org/mailman/listinfo/rails > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Jarkko Laine http://jlaine.net http://odesign.fi _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Peter-Frank Spierenburg
2005-Apr-08 13:29 UTC
Re: Rails Special Ops Task Force in trouble!
Jarkko Laine wrote:> Sam, > > Is there a situation when an agent should be connected to a project > without any duty whatsoever? If not, you could ditch the whole > agents_projects table and put agent_id and project_id in the duties table.But doesn''t this violate some database design constraints? My understanding is that duties are an entity to themselves, and that a duty may exist for which many agents and projects are associated. Like a "manager" duty. Each project has a manager agent, so there are now many records with duties.name = ''manager''. Bad database juju. N''est pas? Peter.
Peter, On 8.4.2005, at 16:29, Peter-Frank Spierenburg wrote:> Jarkko Laine wrote: > >> Sam, >> >> Is there a situation when an agent should be connected to a project >> without any duty whatsoever? If not, you could ditch the whole >> agents_projects table and put agent_id and project_id in the duties >> table. > > But doesn''t this violate some database design constraints? My > understanding is that duties are an entity to themselves, and that a > duty may exist for which many agents and projects are associated. Like > a "manager" duty. Each project has a manager agent, so there are now > many records with duties.name = ''manager''. Bad database juju. N''est > pas?You''re right, if the duties are general enough to justify an own table of themselves. However, it only changes the schema so that there''s one more model (e.g. Dutytitle) that has_many duties. Actually, the second variation I proposed should take that into account, too, if I remember correct. //jarkko> > Peter.-- Jarkko Laine http://jlaine.net http://odesign.fi _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Peter, On 8.4.2005, at 16:29, Peter-Frank Spierenburg wrote:> Jarkko Laine wrote: > >> Sam, >> >> Is there a situation when an agent should be connected to a project >> without any duty whatsoever? If not, you could ditch the whole >> agents_projects table and put agent_id and project_id in the duties >> table. > > But doesn''t this violate some database design constraints? My > understanding is that duties are an entity to themselves, and that a > duty may exist for which many agents and projects are associated. Like > a "manager" duty. Each project has a manager agent, so there are now > many records with duties.name = ''manager''. Bad database juju. N''est > pas?You''re right, if the duties are general enough to justify an own table of themselves. However, it only changes the schema so that there''s one more model (e.g. Dutytitle) that has_many duties. Actually, the second variation I proposed should take that into account, too, if I remember correct. //jarkko> > Peter.-- Jarkko Laine http://jlaine.net http://odesign.fi _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails