Почему нужно избавляться от связи многие ко многим
Перейти к содержимому

Почему нужно избавляться от связи многие ко многим

  • автор:

Связь многие-ко-многим, OLAP и MS SQL Server Analysis Services

Для начала немного о том, как я к этому подошел. Начальство сказало, что на имеющейся базе данных неплохо бы развернуть какую-то аналитику. Проведя небольшой гуглопоиск выяснилось, что отлично подойдет технология OLAP. А так как на сервере компании развернут MS SQL Server , то еще более отлично подойдет тамошний компонент Analysis Services.

Радостно потирая руки, я схватил майкрософтовский учебник, который идет в комплекте с SQL Server’ом. А через два дня я был уверен, что мне все по плечу. Но, не тут то было… В имеющейся базе данных большинство связей между таблицами оказались связями многие-ко-многим, что поначалу не предвещало никаких осложнений. Но на практике выясняется, что без дополнительных весьма осмысленных маханий руками не обойтись, т.к. в противном случае кубик просто выдает некорректную информацию.

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

Собственно в чем проблема. Если читающий знает что стандартными схемами для Olap являются схемы «Звезда» и «Снежинка». А что если нас кругом обложили связями многие-ко-многим?

Самый простой вариант: уйти от связи многие-ко-многим с помощью представлений — это положительно скажется на скорости обработки запросов. Невозможно уйти? Давайте разбираться.

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

Пример надуманный, но все же видна проблема: как связать аж через две связи M2M будущее измерение с таблицей фактов? Все просто, мы подскажем SSAS, куда нужно смотреть.
Выполнив предварительные действия по созданию измерений (Types, Dates, Categories, Persons, Accounts), пытаемся создать куб на мере Sales (количество строк). По умолчанию Visual Studio предложит нам только три меры (Types, Accounts, Dates) — ведь только они связаны напрямую с нашей мерой. Создав куб, руками добавляем оставшиеся две меры. Плюс создаем еще две вспомогательные меры внутри куба, которые будут отвечать за обработку связи M2M: Bridge Accounts Persons и Bridge Persons Categories (обе — количество строк в понятно каких таблицах).
Таким образом, будем иметь следующую картинку:

Видим много серых боксов и то, что Visual Studio уже обработала одну связь M2M: между вспомогательной мерой Bridge Persons Categories и измерением Accounts. И это хорошо, но не достаточно. Если прямо сейчас попросить наш куб что-нибудь выдать, мы не получим ничего хорошего. Нет, измерения Dates и Accounts все сделают правильно, но вот с Persons и Categories увы.

Чтобы исправить сие недоразумение подскажем SSAS, где искать информацию для обработки наших запросов. Для этого заполним те серые боксы на вкладке «Использование измерений» следующим образом: щелкаем на серые бокс-> многоточие-> тип связи выбираем «Многие ко многим»-> Выбираем промежуточную группу мер, как написано на картинке:

Вуаля! Все работает. Можно убедиться на очередной картинке:

В чем магия? Мы указали SSAS, где и как искать информацию о связях. Обратите внимание: невозможно, например, правильно заполнить пересечение «измерение Categories и мера Sales» до того как вы правильно заполните «измерение Categories и мера Bridge Account Persons». В вариантах выпадает только Bridge Persons Categories, т.к. иного пути Visual Studio просто не знает. Но ведь вспомогательные меры — тоже меры. И пути для них надо указывать так же, как и для обычных (целевых) мер. По мере заполнения таблицы Visual Studio набирается знаний и предлагает больше вариантов.

Теперь сформулируем мнемоническое правило о том, как надо заполнять таблицу связей: «Между целевой мерой и целевым измерением выбирай ближайшую таблицу мер к целевой мере». Таким образом, и получалось, что для измерений Type и Date во второй столбце будет полноценная, интересная мера Sales, а в третьем вспомогательная Bridge Accounts Persons. Аналогично и для измерения Categories.

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

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

Ссылки по теме

  • Купить Microsoft: Серверные программы
  • Купить Microsoft: Windows Server Серверные операционные системы
  • Купить Microsoft: Электронные лицензии
  • Запись на Курсы обучения по продуктам компании Microsoft
Рекомендовать Обсудить материал Написать редактору Распечатать Дата публикации: 28.11.2013

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

Браузер не поддерживает видео. Установите Microsoft Silverlight, Adobe Flash Player или Internet Explorer 9.

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

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

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

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

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

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

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

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

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

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

Сохранение

  1. Выберите Создание >Таблица.
  2. Выберите Сохранить

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

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

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

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

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

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

  1. Откройте промежуточную таблицу в режиме конструктора.
  2. Выберите обе строки с идентификаторами. (Если вы следовали предыдущим указаниям, это будут две первые строки.)
  3. Выберите Конструктор таблиц >первичный ключ.
    Значки клавиш отображаются рядом с обоими полями идентификаторов.

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

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

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

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

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

Отслеживать
задан 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

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

Как уйти от связи многие ко многим? Разделить таблицу БД на 2

Author24 — интернет-сервис помощи студентам

Есть таблицы:
Экзамены
-(PK)№ Экзамена
-Дата
-Студент
-Оценка
-Дисциплина (SK к Дисциплины.Название)
Дисциплины
-(PK)Название
-Преподаватель (SK к Преподаватели.Табельный номер)
Преподаватели
-(PK) Табельный номер
-Фамилия
-Имя
-Отчество
-Звание
-Кафедра

Как видно, в таблице Дисциплины нет первичного ключа, т.к. одну и ту же дисциплину могут вести 2 разных препода, и один и тот же препод может вести 2 разных дисциплины.
Прошу помочь и разделить таблицу Дисциплины на 2 разных таблицы так, чтобы PK сохранились как я указал.
Я думал над тем как это сделать и пришел лишь к идее сделать таблицу
Дисциплины
-(PK) ID дисциплины
-Название дисциплины (SK к Квалификация преподавателей.Дисциплина)
Квалификация преподавателей
-(PK) Дисциплина
— Преподаватели (SK к Преподаватели.Табельный номер (см. выше))
Как видите проблема осталась P.S. Вписывать множественные значения в одну строку не предлагать, так нельзя по заданию.

Лучшие ответы ( 1 )
94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
Ответы с готовыми решениями:

Как автоматически создавать третью (связующую) таблицу при связи многие ко многим ?
Например, есть 3 таблицы Subject , Teacher, Teacher_Subject. Teacher_Subject — связующая таблица.

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

Редактировать записи при связи многие-ко-многим
Добрый день. Очень нужна помощь. Есть три таблицы: Пациенты, Болезни и Заболеваемость.

При связи многие ко многим можно ли автоматически создавать третью (связующую) таблицу?
Вот 3 таблицы Subject , Teacher, Teacher_Subject. Teacher_Subject — связующая таблица, с двумя.

26803 / 14482 / 3192
Регистрация: 28.04.2012
Сообщений: 15,782

Ну, во-первых надо убрать препода из Дисциплины. Он там просто не должен быть. Если надо как-то увязать преподавателя и дисциплину, то для этой цели как раз и нужна таблица, задающая связь «многие-ко-многим» между преподавателями и предметами. Назовем ее для определенности ПреподыПредметы .

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *