MySQL Cookbook

12.11 Enumerating a Many-to-Many Relationship

12.11.1 Problem

You want to display a relationship between tables when records in either table may be matched by multiple records in the other table.

12.11.2 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 list the correspondences between them.

12.11.3 Discussion

The artist and painting tables used in earlier sections are related in a one-to-many relationship: 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 tables in the relationship can be related by means of a key that is common to both tables.

Even simpler is the one-to-one relationship, which often is used for performing lookups that map one set of values to another. For example, the states table contains name and abbrev columns that list full state names and their corresponding abbreviations:

mysql> SELECT name, abbrev FROM states;
+----------------+--------+
| name           | abbrev |
+----------------+--------+
| Alabama        | AL     |
| Alaska         | AK     |
| Arizona        | AZ     |
| Arkansas       | AR     |
...

This is a one-to-one relationship. It can be used to map state name abbreviations in the painting table, which contains a state column indicating the state in which each painting was purchased. With no mapping, painting entries can be displayed like this:

mysql> SELECT title, state FROM painting ORDER BY state;
+-------------------+-------+
| title             | state |
+-------------------+-------+
| The Rocks         | IA    |
| The Last Supper   | IN    |
| Starry Night      | KY    |
| The Potato Eaters | KY    |
| The Mona Lisa     | MI    |
| Les Deux Soeurs   | NE    |
+-------------------+-------+

If you want to see the full state names rather than abbreviations, it's possible to use the one-to-one relationship that exists between the two that is enumerated in the states table. Join that table to the painting table as follows, using the abbreviation values that are common to the two tables:

mysql> SELECT painting.title, states.name AS state
    -> FROM painting, states
    -> WHERE painting.state = states.abbrev
    -> ORDER BY state;
+-------------------+----------+
| title             | state    |
+-------------------+----------+
| The Last Supper   | Indiana  |
| The Rocks         | Iowa     |
| Starry Night      | Kentucky |
| The Potato Eaters | Kentucky |
| The Mona Lisa     | Michigan |
| Les Deux Soeurs   | Nebraska |
+-------------------+----------+

A more complex relationship between tables is the many-to-many relationship, which occurs when a record in one table may have many matches in the other, and vice versa. To illustrate such a relationship, this is the point at which database books typically devolve into the "parts and suppliers" problem. (A given part may be available through several suppliers; how can you produce a list showing which parts are available from which suppliers?) However, having seen that example far too many times, I prefer to use a different illustration. So, even though conceptually it's really the same idea, let's use the following scenario: You and a bunch of your friends are avid enthusiasts of euchre, a four-handed card game played with two teams of partners. Each year, you all get together, pair off, and run a friendly tournament. Naturally, to avoid controversy about the results of each tournament, you record the pairings and outcomes in a database. One way to store the results would be with a table that is set up as follows, where for each tournament year, you record the team names, win-loss records, players, and player cities of residence:

mysql> SELECT * FROM euchre ORDER BY year, wins DESC, player;
+----------+------+------+--------+----------+-------------+
| team     | year | wins | losses | player   | player_city |
+----------+------+------+--------+----------+-------------+
| Kings    | 2001 |   10 |      2 | Ben      | Cork        |
| Kings    | 2001 |   10 |      2 | Billy    | York        |
| Crowns   | 2001 |    7 |      5 | Melvin   | Dublin      |
| Crowns   | 2001 |    7 |      5 | Tony     | Derry       |
| Stars    | 2001 |    4 |      8 | Franklin | Bath        |
| Stars    | 2001 |    4 |      8 | Wallace  | Cardiff     |
| Sceptres | 2001 |    3 |      9 | Maurice  | Leeds       |
| Sceptres | 2001 |    3 |      9 | Nigel    | London      |
| Crowns   | 2002 |    9 |      3 | Ben      | Cork        |
| Crowns   | 2002 |    9 |      3 | Tony     | Derry       |
| Kings    | 2002 |    8 |      4 | Franklin | Bath        |
| Kings    | 2002 |    8 |      4 | Nigel    | London      |
| Stars    | 2002 |    5 |      7 | Maurice  | Leeds       |
| Stars    | 2002 |    5 |      7 | Melvin   | Dublin      |
| Sceptres | 2002 |    2 |     10 | Billy    | York        |
| Sceptres | 2002 |    2 |     10 | Wallace  | Cardiff     |
+----------+------+------+--------+----------+-------------+

As shown by the table, each team has multiple players, and each player has participated in multiple teams. The table captures the nature of this many-to-many relationship, but it's also in non-normal form, because each row unnecessarily stores quite a bit of repetitive information. (Information for each team is recorded multiple times, as is information about each player.) A better way to represent this many-to-many relationship is as follows:

• Store each team name, year, and record once, in a table named euchre_team.

• Store each player name and city of residence once, in a table named euchre_player.

• Create a third table, euchre_link, that stores team-player associations and serves as a link, or bridge, between the two primary tables. To minimize the information stored in this table, assign unique IDs to each team and player within their respective tables, and store only those IDs in the euchre_link table.

The resulting team and player tables look like this:

mysql> SELECT * FROM euchre_team;
+----+----------+------+------+--------+
| id | name     | year | wins | losses |
+----+----------+------+------+--------+
|  1 | Kings    | 2001 |   10 |      2 |
|  2 | Crowns   | 2001 |    7 |      5 |
|  3 | Stars    | 2001 |    4 |      8 |
|  4 | Sceptres | 2001 |    3 |      9 |
|  5 | Kings    | 2002 |    8 |      4 |
|  6 | Crowns   | 2002 |    9 |      3 |
|  7 | Stars    | 2002 |    5 |      7 |
|  8 | Sceptres | 2002 |    2 |     10 |
+----+----------+------+------+--------+
mysql> SELECT * FROM euchre_player;
+----+----------+---------+
| id | name     | city    |
+----+----------+---------+
|  1 | Ben      | Cork    |
|  2 | Billy    | York    |
|  3 | Tony     | Derry   |
|  4 | Melvin   | Dublin  |
|  5 | Franklin | Bath    |
|  6 | Wallace  | Cardiff |
|  7 | Nigel    | London  |
|  8 | Maurice  | Leeds   |
+----+----------+---------+

The euchre_link table associates teams and players as follows:

mysql> SELECT * FROM euchre_link;
+---------+-----------+
| team_id | player_id |
+---------+-----------+
|       1 |         1 |
|       1 |         2 |
|       2 |         3 |
|       2 |         4 |
|       3 |         5 |
|       3 |         6 |
|       4 |         7 |
|       4 |         8 |
|       5 |         5 |
|       5 |         7 |
|       6 |         1 |
|       6 |         3 |
|       7 |         4 |
|       7 |         8 |
|       8 |         2 |
|       8 |         6 |
+---------+-----------+

To answer questions about the teams or players using these tables, you need to perform a three-way join, using the link table to relate the two primary tables to each other. Here are some examples:

• List all the pairings that show the teams and who played on them. This query enumerates all the correspondences between the euchre_team and euchre_player tables and reproduces the information that was originally in the non-normal euchre table:

  mysql> SELECT t.name, t.year, t.wins, t.losses, p.name, p.city
      -> FROM euchre_team AS t, euchre_link AS l, euchre_player AS p
      -> WHERE t.id = l.team_id AND p.id = l.player_id
      -> ORDER BY t.year, t.wins DESC, p.name;
  +----------+------+------+--------+----------+---------+
  | name     | year | wins | losses | name     | city    |
  +----------+------+------+--------+----------+---------+
  | Kings    | 2001 |   10 |      2 | Ben      | Cork    |
  | Kings    | 2001 |   10 |      2 | Billy    | York    |
  | Crowns   | 2001 |    7 |      5 | Melvin   | Dublin  |
  | Crowns   | 2001 |    7 |      5 | Tony     | Derry   |
  | Stars    | 2001 |    4 |      8 | Franklin | Bath    |
  | Stars    | 2001 |    4 |      8 | Wallace  | Cardiff |
  | Sceptres | 2001 |    3 |      9 | Maurice  | Leeds   |
  | Sceptres | 2001 |    3 |      9 | Nigel    | London  |
  | Crowns   | 2002 |    9 |      3 | Ben      | Cork    |
  | Crowns   | 2002 |    9 |      3 | Tony     | Derry   |
  | Kings    | 2002 |    8 |      4 | Franklin | Bath    |
  | Kings    | 2002 |    8 |      4 | Nigel    | London  |
  | Stars    | 2002 |    5 |      7 | Maurice  | Leeds   |
  | Stars    | 2002 |    5 |      7 | Melvin   | Dublin  |
  | Sceptres | 2002 |    2 |     10 | Billy    | York    |
  | Sceptres | 2002 |    2 |     10 | Wallace  | Cardiff |
  +----------+------+------+--------+----------+---------+

• List the members for a particular team (the 2001 Crowns):

  mysql> SELECT p.name, p.city
      -> FROM euchre_team AS t, euchre_link AS l, euchre_player AS p
      -> WHERE t.id = l.team_id AND p.id = l.player_id
      -> AND t.name = 'Crowns' AND t.year = 2001;
  +--------+--------+
  | name   | city   |
  +--------+--------+
  | Tony   | Derry  |
  | Melvin | Dublin |
  +--------+--------+

• List the teams that a given player (Billy) has been a member of:

  mysql> SELECT t.name, t.year, t.wins, t.losses
      -> FROM euchre_team AS t, euchre_link AS l, euchre_player AS p
      -> WHERE t.id = l.team_id AND p.id = l.player_id
      -> AND p.name = 'Billy';
  +----------+------+------+--------+
  | name     | year | wins | losses |
  +----------+------+------+--------+
  | Kings    | 2001 |   10 |      2 |
  | Sceptres | 2002 |    2 |     10 |
  +----------+------+------+--------+

Note that although questions about many-to-many relationships involve a three-way join, a three-way join in itself does not necessarily imply a many-to-many relationship. Earlier in this section, we joined the states table to the painting table to map state abbreviations to full names:

mysql> SELECT painting.title, states.name AS state
    -> FROM painting, states
    -> WHERE painting.state = states.abbrev
    -> ORDER BY state;
+-------------------+----------+
| title             | state    |
+-------------------+----------+
| The Last Supper   | Indiana  |
| The Rocks         | Iowa     |
| Starry Night      | Kentucky |
| The Potato Eaters | Kentucky |
| The Mona Lisa     | Michigan |
| Les Deux Soeurs   | Nebraska |
+-------------------+----------+

To display the artist who painted each painting, modify the query slightly by joining the results with the artist table:

mysql> SELECT artist.name, painting.title, states.name AS state
    -> FROM artist, painting, states
    -> WHERE artist.a_id = painting.a_id AND painting.state = states.abbrev;
+----------+-------------------+----------+
| name     | title             | state    |
+----------+-------------------+----------+
| Da Vinci | The Last Supper   | Indiana  |
| Da Vinci | The Mona Lisa     | Michigan |
| Van Gogh | Starry Night      | Kentucky |
| Van Gogh | The Potato Eaters | Kentucky |
| Van Gogh | The Rocks         | Iowa     |
| Renoir   | Les Deux Soeurs   | Nebraska |
+----------+-------------------+----------+

The query now involves a three-way join, but the nature of the relationship between artists and paintings remains the same. It's still one-to-many, not many-to-many.