Как реализовать связь многие ко многим

Создание связей типа «многие-ко-многим»

Браузер не поддерживает видео.

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

Связь «многие-ко-многим» предполагает возможность связи одного или нескольких элементов из одной таблицы с одним или несколькими элементами из другой таблицы. Примеры:

В таблице «Заказы» указаны заказы, сделанные разными клиентами из таблицы «Клиенты». Каждый клиент мог сделать несколько заказов.
В таблице «Продукты» указаны продаваемые товары, каждый из которых может фигурировать в нескольких заказах из таблицы «Заказы».
Каждый продукт может входить в один заказ как в одном, так и в нескольких экземплярах.

Например, в заказ Арины Ивановой № 1012 могут входить продукты № 12 и 15, а также пять продуктов № 30.

Создание связи «многие-ко-многим»

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

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

Ниже рассмотрим пример, когда в заказ Арины Ивановой № 1012 входят продукты № 12, 15 и 30. Это значит, что записи в таблице «Сведения о заказах» выглядят следующим образом:

Арина заказала по одному продукту № 12 и 15, а также пять продуктов № 30. Создать другие строки с номером заказа 1012 и кодом продукта 30 нельзя, потому что поля «Номер заказа» и «Код продукта» вместе составляют первичный ключ, а первичные ключи должны быть уникальными. Вместо этого можно добавить в таблицу «Сведения о заказах» поле «Количество».

Создание промежуточной таблицы

Сохранение

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

Создание полей в промежуточной таблице

Столбец «Код» автоматически добавляется в Access в качестве первого. Измените имя этого поля на идентификатор вашей первой таблицы в связи «многие-ко-многим». Например, если первая таблица называется «Заказы», поле «Код» в ней переименовано в «Номер заказа», и его первичный ключ — число, измените имя поля «Код» в новой таблице на «Номер заказа», а в качестве типа данных выберите Числовой.

В режиме таблицы выберите заголовок столбца Код и введите новое имя поля.
Выберите переименованное поле.
На вкладке Поля в списке Тип данных выберите тип, как в соответствующем поле исходной таблицы, например Числовой или Короткий текст.
Щелкните надпись Щелкните для добавления и выберите тип данных, соответствующий первичному ключу во второй таблице. В заголовке столбца введите имя поля первичного ключа из второй таблицы, например «Код продукта».
Если вам требуется отслеживать другую информацию об этих записях, например количество товаров, создайте дополнительные поля.

Объединение полей для создания первичного ключа

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

Снимок экрана: первичный ключ в таблице

Откройте промежуточную таблицу в режиме конструктора.
Выберите обе строки с идентификаторами. (Если вы следовали предыдущим указаниям, это будут две первые строки.)
На вкладке Конструктор нажмите кнопку Ключевое поле.
Рядом с обоими полями будут отображаться значки ключей.

Соединение трех таблиц для создания связи «многие-ко-многим»

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

После этого связи должны выглядеть следующим образом:

Пример связи «многие-ко-многим»

Здравствуйте! Не могу понять связь «многие ко многим». Что она значит? Приведите, пожалуйста, пример, когда эту связь нужно устанавливать. Лучше даже пример из жизни приведите, пожалуйста, когда такая связь осуществляется.

Отслеживать
задан 11 янв 2012 в 12:45
elenavictory elenavictory
320 3 3 золотых знака 7 7 серебряных знаков 23 23 бронзовых знака

3 ответа 3

Сортировка: Сброс на вариант по умолчанию

«Один-ко-многим» — тип связи таблиц, когда одной записи главной таблицы можно сопоставить несколько записей подчинённой таблицы. Это наиболее частый вид связи между таблицами. Ну, например, если создавать телефонный справочник, то необходимо учесть, что у одного человека может быть несколько телефонов (2 мобильных, 1 домашний и 1 служебный). Или ещё пример: студент (записи о студентах хранятся в главной таблице) обучается в ВУЗе — он изучает несколько предметов (записи о предметах хранятся в подчинённой таблице), по которым сдаёт экзамены и зачёты.

А связь «многие-ко-многим» возникает в тех случаях, когда одной записи одной таблицы может соответствовать несколько записей другой таблицы и наоборот: когда одной записи второй таблицы может соответствовать несколько записей первой таблицы. От такого типа связи следует избавляться и приводить к виду «один-ко-многим». Пример такого вида связи: имеем 2 таблицы «Товары» и «Клиенты», каждый клиент может приобрести несколько товаров, в свою очередь каждый товар (по наименованию) может быть приобретён (или заказан) несколькими клиентами. Ещё пример (по ВУЗ): пусть есть 2 таблицы «Преподаватель» и «Студент», каждый преподаватель может обучать нескольких студентов, в то же время каждый студент может обучаться у нескольких преподавателей.

Отслеживать
ответ дан 11 янв 2012 в 12:54
DelphiM0ZG DelphiM0ZG
3,045 15 15 серебряных знаков 18 18 бронзовых знаков
А, например, в Access как устанавливается подобная связь в схеме данных?
10 ноя 2016 в 4:03
Почему «От такого типа связи следует избавляться»? Связь как связь.
1 дек 2016 в 8:40

Примеры, приведённые для связи «многие-ко-многим» хоть и верные по сути, ошибочны с точки зрения проектирования бд:) например, в примере про студентов — каждый студент состоит в группе, а преподаватель преподаёт предмет. У группы есть предметы, обратной связи никакой:) ну то есть по логике связи «многие ко многим» тут неоткуда взяться.

Пример связи многие-ко-многим (PostgreSQL)

Связь многие-ко-многим – это связь, при которой множественным записям из таблицы (A), могут соответствовать множественные записи из таблицы (B).

Пример такой связи, люди и их счета в банках.

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

manytomany

Таблицы person, bank, person_bank

Связь многие-ко-многим, между таблицами person и bank осуществляется при помощи третьей таблицы person_bank, у которой 2 внешних ключа на таблицы person и bank соответственно. Чтобы связь пользователь-банк не повторялась дважды, на поля person_id и bank_id создается уникальное ограничение или проще ключ.

CREATE TABLE person ( id SERIAL PRIMARY KEY, full_name VARCHAR ); CREATE TABLE bank ( id SERIAL PRIMARY KEY, name VARCHAR ); CREATE TABLE person_bank ( id BIGSERIAL PRIMARY KEY, person_id INTEGER NOT NULL REFERENCES person, bank_id INTEGER NOT NULL REFERENCES bank, UNIQUE (person_id, bank_id) );

Данные

Люди и их связь с банками.

INSERT INTO bank (id, name) VALUES (1, ‘Сбер’), (2, ‘Тинькофф’), (3, ‘Райффайзен’), (4, ‘ВТБ’); INSERT INTO person (id, full_name) VALUES (1, ‘Иванов Сидор Петрович’), (2, ‘Сидоров Петр Иванович’), (3, ‘Петров Иван Сидорович’), (4, ‘Наличный Артем Андреевич’); INSERT INTO person_bank (person_id, bank_id) VALUES (1, 1), (1, 3), (2, 2), (2, 3), (2, 4), (3, 1), (3, 4);

Связь многие-ко-многим

Выберем всех людей и их банки.

SELECT p.full_name AS person_full_name, b.name AS bank_name FROM person p LEFT JOIN person_bank pb ON pb.person_id = p.id LEFT JOIN bank b ON b.id = pb.bank_id;

В результате будут возвращены следующие данные:

person_full_name	bank_name
Иванов Сидор Петрович	Сбер
Иванов Сидор Петрович	Райффайзен
Сидоров Петр Иванович	Тинькофф
Сидоров Петр Иванович	Райффайзен
Сидоров Петр Иванович	ВТБ
Петров Иван Сидорович	Сбер
Петров Иван Сидорович	ВТБ
Наличный Артем Андреевич

Как видно в таблице выше, у Иванова и Петрова по 2 банка, у Сидорова 3 у Наличного нет связи с банками вообще.

✖ ❤ Мне помогла статья нет оценок
10779 просмотров 6 комментариев Артём Фёдоров 22 января 2022

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

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

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

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

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

Таблицу «Заявка»
Таблицу «Номинация»
и т.н. «таблицу связи»

Сделаем это (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 ;

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

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

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

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 (чтобы вы тоже могли поэкспериментировать с запросами)

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

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

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

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

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

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

Если вам требуется от массива связанных сущностей только одно поле (напр. имена участников), то решить задачу можно вообще одним 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, а минусом то, что с этим значением уже нельзя работать как с массивом, явно не преобразовав.

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

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

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

Key Words for FKN + antitotal forum (CS VSU):

ON DELETE CASCADE
многие ко многим
составной уникальный индекс
пример
каскадное удаление
таблица связи
mySQL
mysql многие ко многим пример
связь многие ко многим sql пример
многие ко многим пример
связь многие ко многим подробное объяснение
как извлекать данные
select многие ко многим