mysql Связь "Многие ко Многим" -- пример SQL кода таблиц с пояснениями. Таблица связи (ON DELETE CASCADE). Получение данных

Ситуация

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

Далее будет использоваться синтаксис mysql.

Проектируем базу для связи Многие-ко-Многим -- sql для создания таблиц

Нам потребуется создать три таблицы:

1. Таблицу "Заявка"
2. Таблицу "Номинация"
3. и т.н. "таблицу связи"

Сделаем это (SQL):

CREATE TABLE `Tickets` (
    `ticketID` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL
        DEFAULT '' COMMENT 'Имя участника/название организации',
    `info` VARCHAR(255) NULL
        DEFAULT '' COMMENT 'Информация о номинанте',
    PRIMARY KEY (`ticketID`)
)
COMMENT='Заявки учасников конкурса'
ENGINE=InnoDB
;
  
CREATE TABLE `Nominations` (
    `nominationID` INT(11) NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(255) NULL
        DEFAULT NULL COMMENT 'Название номинации',
    PRIMARY KEY (`nominationID`)
)
COMMENT='Номинации конкурса'
ENGINE=InnoDB
;
  
CREATE TABLE `Tickets_Nominations` (
    `ticket_id` INT(11) NOT NULL,
    `nomination_id` INT(11) NOT NULL,
    PRIMARY KEY (`ticket_id`, `nomination_id`),
    INDEX `ticket_id` (`ticket_id`),
    INDEX `nomination_id` (`nomination_id`),
    CONSTRAINT `FK_Nominations` FOREIGN KEY (`nomination_id`) 
        REFERENCES `Nominations` (`nominationID`) ON DELETE CASCADE,
    CONSTRAINT `FK_Ticket` FOREIGN KEY (`ticket_id`) 
        REFERENCES `Tickets` (`ticketID`) ON DELETE CASCADE
)
COMMENT='Таблица связи заявок участников и номинаций конкурса'
ENGINE=InnoDB
;

Обратите внимание на:

1. Свойство "ON DELETE CASCADE" --
   это значит, что если будет удалена запись в другой таблице, на которую ссылается данный кортеж (из таблицы связи), то и этот кортеж будет удалён целиком. В данном случае связь удаляется из таблицы если удалено хотя быть что-то одно из двух:
   • или заявка, на которую он ссылается
   • или номинация, на которую он ссылается

   -- таким образом мы переносим задачу удаления неактуальный связей с приложения на СУБД.

2. В этом примере использован составной первичный ключ PRIMARY KEY ,ведь там написано:
   

   PRIMARY KEY (`ticket_id`, `nomination_id`),
   

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

   CREATE TABLE `Tickets_Nominations` (
       `id` INT(11) NOT NULL AUTO_INCREMENT,
       `ticket_id` INT(11) NOT NULL,
       `nomination_id` INT(11) NOT NULL,
       INDEX `ticket_id` (`ticket_id`),
       INDEX `nomination_id` (`nomination_id`),
       CONSTRAINT `FK_Nominations` FOREIGN KEY (`nomination_id`) 
   		REFERENCES `Nominations` (`nominationID`) ON DELETE CASCADE,
       CONSTRAINT `FK_Ticket` FOREIGN KEY (`ticket_id`) 
   		REFERENCES `Tickets` (`ticketID`) ON DELETE CASCADE,
   	PRIMARY KEY (`id`),
   	UNIQUE KEY `relation_row_unique` (`ticket_id`,`nomination_id`)
   )
   COMMENT='Таблица связи заявок участников и номинаций конкурса'
   ENGINE=InnoDB
   ; 
   

   -- но часто вполне можно обойтись и изначальным вариантом, не создавая "лишний" столбец.

Извлечение данных для связи "многие ко многим" (SELECT)

Возникает логичный вопрос -- как же получать данные из базы, используя таблицу связи?
Есть разные варианты для разных ситуаций, которые мы сейчас рассмотрим, но прежде чем проиллюстрировать их, заполните созданные выше таблицы данными с помощью такого sql (чтобы вы тоже могли поэкспериментировать с запросами)

Рассмотрим задачу извлечения участников, связанных с данной номинацией -- или короче "номинации, и всех, кто подал в неё заявки" (алгоритм извлечения данных в обратную сторону -- т.е. "участик и все его номинации" абсолютно аналогичен).
На практике приходится сталкиваться с двумя базовыми ситуациями:

1. Извлечение одной сущности номинации и связанных с ней участников
2. Извлечение списка сущностей номинаций и связанных с каждой из номинаций участников (т.е. фактически список участников для каждого элемента из списка номинаций).

Извлечение связанных (многие-ко-многим) данных для одной сущности

Пусть у нас известен id () номинации и мы хотим получить сведения об этой номинации и всех участниках в ней.
Во-первых, сделать это можно двумя sql запросами:

1. Сначала просто получим кортеж этой номинации:
   

   mysql> SELECT * FROM Nominations WHERE nominationID=4;
   +--------------+-----------------------------+
   | nominationID | title                       |
   +--------------+-----------------------------+
   |            4 | Лучшее пособие              |
   +--------------+-----------------------------+
   
   

2. После, опять же зная id номинации (используем в WHERE), достаточно просто сделать LEFT JOIN между таблицей связи и таблицей участников:
   

   SELECT * FROM  Tickets_Nominations LEFT JOIN Tickets 
   	ON ticket_id = ticketID 
   	WHERE Tickets_Nominations.nomination_id = 4;

   Получим:

   +-----------+---------------+----------+-------------------------- +----------------------------------------------+
   | ticket_id | nomination_id | ticketID | name                      | info                                         |
   +-----------+---------------+----------+---------------------------+----------------------------------------------+
   |         3 |             4 |        3 | Программирование для всех | Некоммерческая образовательная организация  
   |         4 |             4 |        4 | Юный программист          | Кружок для детей в д. Простоквашино        
   |         5 |             4 |        5 | IT FOR FREE               | Русскоязычное IT-сообщество с уклоном в web  
   |         6 |             4 |        6 | Саша Петров               | Студент 2 курса, автор пособия по SQL   
   

   -- как видим, тут мы получили вообще все колонки (т.к. в запросе указали звездочку *) двух соединённых таблиц (связи и заявок).
   Также видим что на номинации с id=4 номинировалось 4-ре участника, кроме их имен видны также и описания.
   Все эти данные можно использовать в приложении, после выполнения запроса к БД -- например записать, то что нужно в поле, хранящее массив объекта конкретной номинации.

Если вам требуется от массива связанных сущностей только одно поле (напр. имена участников), то решить задачу можно вообще одним sql запросом, используя группировку (GROUP BY) и применимую к группируемым значения колонки функцию конкатенации GROUP_CONCAT():

SELECT 
    Nominations.*, 
	 GROUP_CONCAT(Tickets.name SEPARATOR ', ') as participants_names
 FROM  
   Nominations LEFT JOIN Tickets_Nominations 
	   ON Nominations.nominationID = Tickets_Nominations.nomination_id 
	LEFT JOIN Tickets  
	   ON Tickets.ticketID = Tickets_Nominations.ticket_id
 
WHERE Tickets_Nominations.nomination_id = 4
 	GROUP BY Nominations.nominationID;

Получим единственный кортеж:

+--------------+-----------------------------+-----------------------------------------------------------------------------------------------------------------------+
| nominationID | title                       | participants_names                                                                                                    |
+--------------+-----------------------------+-----------------------------------------------------------------------------------------------------------------------+
|            4 | Лучшее пособие              | Программирование для всех, Юный программист, IT FOR FREE, Саша Петров                                                 |
+--------------+-----------------------------+-----------------------------------------------------------------------------------------------------------------------+

-- здесь мы:

• провели сразу тройной JOIN, как бы поставив таблицу связи между таблицами номинаций и заявок.
• нас интересовали имена участников для 4 номинации -- поэтому использовали WHERE Tickets_Nominations.nomination_id = 4
• Группировка (чтобы в итоге получить только одну строку-кортеж) проходила по id номинации (Nominations.nominationID)
• Сконкатенированному полю мы назначили псевдоним (participants_names)

Плюсом такого подхода является то, что в приложении можно использовать готовую строку participants_names, а минусом то, что с этим значением уже нельзя работать как с массивом, явно не преобразовав.

Извлечение списка сущностей со связанными данными

Прежде всего можно:

1. Cначала извлечь (SELECT) необходимые номинации (или вообще все),
2. а потом уровне приложения в цикле извлечь связанные данные для каждой номинации отдельно (как это показано выше) -- это не оптимальный способ так как он порождает много запросов к БД (так что если список номинаций - - или иных сущностей велик, то и запросы сильно скажутся на суммарном времени выполнении скрипта и нагрузке на процессор)