[Dbix-class] Adding an additional custom join parameter

[Gavin Rogers]

On Tue, Dec 10, 2013 at 11:36:38AM +0000, Andrew Beverley wrote:
> On Tue, 2013-12-10 at 19:42 +0900, Hailin Hu wrote:
> > Can I ask why you need site_task table?
> > In relationship view, it is the same as issue table.
> 
> That's a good question. The reason is that "issues" will often be
> generated that are not related to a "task", and likewise there will be
> "tasks" that are related to "sites" but that do not have any "issue" to
> link them (in which case the "site_task" table would be used).
> 
> > It is something like that you defined one many-to-many relation (task
> > <-> site) through two bridge tables (site_task and issue).
> 
> You raise a valid point though, in that this is probably a poor way to
> design such a database. Maybe I should only have one "site_task" table
> to link the "sites" and "tasks" tables, and then have appropriate fields
> within that single table to signify whether the entry is linking the 2
> tables for reason of an "issue" or a "task".
> 
> http://files.andybev.com/schema.png

I can't see anything different in this png. But I get what you're talking
about... I have a very basic example of this. In my many_to_many table I have
boolean values for PRIMARY_GROUP and AFFILIATION_GROUP.
https://github.com/cessna-cowboy/UserDatabase/blob/master/password_propagation_schema.pdf

It works ok. When I update_or_create that table, I just have to ensure that
the data structure has one or both of the fields set (not a problem in perl).
In fact I should probable add that to my tests. Feel free to check out my
GroupMembership.pm to see how it was set up. For the record, mst correctly
advised against this. In my situation the better design would be to have 2
more fields in User, which are related directly to a Group (while still having
the vanilla many_to_many in place). The only problem with that is you have to
ensure that anyone using your application remembers to write code that
creates/updates both the many_to_many relationship AND the extra direct
relationships.

In the end I was told (in meatspace) to write it the way the PDF shows it. It
doesn't work too badly... just think of using the linking table to colour a
relationship "red" or "issue" or "task". Or in my case, "primary" or
"affiliation". This might not be sound DB theory but it works fine with DBIC.

Gavin


> 
> Thanks,
> 
> Andy
> 
> 
> 
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk