Много ко многим

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

+--------+------------+-------------+---------------+----------------+
| gameid | hometeamid | guestteamid | hometeamgoals | guestteamgoals |
+--------+------------+-------------+---------------+----------------+
|     47 |          2 |           1 |             2 |              2 |

Теперь у нас есть команда игроков и тренеров:

+----------+--------+------------+--------------+------------+
| personid | teamid | firstname  | lastname     | positionid |
+----------+--------+------------+--------------+------------+
|        1 |      1 | Ole Gunnar | Solskjær     |          1 |
|        2 |      1 | Michael    | Carrick      |          1 |
|        3 |      1 | Kieran     | McKenna      |          1 |
|        4 |      1 | David      | de Gea       |          2 |

Если мы добавим колонку к таблице игроков для хранения игры, то сможем сказать, что эти игроки участвовали:

+----------+--------+------------+--------------+---------------------+
| personid | teamid | firstname  | lastname     | positionid | gameid |
+----------+--------+------------+--------------+---------------------+
|        1 |      1 | Ole Gunnar | Solskjær     |          1 |     47 |
|        2 |      1 | Michael    | Carrick      |          1 |     47 |
|        3 |      1 | Kieran     | McKenna      |          1 |     47 |
|        4 |      1 | David      | de Gea       |          2 |     47 |

Отлично, четыре человечка из Манчестер Юнайтед сыграли в матче 47, в котором играли Манчестер Юнайтед и Ливерпуль. Можно добавить еще и игроков Ливерпуля, но уже этого достаточно, чтобы рассмотреть печальку.

MU еще играл и с Арсеналом в игре под номером 48 (если вы позабыли то MU это команда под номером 1 и она тут гостевая):

+--------+------------+-------------+---------------+----------------+
| gameid | hometeamid | guestteamid | hometeamgoals | guestteamgoals |
+--------+------------+-------------+---------------+----------------+
|     48 |          3 |           1 |             3 |              0 |

Несколько человек может участвовать в одной игре и эту проблему мы решили добавлением колонки в таблицу людей. С другой стороны, один и тот же человек мог играть в нескольких играх. Может мы не туда добавили колонку для связи и ее нужно было добавлять в таблицу игр? Нет, не поможет.

Чтобы понять проблему, давайте визуализируем это дело. Игрок deGea играл в двух матчах – MU против Арсенала и против Евертона. Мы не можем в колонку GameID засунуть в одну колонку две игры. Мы можем создать две колонки в таблице людей, чтобы хранить два числа, но если один игрок играл в 10 играх, то мы не можем создавать 10 колонок.

В игре MU арсенал играло два игрока (их было скорей всего больше, но для примера ограничимся двумя) и снова мы не можем несколько игроков засунуть в одну колонку.

Данная задача легко решается, если воткнуть между двумя таблицами еще одну, которая и обеспечит связь многие ко многим:

Смотрим на игру MU-Арсенал под номером 1. В связующей таблице аж две записи, где в колонке Game число 1, первая принадлежит игроку 1 Carrick, а вторая de Gea.

То же самое и в обратную сторону. Смотрим на игрока 3 de Gea, для которого в связующей таблице две записи и этот игрок играл в двух играх – первой игре MU Арсенал, а также MU Ливерпуль.

Круто, мы смогли сделать так, что много игроков могут играть во многих играх или по-другому, получилась связь многие ко многим, хотя по факту получается дважды один ко многим. Во связующей таблице у нас колонка Player связана многое к одному на таблицу игроков, а таблица игра – один ко многим к колонке Game во связующей таблице.

В теории разобрались, давайте создадим таблицу на практике. Нам нужна связующая таблица, которая связывает игры и игроков. Для простоты я даю таким таблицам имя из имен таблиц, которые связывают. Раз связываем game и person, значит связующую назовем gameperson, все просто.

create table gameperson (
    gameid int not null,
    personid int not null,
    primary key (gameid, personid),
    constraint fk_gameid foreign key (gameid) references game(gameid),
    constraint fk_personid foreign key (personid) references person(personid)
)

Обратите внимание, что в качестве первичного ключа я не заводил никаких дополнительных колонок с автоматическим увеличением. Это можно сделать, но в данном случае это что-то лишнее, потому что идентификатор игры и идентификатор человека вместе взяты обязаны быть уникальными. Один игрок в одной и той же игре не должен играть дважды. Так что если мы засунули Марадону в игру между Аргентиной и Бразилией один раз, то второй раз мы его засовывать уже туда его не будем, просто нет смысла. Раз две колонки создают необходимую уникальность, то мы можем создать первичный ключ из двух колонок. Это легально, первичным ключом может быть и 3 колонки, если надо и, если есть смысл.

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

добавим игроков из домашней команды

insert into gameperson
select g.gameid, p.personid
from game g
	join person p on g.hometeamid = p.teamid
where p.positionid > 1

добавим игроком из гостевой команды

insert into gameperson
select g.gameid, p.personid
from game g
	join person p on g.guestteamid = p.teamid
where p.positionid > 1

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

select ht.name, gt.name, p.firstname, p.lastname,
 case
    when p.teamid = hometeamid then 'Гостевая команда'
    else 'Домашняя команда'
 end
from game g
    join gameperson gp on g.gameid = gp.gameid 
    join person p on p.personid = gp.personid
    join team ht on ht.teamid = g.hometeamid
    join team gt on gt.teamid = g.guestteamid

У нас как-то была тестовая база, где были люди и телефоны. Давайте сделаем так, чтобы один номер телефона мог принадлежать нескольким людям, и один человек мог владеть несколькими телефонами.

Сначала убиваем колонку phoneid у таблицы людей, потому что там не должно быть прямой связи:

alter table user drop phoneid

Вместо этого создаем связующую таблицу userphone:

create table userphone (
    userid int not null,
    phoneid int not null,
    primary key (userid, phoneid),
    constraint fk_userid foreign key (userid) references user(userid),
    constraint fk_phoneid foreign key (phoneid) references phone(phoneid)    
)

Визуально это все

Теперь мы можем навести связь

Insert into userphone values (1, 1)
Insert into userphone values (1, 2)

Теперь у нас первый человек владеет первым и вторым телефоном.

Insert into userphone values (2, 5)
Insert into userphone values (3, 5)

А вот теперь второй и третий человек владеют пятым телефоном.