какие таблицы связаны отношением многие ко многим
Руководство по проектированию реляционных баз данных (7-9 часть из 15) [перевод]
Продолжение.
Предыдущие части: 1-3, 4-6
7. Связь один-ко-многим.
Я уже показал вам как данные из разных таблиц могут быть связаны при помощи связи по внешнему ключу. Вы видели как заказы связываются с клиентами путем помещения 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 в отдельную таблицу для ускорения поиска по родительской таблице). Или порой вы можете решить, что вы хотите разделить две сущности в разные таблицы в то время, как они все еще имеют связь один-к-одному. Но обычно наличие двух таблиц в связи один-к-одному считается дурной практикой.
Примеры связи один-к-одному.
Проект реляционной базы данных – это коллекция таблиц, которые перелинковываются (связываются) первичными и внешними ключами. Реляционная модель данных включает в себя ряд правил, которые помогают вам создать верные связи между таблицами. Эти правила называются “нормальными формами”. В следующих частях я покажу как нормализовать вашу базу данных.
Какой же вид связи вам нужен?
А если есть некие данные, которые могу быть присвоены любому человеку, то имеем дело со связью многие-ко-многим. Например, есть таблица со списком людей и мы хотим хранить информацию о том, какие страны посетил каждый человек. В данном случае имеется две сущности: люди и страны. Любой человек может посетить любое количество стран равно, как и любая страна может быть посещена любым человеком. Т.е., в данном случае, страна не является уникальными данными для конкретного человека и может использоваться повторно.
В таких случаях использование связи многие-ко-многим с использованием трех таблиц и с хранением общей информации централизованно очень удобно. Ведь если общие данные меняются, то для того, чтобы информация в базе данных соответствовала действительности достаточно подправить ее только в одном месте, т.к. хранится она только в одном месте (таблице), в остальных таблицах имеются лишь ссылки на нее.
А когда у вас есть набор уникальных данных, которые имеют отношение только друг к другу, то храните все в одной таблице. Ваш выбор – связь один-к-одному. Например, у вас есть небольшая коллекция автомобилей и вы хотите хранить информацию о них (цвет, марка, год выпуска и пр.).
Руководство по связи «многие ко многим»
В этой статье описаны средства моделирования данных, работающие с Power BI Desktop. В ней представлены три различных сценария моделирования связей «многие ко многим». Она также содержит рекомендации по их успешному проектированию в моделях.
Общие сведения о связях в моделях в этой статье не приводятся. Если у вас есть пробелы в знаниях о связях, их свойствах или настройке, рекомендуем сначала прочитать статью Связи модели в Power BI Desktop.
Вы также должны иметь представление о проектировании схемы типа «звезда». Дополнительные сведения см. в статье Общие сведения о схеме типа «звезда» и ее значении в Power BI.
По сути, есть три сценария применения связей «многие ко многим». Потребность в них возникает в следующих случаях:
Связывание измерений «многие ко многим»
Давайте рассмотрим первый сценарий применения связей «многие ко многим» на примере. В классическом сценарии связаны две сущности: клиенты банка и банковские счета. У каждого клиента может быть несколько счетов, а у каждого счета — несколько клиентов. Если со счетом связано несколько клиентов, они обычно называются держателями совместного счета.
Смоделировать эти сущности несложно. В одной таблице измерений хранятся счета, а в другой — клиенты. В обеих таблицах есть столбец идентификаторов, что присуще всем таблицам измерений. Для моделирования связи между двумя таблицами требуется третья таблица. Обычно она называется сопоставительной. В этом примере она предназначена для хранения одной строки для каждой связи клиента со счетом. Имейте в виду, что, когда такая таблица содержит только столбцы идентификаторов, она называется таблицей фактов без метрик.
Ниже приведена упрощенная схема этих трех таблиц.
Первая таблица называется Счет и содержит два столбца: ИдСчета и Счет. Вторая таблица называется СчетКлиент и содержит два столбца: ИдСчета и ИдКлиента. Третья таблица называется Клиент и содержит два столбца: ИдКлиента и Клиент. Между этими таблицами пока нет никаких связей.
Для связывания таблиц добавляются две связи «один ко многим». Ниже представлена обновленная схема связанных таблиц. Была добавлена таблица фактов с именем Транзакция. В ней записываются транзакции по счетам. Сопоставительная таблица и все столбцы идентификаторов скрыты.
Для демонстрации того, как работает распространение фильтра связей, на схеме модели показаны строки таблиц.
Строки таблиц невозможно отобразить на схеме модели в Power BI Desktop. В этой статье это было сделано для наглядности.
В приведенном ниже списке описываются строки каждой из четырех таблиц.
Давайте посмотрим, что происходит при запросе к модели.
Ниже представлены два визуальных элемента, которые суммируют значения из столбца Сумма в таблице Транзакция. В первом из них группировка производится по счетам, поэтому сумма значений из столбца Сумма представляет остаток на счете. Во втором группировка производится по клиентам, поэтому сумма значений из столбца Сумма представляет баланс клиента.
Первый визуальный элемент называется Остаток на счете и содержит два столбца: Счет и Сумма. В нем отображаются следующие результаты:
Второй визуальный элемент называется Баланс клиента и содержит два столбца: Клиент и Сумма. В нем отображаются следующие результаты:
Быстрого взгляда на строки таблицы и визуальный элемент Остаток на счете достаточно, чтобы понять, что как результаты для каждого счета, так и общая сумма верны. Связано это с тем, что группирование по каждому счету приводит к распространению фильтра в таблицу Транзакция для этого счета.
Однако в визуальном элементе Баланс клиента что-то не так. У каждого клиента в визуальном элементе Баланс клиента баланс совпадает с общей суммой. Такой результат был бы правильным, только если все счета были бы совместными для каждого клиента. Однако в данном примере это не так. Проблема связана с распространением фильтра. Путь его применения к таблице Транзакция где-то прерывается.
Проследите за направлениями фильтров связей от таблицы Клиент к таблице Транзакция. Очевидно, что связь между таблицами Счет и СчетКлиент имеет неправильное направление. В качестве направления фильтра для этой связи следует выбрать Оба.
Как и следовало ожидать, визуальный элемент Остаток на счете не изменился.
Визуальный элемент Баланс клиента, однако, теперь содержит другой результат:
Теперь результаты в визуальном элементе Баланс клиента верны. Проследите за направлениями фильтров самостоятельно и посмотрите, как вычисляются балансы клиентов. Также обратите внимание на то, что сумма в визуальном элементе означает сумму по всем клиентам.
Кто-то незнакомый со связями модели может прийти к выводу, что результат неправильный. У него может возникнуть вопрос: Почему суммарный баланс для клиентов Клиент-91 и Клиент-92 не равен 350 (75 + 275)?
Ответ на этот вопрос кроется в понимании связи «многие ко многим». Баланс каждого клиента может представлять собой сумму остатков на нескольких счетах, поэтому балансы клиентов не складываются.
Рекомендации по связыванию измерений «многие ко многим»
Если вам нужно реализовать связь «многие ко многим» между таблицами измерений, следуйте приведенным ниже рекомендациям.
Мы не рекомендуем связывать таблицы измерений «многие ко многим» напрямую. Такой подход требует настройки связи с кратностью «многие ко многим». В принципе, это можно реализовать, но в результате связанные столбцы будут содержать дублирующиеся значения. Однако согласно общепринятой методике в таблицах измерений должен быть столбец идентификаторов. В таблицах измерений столбец идентификаторов всегда должен находиться на стороне «один» связи.
Связывание фактов «многие ко многим»
Второй сценарий применения связи «многие ко многим» предполагает связывание двух таблиц фактов. Две таблицы фактов можно связать напрямую. Такой прием может быть полезен для быстрого изучения данных. Однако мы не рекомендуем применять этот подход. Причины будут раскрыты в следующем разделе.
Рассмотрим пример, в котором используются две таблицы фактов: Заказ и Выполнение. В таблице Заказ содержится по одной строке на каждую позицию заказа, а в таблице Выполнение может содержаться от нуля и более строк на позицию. Строки в таблице Заказ представляют заказы на продажу. Строки в таблице Выполнение представляют отгруженные элементы заказов. Связь «многие ко многим» связывает два столбца ИдЗаказа, причем фильтр распространяется только из таблицы Заказ (таблица Заказ фильтрует таблицу Выполнение).
Для поддержки хранения дублирующихся значений ИдЗаказа в обеих таблицах установлена кратность связи «многие ко многим». В таблице Заказ значения ИдЗаказа могут дублироваться по той причине, что заказ может состоять из нескольких позиций. В таблице Выполнение значения ИдЗаказа могут дублироваться по той причине, что заказ может состоять из нескольких позиций, которые могут включаться в разные отгрузки.
Теперь давайте посмотрим на строки таблиц. В таблице Выполнение обратите внимание на то, что позиции заказа могут включаться в разные отгрузки. (Отсутствие позиции означает, что заказ еще не выполнен.)
В приведенном ниже списке описываются строки каждой из двух таблиц.
Давайте посмотрим, что происходит при запросе к модели. Ниже приведен табличный визуальный элемент, в котором сравниваются заказанное и выполненное количество по столбцу ИдЗаказа таблицы Заказ.
Визуальный элемент содержит правильные результаты. Однако полезность модели ограничена — фильтрацию или группировку можно производить только по столбцу ИдЗаказа таблицы Заказ.
Рекомендации по связыванию фактов «многие ко многим»
Как правило, не рекомендуется связывать две таблицы фактов напрямую, используя кратность «многие ко многим». Главная причина в том, что модель будет недостаточно гибкой в плане фильтрации и группировки визуальных элементов в отчете. В приведенном примере фильтрация и группировка в визуальном элементе возможны только по столбцу ИдЗаказа таблицы Заказ. Еще одна причина связана с качеством данных. Если в данных есть проблемы с целостностью, некоторые строки могут быть опущены при выполнении запроса из-за самого характера ограниченной связи. Дополнительные сведения см. в разделе Вычисление связей из статьи «Создание связей модели в Power BI Desktop».
Вместо того чтобы связывать таблицы фактов напрямую, мы рекомендуем применять принципы проектирования на основе схемы типа «звезда». Для этого нужно добавить таблицы измерений. Затем они связываются с таблицами фактов связями «один ко многим». Такой подход к проектированию эффективнее, так как делает отчеты более гибкими. Он позволяет выполнять фильтрацию и группирование по любым столбцам измерений, а также суммировать любую связанную таблицу фактов.
Давайте улучшим решение.
Обратите внимание на указанные ниже изменения.
Дополнительные усилия, затраченные на реализацию принципов проектирования на основе схемы типа «звезда», дали указанные ниже преимущества.
Связывание фактов с более высокой детализацией
Этот сценарий связи «многие ко многим» существенно отличается от двух других, уже рассмотренных в этой статье.
Рассмотрим пример с четырьмя таблицами: Дата, Продажи, Товар и Цель. Дата и Товар — это таблицы измерений, каждая из которых связана с таблицей фактов Продажи связью «один ко многим». Пока структура вполне соответствует схеме типа «звезда». Однако еще предстоит связать таблицу Цель с другими таблицами.
Таблица Цель содержит три столбца: Категория, ЦелевоеКоличество и ЦелевойГод. В строках таблицы данные детализируются по году и категории товаров. Иными словами, целевые показатели, по которым оценивается эффективность продаж, задаются на каждый год для каждой категории товаров.
Так как в таблице Цель хранятся более обобщенные данные, чем в таблицах измерений, создать связь «один ко многим» невозможно. Однако это верно лишь для одного из отношений. Давайте посмотрим, как можно связать таблицу Цель с таблицами измерений.
Связывание периодов времени с более высокой детализацией
Между таблицами Дата и Цель должна быть связь «один ко многим». Это обусловлено тем, что значения в столбце ЦелевойГод представляют собой даты. В примере каждое значение столбца ЦелевойГод — это первая дата целевого года.
При хранении фактов с более высоким уровнем детализации, чем день, в качестве типа данных для столбца следует выбрать Дата (или Целое число при использовании ключей дат). В столбце должно храниться значение, представляющее первый день периода времени. Например, год записывается как 1 января года, а месяц — как первый день этого месяца.
Однако необходимо следить за тем, чтобы фильтры на уровне месяца или даты давали осмысленные результаты. Без специальной вычислительной логики визуальные элементы отчета могут сообщать о том, что целевыми датами буквально являются первые дни каждого года. Для всех остальных дней и месяцев, кроме января, суммирование целевого количества будет давать пустой результат.
Приведенный ниже матричный визуальный элемент показывает, что происходит, когда пользователь отчета производит детализацию от года к месяцам. Суммирование выполняется по столбцу ЦелевоеКоличество. (Для строк матрицы был включен параметр Показать элементы без данных.)
Чтобы избежать этого, рекомендуется контролировать суммирование фактов с помощью мер. Один из способов контроля суммирования — возврат пустых значений при запросе временных периодов более низкого уровня. Другой способ, для реализации которого требуются довольно сложные выражения DAX, заключается в пропорциональном распределении значений по временным периодам более низкого уровня.
Рассмотрим приведенное ниже определение меры, в котором используется функция DAX ISFILTERED. Оно возвращает значение, только если столбец Дата или Месяц не отфильтрован.
В приведенном ниже визуальном элементе теперь используется мера Целевое количество. Все значения целевого количества для отдельных месяцев в нем пусты.
Связывание фактов с более высокой детализацией (не дат)
При связывании столбца из таблицы измерений, не содержащего даты, с таблицей фактов (которая имеет более высокую степень детализации, чем таблица измерений), требуется другой подход.
Столбцы Категория (как в таблице Товар, так и в таблице Цель) содержат дублирующиеся значения. Поэтому сторона «один» для связи «один ко многим» отсутствует. В таком случае необходимо создать связь «многие ко многим». Фильтры связи должны распространяться в одном направлении: от таблицы измерений к таблице фактов.
Теперь давайте посмотрим на строки таблиц.
В таблице Цель четыре строки: две для каждого целевого года (2019 и 2020) и две категории («Одежда» и «Аксессуары»). В таблице Товар три товара. Два из них относятся к категории «Одежда», а один — к категории «Аксессуары». Один из товаров зеленый, а остальные два синие.
Группирование в табличном визуальном элементе по столбцу Категория из таблицы Товар дает следующий результат:
Этот визуальный элемент дает правильный результат. Давайте теперь посмотрим, что происходит, когда для группирования целевого количества используется столбец Цвет из таблицы Товар.
Данные в этом визуальном элементе представлены неточно. Что же происходит?
Применение фильтра к столбцу Цвет из таблицы Товар дает две строки. Одна из них относится к категории «Одежда», а другая — к категории «Аксессуары». Значения для двух этих категорий распространяются как фильтры в таблицу Цель. Иными словами, поскольку синий цвет имеют товары из двух категорий, эти категории используются для фильтрации целевых показателей.
Чтобы избежать этого, рекомендуется контролировать суммирование фактов с помощью мер, как было описано ранее.
Рассмотрим приведенное ниже определение меры. Обратите внимание на то, что все столбцы таблицы Товар ниже уровня категории проверяются на наличие фильтров.
В приведенном ниже табличном визуальном элементе теперь используется мера Целевое количество. Все значения целевого количества для цветов в нем пусты.
Итоговая схема модели выглядит следующим образом:
Рекомендации по связыванию фактов с более высокой детализацией
Если вам нужно связать таблицу измерений с таблицей фактов, причем в таблице фактов хранятся строки с более высоким уровнем детализации, чем у строк таблицы измерений, следуйте приведенным ниже рекомендациям.
Дальнейшие действия
Дополнительные сведения, связанные с темой этой статьи, см. в следующих ресурсах.