[Dbix-class] Three table relationships?

[James Marca]

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