Using Joins and Subqueries - MySQL - page 498

Solution
This is a many-to-many relationship. It requires a third table for associating your two
primary tables and a three-way join to produce the correspondences between them.
Discussion
The artist and painting tables used in earlier sections have a one-to-many relation‐
ship: a given artist may have produced many paintings, but each painting was created
by only one artist. One-to-many relationships are relatively simple and the two related
tables can be joined using a column that is common to both.
A many-to-many relationship between tables is more complex. It occurs when a row in
one table may have many matches in the other, and vice versa. An example is the rela‐
tionship between movies and actors: each movie may have multiple actors, and each
actor may have appeared in multiple movies. One way to represent this relationship
uses a table structured as follows, with a row for each movie-actor combination:
mysql> SELECT * FROM movies_actors ORDER BY year, movie, actor;
+------+----------------------------+---------------+
| year | movie | actor |
+------+----------------------------+---------------+
| 1997 | The Fifth Element | Bruce Willis |
| 1997 | The Fifth Element | Gary Oldman |
| 1997 | The Fifth Element | Ian Holm |
| 1999 | The Phantom Menace | Ewan McGregor |
| 1999 | The Phantom Menace | Liam Neeson |
| 2001 | The Fellowship of the Ring | Elijah Wood |
| 2001 | The Fellowship of the Ring | Ian Holm |
| 2001 | The Fellowship of the Ring | Ian McKellen |
| 2001 | The Fellowship of the Ring | Orlando Bloom |
| 2005 | Kingdom of Heaven | Liam Neeson |
| 2005 | Kingdom of Heaven | Orlando Bloom |
| 2010 | Red | Bruce Willis |
| 2010 | Red | Helen Mirren |
| 2011 | Unknown | Diane Kruger |
| 2011 | Unknown | Liam Neeson |
+------+----------------------------+---------------+
The table captures the nature of this many-to-many relationship, but it's also in non‐
normal form because it unnecessarily stores repetitive information. For example, in‐
formation for each movie is recorded multiple times. To better represent this many-to-
many relationship, use multiple tables:
• Store each movie year and name once in a table named movies .
• Store each actor name once in a table named actors .
• Create a third table, movies_actors_link , that stores movie-actor associations and
serves as a link, or bridge, between the two primary tables. To minimize the infor‐