Отношение «многие-ко-многим»

Задача
Вы хотите вывести данные из таблиц, при этом записи любой таблицы могут соответствовать нескольким записям другой таблицы.

Решение
Это связь «многие-ко-многим». Необходимо использовать третью таблицу для сопоставления двух исходных, и трехстороннее соединение для вывода соотношений между ними.

Обсуждение
Использованные в предыдущих разделах таблицы artist и painting имеют связь «один-ко-многим»: определенный художник мог написать много картин, но каждая картина создана ровно одним автором. Связь «один-ко-многим» достаточно проста, и две таблицы могут быть связаны при помощи общего ключа (common key).

Еще проще связь «один-к-одному», часто применяемая для выполнения подстановок, отображающих один набор значений на другой. Например, таблица states содержит столбцы name и abbrev, в которых приводятся полные названия штатов и их аббревиатуры:

mysql> SELECT name, abbrev FROM states;

+----------+---------+
| name | abbrev |
+----------+---------+
| Alabama | AL |
| Alaska | AK |
| Arizona | AZ |
| Arkansas | AR |
...

Это связь «один-к-одному».


Ее можно использовать для сопоставления аббревиатурам названий штатов из таблицы painting, которая содержит столбец state, указывающий штат приобретения картины. Без сопоставления записи painting можно вывести так:

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 |
+----------------------+-------+

Если вы хотите видеть полные названия штатов, а не их аббревиатуры, то можете использовать связь «один-к-одному», существующую между столбцами таблицы states. Объедините эту таблицу с painting, используя общие для двух таблиц значения аббревиатур:

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 |
+---------------------+------------+

Более сложной связью между таблицами является связь «многие-ко-многим», которая имеет место, если записи одной таблицы может соответствовать несколько записей второй, и наоборот.


Обычно в книгах по базам данных такая связь иллюстрируется задачей «о деталях и поставщиках» («parts and suppliers»). (Указанную деталь можно получить от разных поставщиков.

Как тогда составить список, показывающий, какие детали приходят от каких поставщиков?) Но я уже столько раз видел этот пример, что хочу привести другой. И хотя общая идея остается той же, давайте все же рассмотрим мой пример. Вы с приятелями – страстные поклонники юкера (euchre – карточная игра для четверых, в которую играют парами). Каждый год вы собираетесь вместе, делитесь на пары и организуете товарищеский турнир. Естественно, чтобы избежать споров об итогах каждого матча, вы записываете состав пар и результаты в базу данных. Можно было бы хранить результаты в таблице, куда каждый год вы записываете названия команд-участниковтурнира, количества побед и поражений, имена игроков и место их проживания:

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 |
+----------+------+-----+------+----------+---------------+

Из таблицы видно, что в каждой команде несколько участников и каждый игрок выступал в нескольких командах.


В таблице зафиксирована связь «многие-ко-многим», но она не приведена к нормальной форме. Каждая строка хранит избыточную информацию. (Данные о каждой команде записаны несколько раз, как и сведения о каждом игроке.) Лучше представить эту связь «многие-ко-многим» так:

• Хранить название каждой команды, год и победы-поражения единожды в таблице euchre_team.

• Хранить имя каждого игрока и город его проживания единожды в таблице euchre_player.

• Создать третью таблицу, euchre_link, в которой будут храниться соответствия игроков и команд и которая будет служить связкой (link) или мостом (bridge) между двумя исходными таблицами. Чтобы минимизировать информацию, хранящуюся в этой таблице, присвоим уникальный идентификатор каждой команде и каждому игроку в соответствующих им таблицах, и будем хранить в таблице euchre_link только такие идентификаторы.

Таблицы игроков и команд будут выглядеть так:

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 |
+--+----------+---------+

Таблица euchre_link сопоставляет игроков и команды:

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 |
+----------+-----------+

Чтобы отвечать на вопросы о командах игроков с помощью созданных таблиц, нужно выполнить трехстороннее соединение, используя таблицу euchre_link для связывания двух базовых таблиц друг с другом.


Рассмотрим несколько примеров:

• Выведем все пары, включающие сведения о командах и их участниках. Этот запрос перечисляет все соответствия между таблицами euchre_teamи euchre_player и воспроизводит информацию, которая изначально содержалась в ненормализованной таблице euchre:

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 |
+----------+------+-------+--------+----------+---------+

• Выведем членов определенной команды (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 |
+-------+--------+

• Выведем команды, за которые выступал определенный игрок (Billy):

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 |
+----------+-------+------+--------+

Обратите внимание на то, что для ответа на вопросы о связях «многие-комногим» необходимо использовать трехстороннее соединение, но это не означает, что трехстороннее соединение само по себе подразумевает связь «многие-ко-многим».


Ранее в разделе мы объединяли таблицу states с таблицей painting для сопоставления аббревиатурам названий штатов их полных названий:

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 |
+----------------------+-----------+

Чтобы вывести фамилию художника, написавшего каждую из картин, слегка изменим запрос, объединив результаты с таблицей artist:

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 |
+-----------+-----------------------+------------+

Теперь запрос включает трехстороннее соединение, хотя природа связи между авторами и картинами не изменилась: это связь «один-ко-многим», а не «многие-ко-многим».

Оцените статью: (0 голосов)
0 5 0

Статьи из раздела MySQL на эту тему:
Вставка записей в таблицу, включающую значения из другой
Вывод списков для записей «главная-подчиненная» и итогов
Вычисление разности между последовательными строками
Вычисление рейтинга команд
Выявление и удаление несвязанных записей