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

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

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

При выделении связи выделяют главную или родительскую таблицу (primary key table / master table) и зависимую, дочернюю таблицу (foreign key table / child table). Дочерняя таблица зависит от родительской.

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

Связи между таблицами бывают следующих типов:

Один к одному (One to one)

Один к многим (One to many)

Многие ко многим (Many to many)

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

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

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

В этом отношении первичный ключ зависимой таблицы в то же время является внешним ключом, который ссылается на первичный ключ из главной таблицы.

Например, таблица Users представляет пользователей и имеет следующие столбцы:

UserId (идентификатор, первичный ключ)

Name (имя пользователя)

И таблица Blogs представляет блоги пользователей и имеет следующие столбцы:

BlogId (идентификатор, первичный и внешний ключ)

Name (название блога)

В этом случае столбец BlogId будет хранить значение из столбца UserId из таблицы пользователей. То есть столбец BlogId будет выступать одновременно первичным и внешним ключом.

Связь один ко многим

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

К примеру, пусть будет таблица Articles, которая представляет статьи блога и которая имеет следующие столбцы:

ArticleId (идентификатор, первичный ключ)

BlogId (внешний ключ)

Title (название статьи)

Text (текст статьи)

В этом случае столбец BlogId из таблицы статей будет хранить значение из столбца BlogId из таблицы блогов.

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

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

Другой пример — статьи и теги: для одной статьи можно определить несколько тегов, а один тег может быть определен для нескольких статей.

Но в SQL Server на уровне базы данных мы не можем установить прямую связь многие ко многим между двумя таблицами. Это делается посредством вспомогательной промежуточной таблицы. Иногда данные из этой промежуточной таблицы представляют отдельную сущность.

Например, в случае со статьями и тегами пусть будет таблица Tags, которая имеет два столбца:

TagId (идентификатор, первичный ключ)

Text (текст тега)

Также пусть будет промежуточная таблица ArticleTags со следующими полями:

TagId (идентификатор, первичный и внешний ключ)

ArticleIdId (идентификатор, первичный и внешний ключ)

Технически мы получим две связи один-ко-многим. Столбец TagId из таблицы ArticleTags будет ссылаться на столбец TagId из таблицы Tags. А столбец ArticleId из таблицы ArticleTags будет ссылаться на столбец ArticleId из таблицы Articles. То есть столбцы TagId и ArticleId в таблице ArticleTags представляют составной первичный ключ и одновременно являются внешними ключами для связи с таблицами Articles и Tags.

Ссылочная целостность данных

При изменении первичных и внешних ключей следует соблюдать такой аспект как ссылочная целостность данных (referential integrity). Ее основная идея состоит в том, чтобы две таблице в базе данных, которые хранят одни и те же данные, поддерживали их согласованность. Целостность данных представляет правильно выстроенные отношения между таблицами с корректной установкой ссылок между ними. В каких случаях целостность данных может нарушаться:

Аномалия удаления (deletion anomaly). Возникает при удалении строки из главной таблицы. В этом случае внешний ключ из зависимой таблицы продолжает ссылаться на удаленную строку из главной таблицы

Аномалия вставки (insertion anomaly). Возникает при вставке строки в зависимую таблицу. В этом случае внешний ключ из зависимой таблицы не соответствует первичному ключу ни одной из строк из главной таблицы.

Аномалии обновления (update anomaly). При подобной аномалии несколько строк одной таблицы могут содержать данные, которые принадлежат одному и тому же объекту. При изменении данных в одной строке они могу прийти в противоречие с данными из другой строки.

Аномалия удаления

Для решения аномалии удаления для внешнего ключа следует устанавливать одно из двух ограничений:

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

Если строка из зависимой таблицы допускает отсутствие связи со строкой из главной таблицы (то есть такая связь необязательна), то для внешнего ключа при удалении связанной строки из главной таблицы задается установка значения NULL. При этом столбец внешнего ключа должен допускать значение NULL.

Аномалия вставки

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

Аномалии обновления

Для решения проблемы аномалии обновления применяется нормализация, которая будет рассмотрена далее.

3. Связь типа «многие-ко-многим»

При установлении связи между таблицами возможна ситуация, когда между ними нельзя установить отношение «главная-подчиненная» из-за того, что любой записи в одной таблице может соответствовать несколько записей из другой таблицы. Примером могут служить таблицы Студенты и Преподаватели, так как каждый студент сдавал экзамены нескольким преподавателям, а каждый преподаватель принимал экзамен у нескольких студентов. Поэтому между этими таблицами нельзя установить ни одну из описанных выше связей. Это пример связи типа «многие-ко-многим». Access непосредственно не поддерживает такой тип связи. Но ее можно реализовать в виде двух связей типа «один-ко-многим» с помощью третьей (связующей) таблицы. В качестве такой связующей таблицы в БД Деканат используется таблица Сессия (см. рис. 3.2). Она связана как с таблицей Студенты по полю Код студента, так и с таблицей Преподаватели по полю Код преподавателя.

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

3.3. Создание связей

Для создания связей между таблицами нужно вернуться в окно БД и закрыть все открытые таблицы(иначе на экране вы увидите сообщение: ТАБЛИЦА ЗАБЛОКИРОВАНА ЯДРОМ БАЗЫ ДАННЫХ). Далее нужно щелкнуть по кнопкеСхема данныхпанели инструментов, либо вызвать щелчком правой кнопки мыши контекстное меню и выбрать в нем пунктСхема данных. Если связи в БД определяются впервые, то будет открыто пустое окноСхема данных. В это окно нужно добавить таблицы, между которыми устанавливается связь. Для добавления таблиц следует вызвать щелчком правой кнопки мыши контекстное меню окнаСхема данныхи выбрать в нем пунктДобавить таблицу. Откроется диалоговое окноДобавление таблицы, содержащее список таблиц БД (см. рис. 3.3). Для добавления таблицы нужно щелкнуть по ее имени, а затем — по кнопкеДобавить. После того как все таблицы отобраны, нужно закрыть это окно и вернуться в окноСхема данных.

Рис. 3.3. Окно Добавление таблицы

Диалоговое окно Добавление таблицыдает возможность добавить как таблицы, так и запросы. Иногда нужно определить связи между таблицами и запросами или только между запросами, чтобы Access знал, как правильно объединять эти объекты.

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

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

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

Режим обеспечения целостности данных этой связи можно включить, если выполнены следующие условия:

поле связи главной таблицы является первичным ключом или имеет уникальный индекс;

связанные поля имеют один и тот же тип данных;

обе связанные таблицы принадлежат одной базе данных Access.

Если для связи включен этот режим, то можно дополнительно указать, следует ли автоматически выполнять для связанных записей операции каскадного обновления и каскадного удаления. Если включить режим Каскадное обновление связанных полей, то при изменении значения ключа в главной таблице будут автоматически обновлены соответствующие значения в связанных записях подчиненной таблицы. При включении режимаКаскадное удаление связанных записейпри удалении записи из главной таблицы будут автоматически удалены связанные с ней записи в подчиненной таблице. В том случае, когда эти режимы не включены, а режим обеспечения целостности данных включен, Access не позволит изменить значение в ключевом поле главной таблицы, а также удалить запись в главной таблице, если в подчиненной таблице имеются данные, связанные с этой записью. После завершения операции создания связи нужно нажать кнопкуОК. Связь отображается в виде линии, соединяющей две таблицы. Если включен режим обеспечения целостности данных, то Access изобразит на конце линии, соответствующей главной таблице, цифру1. На другом конце линии, соответствующем подчиненной таблице, будет изображен символ бесконечности∞для связи типа «один-ко-многим» и цифра1для связи типа «один-к-одному».

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

Любую связь можно изменить или удалить. Для изменения связи нужно сделать двойной щелчок по линии связи, и отредактировать ее в открывшемся окне Связи. Чтобы удалить связь, следует щелкнуть по ней и нажать клавишуDelete.

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

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

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

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

SQL для начинающих: часть 3 — связи базы данных

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

Вы также можете увидеть базы данных SQL в действии, просмотрев SQL scripts, apps and add-ons на рынке Envato.

Напоминание

Введение

При создании базы данных здравый смысл подсказывает, что мы используем отдельные таблицы для разных типов сущностей. Например: клиенты, заказы, предметы, сообщения. Но нам также нужно иметь отношения между этими таблицами. Например, клиенты делают заказы, а заказы содержат предметы. Эти отношения должны быть представлены в базе данных. Кроме того, при получении данных с помощью SQL нам нужно использовать определённые типы запросов JOIN, чтобы получить то, что нам нужно.

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

• Отношения один к одному
• Отношения «один ко многим» и «многие к одному»
• «Многие ко многим» отношения
• Самостоятельные ссылки

При выборе данных из нескольких таблиц с отношениями мы будем использовать запрос JOIN. Существует несколько типов JOIN, и мы собираемся узнать следующее:

• Перекрестные соединения
• Обычные соединения
• Внутренние соединения
• Левые (внешние) соединения
• Правые (внешние) соединения

Мы также узнаем об оговорках ON и USING.

Отношения один к одному

Предположим, у вас есть таблица для клиентов:

Мы можем поместить информацию об адресе клиента в отдельную таблицу:

Теперь мы имеем отношение между таблицей Customers и таблицей Addresses. Если каждый адрес может принадлежать только одному клиенту, это отношение «Один к одному». Имейте в виду, что такого рода отношения не очень распространены. Наша начальная таблица, которая включала адрес вместе с клиентом, в большинстве случаев могла работать нормально.

Обратите внимание: теперь в таблице Customers есть поле с именем «address_id», которое ссылается на запись соответствия в таблице Address. Это называется «Foreign Key» и используется для всех видов отношений баз данных. Мы рассмотрим этот вопрос позже.

Мы можем показать отношения между клиентскими и адресными записями следующим образом:

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

Отношения «один ко многим» и «многие к одному»

Это наиболее часто используемый тип отношений. Рассмотрим веб-сайт e-commerce со следующим:

• Клиенты могут делать много заказов.
• Заказы могут содержать много позиций.
• Позиции могут иметь описания на многих языках.

В этих случаях нам необходимо создать отношения «один ко многим». Вот пример:

У каждого клиента может быть ноль, один или несколько заказов. Но заказ может принадлежать только одному клиенту.

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

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

Для этих отношений нам нужно создать дополнительную таблицу:

Таблица Items_Orders имеет только одну цель, а именно, чтобы создать отношение «многие ко многим» между элементами и заказами.

Вот картинка таких отношений:

Если вы хотите включить записи items_orders в график, это может выглядеть так:

Самостоятельные ссылки

Это используется, когда таблица должна иметь отношения с самой собой. Например, у вас есть реферальная программа. Клиенты могут направлять других клиентов на ваш веб-сайт. Таблица может выглядеть так:

Клиенты 102 и 103 были переданы клиентом 101.

На самом деле это может быть похоже на отношение «один ко многим», поскольку один клиент может ссылаться на нескольких клиентов. Также он может выглядеть, как древовидная структура:

Один клиент может ссылаться на ноль, одного или несколько клиентов. К каждому клиенту может обращаться только один клиент, или вообще никто.

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

Foreign Keys

До сих пор мы узнали только о некоторых концепциях. Теперь пришло время воплотить их в жизнь с помощью SQL. Для этой части нам нужно понять, что такое Foreign Keys.

В приведённых выше примерах отношений мы всегда имели эти поля «**** _ id», которые ссылались на столбец в другой таблице. В этом примере столбец customer_id в таблице Orders является столбцом Foreign Key:

В базе данных типа MySQL есть два способа создания столбцов внешних ключей:

Чёткое определение Foreign Key

Давайте создадим простую таблицу клиентов:

Теперь таблицу заказов, в которой будет Foreign Key:

Оба столбца (customers.customer_id и orders.customer_id) должны иметь одинаковую структуру данных. Если один является INT, другой не должен быть BIGINT, например.

Обратите внимание, что в MySQL только механизм InnoDB имеет полную поддержку Foreign Keys. Но другие механизмы хранения данных по-прежнему позволят вам указывать их без каких-либо ошибок. Кроме того, столбец Foreign Key индексируется автоматически, если не указать для него другой индекс.

Без явной декларации

Та же таблица заказов может быть создана без явного объявления столбца customer_id как Foreign Key:

При получении данных с помощью запроса JOIN вы всё равно можете рассматривать этот столбец как Foreign Key , хотя механизм базы данных не знает об этом отношении.

Далее мы собираемся узнать о JOIN-запросах.

Визуализация отношений

Моим любимым программным обеспечением для проектирования баз данных и визуализации отношений Foreign Key является MySQL Workbench.

После разработки базы данных вы можете экспортировать SQL и запустить его на своем сервере. Это очень удобно для больших и сложных баз данных.

JOIN Queries

Для извлечения данных из базы, имеющей отношения, нам часто приходится использовать JOIN queries.

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

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

Перекрестное соединение

Это тип JOIN query по умолчанию, если условие не указано.

Результатом является так называемый «Cartesian product» таблиц. Это означает, что каждая строка из первой таблицы сопоставляется с каждой строкой второй таблицы. Так как каждая таблица имела 4 строки, мы получили результат из 16 строк.

Ключевое слово JOIN может быть опционально заменено запятой.

Конечно, такой результат не очень полезен. Давайте посмотрим на другие типы соединений.

Обычное соединение

При таком типе JOIN query таблицы должны иметь имя соответствующего столбца. В нашем случае обе таблицы имеют столбец customer_id. Таким образом, MySQL будет присоединяться к записям только тогда, когда значение этого столбца соответствует двум записям.

Как вы можете видеть, столбец customer_id отображается только один раз, потому что ядро базы данных рассматривает это как общий столбец. Мы видим два заказа Адама, а два других — Джо и Сэнди. Наконец, мы получаем некоторую полезную информацию.

Внутреннее соединение

Когда указано условие соединения, выполняется Inner Join. В этом случае было бы неплохо иметь поле customer_id в обеих таблицах. Результаты должны быть похожими на Natural Join.

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

Давайте добавим еще несколько условий в запрос.

На этот раз мы получили заказы на сумму более $15.

ON Clause

Прежде чем перейти к другим типам соединений, нам нужно посмотреть ON clause. Это полезно для помещения условий JOIN в отдельное предложение.

Теперь мы можем отличить условие JOIN от условий WHERE. Но есть и небольшая разница в функциональности. Мы увидим это в примерах LEFT JOIN.

USING Clause

USING clause похоже на предложение ON, но оно короче. Если столбец имеет одинаковое имя в обеих таблицах, мы можем указать его здесь.

На самом деле это похоже на NATURAL JOIN, поэтому столбец join (customer_id) не повторяется дважды в результатах.

Левое (внешнее) соединение

LEFT JOIN — это тип внешнего соединения. В этих запросах, если во второй таблице не найдено совпадений, запись из первой таблицы по-прежнему отображается.

Хотя у Энди нет заказов, его запись все ещё отображается. Значения под столбцами второй таблицы имеют значение NULL.

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

Всё, что мы сделали, это нашли NULL для order_id.

Также обратите внимание, что ключевое слово OUTER является необязательным. Вы можете просто использовать LEFT JOIN вместо LEFT OUTER JOIN.

Условия

Теперь давайте рассмотрим запрос с условием.

Так что случилось с Энди и Сэнди? LEFT JOIN должен был вернуть клиентов без соответствующих заказов. Проблема в том, что предложение WHERE блокирует эти результаты. Чтобы их получить, мы можем попытаться включить условие NULL.

У нас Энди, но нет Сэнди. Тем не менее это выглядит не так. Чтобы получить то, что мы хотим, нам нужно использовать ON clause.

Теперь у нас есть все, и все заказы выше $ 15. Как я уже говорил, ON clause иногда имеет несколько иную функциональность, чем WHERE clause. В условии Outer Join , таком как этот, строки включаются, даже если они не соответствуют условиям ON clause.

Правое (внешнее) соединение

RIGHT OUTER JOIN работает точно так же, но порядок таблиц обратный.

На этот раз у нас нет результатов NULL, потому что каждый заказ имеет соответствующую запись клиента. Мы можем изменить порядок таблиц и получить те же результаты, что и в LEFT OUTER JOIN.

Теперь у нас есть эти значения NULL, потому что таблица Customers находится на правой стороне соединения.

Заключение

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

Не забудьте проверить SQL scripts, apps and add-ons на рынке Envato. Вы получите представление о возможностях баз данных SQL, и сможете найти идеальное решение, которое поможет вам в текущем проекте разработки.

Следуйте за нами на Twitter или подпишитесь на Nettuts + RSS Feed для получения лучших обучающих материалов по веб-разработке в Интернете.