Recipe 12.5. Enumerating a Many-to-Many Relationship
ProblemYou want to display a relationship between tables when rows in either table might be matched by multiple rows in the other table. SolutionThis 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. DiscussionThe 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 joined with a key that is common to both tables. Even simpler is the one-to-one relationship, which often is used to perform 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 one-to-one relationship 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, exploit 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 INNER JOIN states ON 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 row 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 how different players might remember the results of each tournament, you record the pairings and outcomes in a database. One way to store the results is 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 | 2005 | 10 | 2 | Ben | Cork | | Kings | 2005 | 10 | 2 | Billy | York | | Crowns | 2005 | 7 | 5 | Melvin | Dublin | | Crowns | 2005 | 7 | 5 | Tony | Derry | | Stars | 2005 | 4 | 8 | Franklin | Bath | | Stars | 2005 | 4 | 8 | Wallace | Cardiff | | Sceptres | 2005 | 3 | 9 | Maurice | Leeds | | Sceptres | 2005 | 3 | 9 | Nigel | London | | Crowns | 2006 | 9 | 3 | Ben | Cork | | Crowns | 2006 | 9 | 3 | Tony | Derry | | Kings | 2006 | 8 | 4 | Franklin | Bath | | Kings | 2006 | 8 | 4 | Nigel | London | | Stars | 2006 | 5 | 7 | Maurice | Leeds | | Stars | 2006 | 5 | 7 | Melvin | Dublin | | Sceptres | 2006 | 2 | 10 | Billy | York | | Sceptres | 2006 | 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 nonnormal 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 to use multiple tables:
The resulting team and player tables look like this: mysql> SELECT * FROM euchre_team; +----+----------+------+------+--------+ | id | name | year | wins | losses | +----+----------+------+------+--------+ | 1 | Kings | 2005 | 10 | 2 | | 2 | Crowns | 2005 | 7 | 5 | | 3 | Stars | 2005 | 4 | 8 | | 4 | Sceptres | 2005 | 3 | 9 | | 5 | Kings | 2006 | 8 | 4 | | 6 | Crowns | 2006 | 9 | 3 | | 7 | Stars | 2006 | 5 | 7 | | 8 | Sceptres | 2006 | 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:
|
