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

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

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

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

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

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

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

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

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

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

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

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

Сохранение

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

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

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

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

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

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

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

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

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

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

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

Отношение многие-ко-многим (many-to-many) между таблицами

Entity Framework поддерживает реализацию отношения многие-ко-многим (many-to-many) между таблицами в базе данных. Такая связь в реляционных базах данных обеспечивается за счет использования промежуточной таблицы, с которой связываются две другие таблицы через отношение один-ко-многим. В результате между этими двумя таблицами образуется связь многие-ко-многим. Фактически в такой связи нет главной и зависимой таблицы, как мы описывали это для связей один-к-одному и один-ко-многим, промежуточная таблица является зависимой от двух главных таблиц.

Давайте продолжим рассмотрение нашего примера модели, в которой мы определили таблицу покупателя и его заказы. В таблице заказов (Orders) мы использовали поля, описывающие наименование товара и его стоимость. Очевидно, что использование такой структуры базы данных является неэффективным. Более правильный подход, предусматривает создание таблицы Product, в которой содержится коллекция товаров для магазина, а между таблицами Product и Customer нужно создать отношение многие-ко-многим за счет использования промежуточной таблицы Orders. То есть в контексте нашего примера связь многие-ко-многим говорит о том, что один покупатель может заказать несколько товаров, и один товар может быть заказан несколькими покупателями.

Давайте реализуем такую модель в Code-First. Ниже показана структура модели, при которой Entity Framework автоматически распознает связь многие-ко-многим между двумя таблицами Customers и Products (мы удалили класс Profile, который использовали при обсуждении отношения один-к-одному в предыдущей статье, а также класс Order):

public class Customer < public int CustomerId < get; set; >public string FirstName < get; set; >public string LastName < get; set; >public string Email < get; set; >public int Age < get; set; >public byte[] Photo < get; set; >// Заказанные покупателем товары public List Products < get; set; >> public class Product < public int ProductId < get; set; >public string ProductName < get; set; >public string Description < get; set; >public decimal Cost < get; set; >// Список покупателей, заказавших этот товар public List Customers < get; set; >>

Если вы запустите приложение, то Entity Framework автоматически воссоздаст базу данных, т.к. модель изменилась. Code-First сможет распознать связь между этими классами как многие-ко-многим и создаст промежуточную таблицу, которая содержит внешние ключи, ссылающиеся на таблицы Customers и Products, как показано на рисунке ниже:

Создание трех таблиц при определении отношения многие-ко-многим

Стоит отметить, что эти внешние ключи являются и первичными ключами промежуточной таблицы. Имя промежуточной таблицы состоит из имен двух связанных таблиц, в нашем примере она имеет имя ProductCustomers. Имена сгенерированных внешних ключей следуют тем же соглашениям, что мы описали ранее. После того, как отношение многие-ко-многим определено, Entity Framework знает, какие нужно использовать SQL-операторы для вставки, удаления и обновления данных в связанных таблицах.

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

protected override void OnModelCreating(DbModelBuilder modelBuilder) < modelBuilder.Entity() .HasMany(c => c.Products) .WithMany(p => p.Customers) .Map(m => < // Ссылка на промежуточную таблицу m.ToTable("Orders"); // Настройка внешних ключей промежуточной таблицы m.MapLeftKey("CustomerId"); m.MapRightKey("ProductId"); >); >

Обратите внимание, что здесь мы явно задаем имя промежуточной таблицы при вызове вспомогательного метода Map(), в результате чего Entity Framework создаст при запуске примера таблицу с именем Orders, а не ProductCustomers.

Мы также явно задаем имена внешних ключей, используя методы MapLeftKey() и MapRightKey() объекта конфигурации ManyToManyAssociationMappingConfiguration, который передается методу Map() в параметре делегата Action. Эти методы указывают левый и правый ключи для промежуточной таблицы. Чтобы их не перепутать, вы должны запомнить простое правило, левый ключ указывает на класс сущности, для которого мы вызвали метод HasMany() ранее, а правый ключ указывает на класс сущности, для которого мы вызвали метод WithMany(). Если вы их перепутаете ничего страшного не произойдет, просто внешний ключ CustomerId будет ссылаться на таблицу Product, а внешний ключ ProductId на таблицу Customer.

Пример связи многие-ко-многим (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 у Наличного нет связи с банками вообще.

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

Руководство по проектированию реляционных баз данных (7-9 часть из 15) [перевод]

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

Другой пример связи один-ко-многим – это связь, которая существует между матерью и ее детьми. Мать может иметь множество детей, но каждый ребенок может иметь только одну мать.

(Технически лучше говорить о женщине и ее детях вместо матери и ее детях потому, что, в контексте связи один-ко-многим, мать может иметь 0, 1 или множество потомков, но мать с 0 детей не может считаться матерью. Но давайте закроем на это глаза, хорошо?)

Когда одна запись в таблице А может быть связана с 0, 1 или множеством записей в таблице B, вы имеете дело со связью один-ко-многим. В реляционной модели данных связь один-ко-многим использует две таблицы.

Схематическое представление связи один-ко-многим. Запись в таблице А имеет 0, 1 или множество ассоциированных ей записей в таблице B.

Как опознать связь один-ко-многим?

Если у вас есть две сущности спросите себя:
1) Сколько объектов и B могут относится к объекту A?
2) Сколько объектов из A могут относиться к объекту из B?

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

Примеры.

Некоторые примеры связи один-ко-многим:

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

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

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

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

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

Обратите внимание, что при проектировании базы данных вы должны спросить себя не о том, существуют ли определенные связи в данный момент, а о том, возможно ли существование связей вообще, в перспективе. Если в настоящий момент все поставщики предоставляют множество видов пива, но каждый вид пива предоставляется только одним поставщиком, то вы можете подумать, что это связь один-ко-многим, но… Не торопитесь реализовывать связь один-ко-многим в этой ситуации. Существует высокая вероятность того, что в будущем два или более поставщиков будут поставлять один и тот же вид пива и когда это случится ваша база данных — со связью один-ко-многим между поставщиками и видами пива – не будет подготовлена к этому.

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

Связь многие-ко-многим создается с помощью трех таблиц. Две таблицы – “источника” и одна соединительная таблица. Первичный ключ соединительной таблицы A_B – составной. Она состоит из двух полей, двух внешних ключей, которые ссылаются на первичные ключи таблиц A и B.

Все первичные ключи должны быть уникальными. Это подразумевает и то, что комбинация полей A и B должна быть уникальной в таблице A_B.

Пример проект базы данных ниже демонстрирует вам таблицы, которые могли бы существовать в связи многие-ко-многим между бельгийскими брендами пива и их поставщиками в Нидерландах. Обратите внимание, что все комбинации beer_id и distributor_id уникальны в соединительной таблице.

Таблицы “о пиве”.

Таблицы выше связывают поставщиков и пиво связью многие-ко-многим, используя соединительную таблицу. Обратите внимание, что пиво ‘Gentse Tripel’ (157) поставляют Horeca Import NL (157, AC001) Jansen Horeca (157, AB899) и Petersen Drankenhandel (157, AC009). И vice versa, Petersen Drankenhandel является поставщиком 3 видов пива из таблицы, а именно: Gentse Tripel (157, AC009), Uilenspiegel (158, AC009) и Jupiler (163, AC009).

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

Есть еще одна важная вещь на которую нужно знать. Связь многие-ко-многим состоит из двух связей один-ко-многим. Обе таблицы: поставщики пива и пиво – имеют связь один-ко-многим с соединительной таблицей.

Другой пример связи многие-ко-многим: заказ билетов в отеле.

В качестве последнего примера позвольте мне показать как бы могла быть смоделирована таблица заказов номеров гостиницы посетителями.

Соединительная таблица связи многие-ко-многим имеет дополнительные поля.

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

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

9. Связь один-к-одному.

В связи один-к-одному каждый блок сущности A может быть ассоциирован с 0, 1 блоком сущности B. Наемный работник, например, обычно связан с одним офисом. Или пивной бренд может иметь только одну страну происхождения.

В одной таблице.

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

В отдельных таблицах.

В редких случаях связь один-к-одному моделируется используя две таблицы. Такой вариант иногда необходим, чтобы преодолеть ограничения РСУБД или с целью увеличения производительности (например, иногда — это вынесение поля с типом данных blob в отдельную таблицу для ускорения поиска по родительской таблице). Или порой вы можете решить, что вы хотите разделить две сущности в разные таблицы в то время, как они все еще имеют связь один-к-одному. Но обычно наличие двух таблиц в связи один-к-одному считается дурной практикой.

Примеры связи один-к-одному.

Люди и их паспорта. Каждый человек в стране имеет только один действующий паспорт и каждый паспорт принадлежит только одному человеку.

Проект реляционной базы данных – это коллекция таблиц, которые перелинковываются (связываются) первичными и внешними ключами. Реляционная модель данных включает в себя ряд правил, которые помогают вам создать верные связи между таблицами. Эти правила называются “нормальными формами”. В следующих частях я покажу как нормализовать вашу базу данных.

Какой же вид связи вам нужен?

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

А если есть некие данные, которые могу быть присвоены любому человеку, то имеем дело со связью многие-ко-многим. Например, есть таблица со списком людей и мы хотим хранить информацию о том, какие страны посетил каждый человек. В данном случае имеется две сущности: люди и страны. Любой человек может посетить любое количество стран равно, как и любая страна может быть посещена любым человеком. Т.е., в данном случае, страна не является уникальными данными для конкретного человека и может использоваться повторно.

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

А когда у вас есть набор уникальных данных, которые имеют отношение только друг к другу, то храните все в одной таблице. Ваш выбор – связь один-к-одному. Например, у вас есть небольшая коллекция автомобилей и вы хотите хранить информацию о них (цвет, марка, год выпуска и пр.).

sql
mysql
проектирование баз данных

Читать:

Hyundai бытовая техника чей бренд