There seems to be two interpretation of the word 'reducible' - the one that I was thinking of was about reducing in the DBIx::Class layer - what Marca proposed is changing the database schema. I still maintain that it is not reducible on the DBIx::Class layer - but I accept Marca's argument that in most cases this three table schema is not the most clean database schema design.
Anyway the definition by Aristotle - that the relationships are should be what you can express in the SQL seems reasonable. This closes the first part of my question - if we really should treat many-to-many differently from the other relationships. The second part was if we need then another ResultSet method that would list the many-to-many relationships (perhaps together with other bridge relationships). -- Zbyszek On 2/5/07, James Marca <[EMAIL PROTECTED]> wrote:
At approximately Fri, Feb 02, 2007 at 12:20:49PM +0000, Zbigniew Lukasiak wrote: > On 1/31/07, Matt S Trout <[EMAIL PROTECTED]> wrote: ... > > So here is an example for the general case, perhaps you could call it ... > > select * from a, b, c where > a.key1 = b.key2 and b.key1 = c.key2 and c.key1 = a.key2 > > With composed (key1, key2) primary keys for a, b and c tables. > > This join is a three table relationship (just like the many to many > case) - and I think it is not reducible to a superposition of two > table relstionships (differently from the many to many case). Sorry to pop in the middle here, but my understanding is that the above relationship *is* reducible. The issues are covered in better detail in the O'Reilly book, Java Database Best Practices, page 44 to 46. I wrote your relationships down, and they seem to translate to: A= ActorRole B= ActorFilm C= FilmRole so your condition: > a.key1 = b.key2 and b.key1 = c.key2 and c.key1 = a.key2 is ActorRole ActorFilm FilmRole actorId (a.k1) filmId (b.k1) roleId (c.k1) roleId (a.k2) actorId (b.k2) filmId (c.k2) This is used by the book as an example of a database that isn't in fifth normal form. You can enter data that make sense in the db but that produce nonsensical results (actors in roles in the wrong film). In essense, you never guarantee that person A was in film B playing Role C, all at the same time. Instead, the table structure just hopes that something else prevents generic roles such as "the President" or "reporter" from being assigned the same roleId. Otherwise, you may get a case where some actor plays a reporter in one film, the president in another, and if both those roles are in both films, you'll get the actor playing the wrong role in the wrong film. For example (the book's example seems to be in error, by the way): actor filmId role True or False? 1- J.Doe 101 1001- the President true 1- J.Doe 102 1002- a reporter true 3- S.Queue 101 1002- a reporter true 4- A.Smithee 102 1001- the President true -------------------------------------------------- 1- J.Doe 101 1002- a reporter false 1- J.Doe 102 1001- the President false The solution is to create a trinary join table: Appearance actorId filmId roleId And once again, the many to many relationship is intermediated by a single (but this time trinary) table. James _______________________________________________ List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class Wiki: http://dbix-class.shadowcatsystems.co.uk/ IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/ Searchable Archive: http://www.mail-archive.com/[email protected]/
-- Zbigniew Lukasiak http://brudnopis.blogspot.com/ _______________________________________________ List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class Wiki: http://dbix-class.shadowcatsystems.co.uk/ IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/ Searchable Archive: http://www.mail-archive.com/[email protected]/
