[Dbix-class] Three table relationships?

[Zbigniew Lukasiak]

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 <jmarca at translab.its.uci.edu> wrote:
> At approximately Fri, Feb 02, 2007 at 12:20:49PM +0000, Zbigniew Lukasiak wrote:
> > On 1/31/07, Matt S Trout <dbix-class at trout.me.uk> 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/dbix-class@lists.rawmode.org/
>


-- 
Zbigniew Lukasiak
http://brudnopis.blogspot.com/