Связь многие-ко-многим: пример в Access, в SQL. Как сделать связь многие-ко-многим?

Во всех СУБД (системах управления базами данных) имеется несколько типов отношений между таблицами. Среди них связь один-к-одному, один-к-многим, многие-к-одному (некоторые склонны отождествлять эти два типа в один) и связь многие-ко-многим. Пример последней, ее объяснение и применение в различных СУБД, таких как Access или SQL, будет рассмотрено в этой статье.

Определение

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

Представленная схема наглядно отображает суть данного отношения.

Когда может быть использована связь многие-ко-многим?

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

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

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

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

Microsoft Access

Офисный программный продукт от "Майкрософт" известен на рынке ПО уже довольно продолжительное время. Он поставляется вместе с текстовым редактором Worfd, табличным процессором Excel и прочими, входящими в линейку "офиса". Можно Access (читается как "аксес", дословный перевод - "доступ") приобрести и отдельно от его "коллег". Рекомендуется купить, разумеется, лицензионный софт, но ни для кого не секрет, сколько пиратских репаков можно найти на просторах Сети, в виде обычных файлов или раздач торрентов. "Майкрософт Аксес" доступен даже в портативной сборке. Она, не требующая инсталляции и особых навыков работы с ПК, лучше всего подходит для выбора, если ПО не будет использоваться продолжительно и часто.

Из контекста ясно, что "Майкрософт Аксес" - это система управления базами данных. Причем одна из популярнейших. Она является реляционной, что значит, она основана на логической модели данных, которая в ходе своей работы обращается к теории множеств и логике первого порядка. Связь многие-ко-многим в Access (примеры будут даны в ходе объяснения) реализуется очень и очень просто. Рассмотрим ее.

Есть две таблицы..

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

Видео: 4 Создание связи многие ко многим

Если рассмотреть иной пример: допустим, футболисты и команды ( с учетом того, что хотя бы один из футболистов играл за разные сборные, и каждая сборная имеет в своем составе одиннадцать игроков), суть построения связи не поменяется. Также будут нужны три таблицы. Из них "Футболисты" и "Команды" в качестве основных, и одна промежуточная.

Схема данных

Связи между таблицами в СУБД "Майкрософт Аксес" реализуются при помощи вкладки "Схема данных". На появившуюся панель добавляются все необходимые сущности (в нашем случае все три таблицы). Создание связи многие-ко-многим будет происходить при помощи двух отношений один-к-многих между главными ("Студенты" и "Преподаватели") и промежуточной таблицы. Для этого необходимо соединить между собой соответствующие первичные ключи.

На иллюстрации выше показано, как выглядит вкладка "Схема данных" (Relathionships). Количество добавляемых на панель таблиц неограниченно. Расположение полностью регулируется пользователем.

SQL

Проектирование баз данных на SQL - задача сложнее, чем на "Аксес". Если майкрософтовский продукт полностью адаптирован под офисную среду, имеет огромный и, с каждым выпуском и обновлением все расширяемый, функционал, но в то же время и удобный для простого пользователя интерфейс, то SQL - это отдельный непроцедурный язык программирования, при помощи которого на разных платформах можно работать с базами данных. Известное ПО для данной задачи: Oracle MySQL и DB2 (популярное, но не единственное в своем роде). Несмотря на то, что у каждого из них есть свои тонкости и нюансы, язык SQL их "объединяет". Научившись работать хотя бы с одним из них, разобраться с другим будет гораздо проще.

Видео: SQL. 13. Внешний ключ и виды связей

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

Принцип создания связи многие-ко-многим

Длительное вступление могло несколько смутить и "напустить туману", но на самом деле принцип реализации связи остается прежним. Для того чтобы тип связи многие-ко-многим был воплощен на практике, не только в "Аксес", но и SQL, требуется изначально создать две базовые таблицы и одну - промежуточную. Аналогично дело обстоит и с ключами: основные сущности имеют главные поля, каждый из которых записывается в связующую таблицу. Что значит, что SQL-связь многие-ко-многим принципиально не отличается от "Аксеса".

Реализация связи

Для реализации связи многие-ко-многим в скриптах SQL используются внешние ключи (FOREIGN KEY) аналогичные исходным ключам в основных таблицах. Они записываются вместе со всеми полями при их создании и/или редактировании.

Роль связи многие-ко-многим

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

А это значит, что при составлении схемы данных (в "Аксесе") или написании скриптов (в "Оракл" или "ДиБиТу") будет присутствовать как минимум одна связь многие-ко-многим. Пример SQl, часто использующийся при обучении курса "Организации баз данных" - БД Кинга.

База данных Кинга

Эта учебная база данных представляет собой сведения о корпорации Кинга. Среди таблиц:

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

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

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

Важные нюансы

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