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.
|
No comments:
Post a Comment