You want to display a relationship between tables when rows in either table might be matched by multiple rows in the other table.
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.
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 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:
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 | 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:
List all the pairings that show the teams and who played on
them. This statement enumerates all the correspondences between
the euchre_team
and euchre_player
tables and reproduces the
information that was originally in the nonnormal euchre
table:
mysql>SELECT t.name, t.year, t.wins, t.losses, p.name, p.city
->FROM euchre_team AS t INNER JOIN euchre_link AS l
->INNER JOIN euchre_player AS p
->ON 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 | 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 | +----------+------+------+--------+----------+---------+
List the members for a particular team (the 2005 Crowns):
mysql>SELECT p.name, p.city
->FROM euchre_team AS t INNER JOIN euchre_link AS l
->INNER JOIN euchre_player AS p
->ON t.id = l.team_id AND p.id = l.player_id
->AND t.name = 'Crowns' AND t.year = 2005;
+--------+--------+ | 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 INNER JOIN euchre_link AS l
->INNER JOIN euchre_player AS p
->ON t.id = l.team_id AND p.id = l.player_id
->WHERE p.name = 'Billy';
+----------+------+------+--------+ | name | year | wins | losses | +----------+------+------+--------+ | Kings | 2005 | 10 | 2 | | Sceptres | 2006 | 2 | 10 | +----------+------+------+--------+
18.189.188.238