какие таблицы связаны отношением многие ко многим

Связи между таблицами базы данных

1. Введение

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

1.1. Для кого эта статья?

Эта статья будет полезна тем, кто хочет разобраться со связями между таблицами базы данных. В ней я постарался рассказать на понятном языке, что это такое. Для лучшего понимания темы, я чередую теоретический материал с практическими примерами, представленными в виде диаграммы и запроса, создающего нужные нам таблицы. Я использую СУБД Microsoft SQL Server и запросы пишу на T-SQL. Написанный мною код должен работать и на других СУБД, поскольку запросы являются универсальными и не используют специфических конструкций языка T-SQL.

1.2. Как вы можете применить эти знания?

2. Благодарности

Учтены были советы и критика авторов jobgemws, unfilled, firnind, Hamaruba.
Спасибо!

3.1. Как организовываются связи?

Связи создаются с помощью внешних ключей (foreign key).
Внешний ключ — это атрибут или набор атрибутов, которые ссылаются на primary key или unique другой таблицы. Другими словами, это что-то вроде указателя на строку другой таблицы.

3.2. Виды связей

4. Многие ко многим

Представим, что нам нужно написать БД, которая будет хранить работником IT-компании. При этом существует некий стандартный набор должностей. При этом:

4.1. Как построить такие таблицы?

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

На эту таблицу можно посмотреть с двух сторон:

4.2. Реализация

С помощью ограничения foreign key мы можем ссылаться на primary key или unique другой таблицы. В этом примере мы

4.3. Вывод

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

5. Один ко многим

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

Предположим, нам нужно реализовать некую БД, которая ведет учет данных о пользователях. У пользователя есть: имя, фамилия, возраст, номера телефонов. При этом у каждого пользователя может быть от одного и больше номеров телефонов (многие номера телефонов).

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

Другими словами, телефон принадлежит только одному пользователю. А пользователю могут принадлежать 1 и более телефонов (многие).

Как мы видим, это отношение один ко многим.

5.1. Как построить такие таблицы?

Данная таблица представляет три номера телефона. При этом номера телефона с id 1 и 2 принадлежат пользователю с id 5. А вот номер с id 3 принадлежит пользователю с id 17.
Заметка. Если бы у таблицы «Phones» было бы больше атрибутов, то мы смело бы их добавляли в эту таблицу.

5.2. Почему мы не делаем тут таблицу-посредника?

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

5.3. Реализация

6. Один к одному

Представим, что на работе вам дали задание написать БД для учета всех работников для HR. Начальник уверял, что компании нужно знать только об имени, возрасте и телефоне работника. Вы разработали такую БД и поместили в нее всю 1000 работников компании. И тут начальник говорит, что им зачем-то нужно знать о том, является ли работник инвалидом или нет. Наиболее простое, что приходит в голову — это добавить новый столбец типа bool в вашу таблицу. Но это слишком долго вписывать 1000 значений и ведь true вы будете вписывать намного реже, чем false (2% будут true, например).

Более простым решением будет создать новую таблицу, назовем ее «DisabledEmployee». Она будет выглядеть так:

Но это еще не связь один к одному. Дело в том, что в такую таблицу работник может быть вписан более одного раза, соответственно, мы получили отношение один ко многим: работник может быть несколько раз инвалидом. Нужно сделать так, чтобы работник мог быть вписан в таблицу только один раз, соответственно, мог быть инвалидом только один раз. Для этого нам нужно указать, что столбец EmployeeId может хранить только уникальные значения. Нам нужно просто наложить на столбец EmloyeeId ограничение unique. Это ограничение сообщает, что атрибут может принимать только уникальные значения.

Выполнив это мы получили связь один к одному.

Заметка. Обратите внимание на то, что мы могли также наложить на атрибут EmloyeeId ограничение primary key. Оно отличается от ограничения unique лишь тем, что не может принимать значения null.

6.1. Вывод

Можно сказать, что отношение один к одному — это разделение одной и той же таблицы на две.

6.2. Реализация

7. Обязательные и необязательные связи

Связи можно поделить на обязательные и необязательные.

7.1. Один ко многим

У одной биологической матери может быть много детей. У ребенка есть только одна биологическая мать.

А) У женщины необязательно есть свои дети. Соответственно, связь необязательна.
Б) У ребенка обязательно есть только одна биологическая мать – в таком случае, связь обязательна.

7.2. Один к одному

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

А) Наличие загранпаспорта необязательно – его может и не быть у гражданина. Это необязательная связь.
Б) У загранпаспорта обязательно есть только один владелец. В этом случае, это уже обязательная связь.

7.3. Многие ко многим

Человек может инвестировать в акции разных компаний (многих). Инвесторами какой-то компании являются определенные люди (многие).

А) Человек может вообще не инвестировать свои деньги в акции.
Б) Акции компании мог никто не купить.

8. Как читать диаграммы?

Выше я приводил диаграммы созданных нами таблиц. Но для того, чтобы их понимать, нужно знать, как их «читать». Разберемся в этом на примере диаграммы из пункта 5.3.

Мы видим отношение один ко многим. Одной персоне принадлежит много телефонов.

9. Итоги

10. Задачи

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

Источник

Руководство по проектированию реляционных баз данных (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 в отдельную таблицу для ускорения поиска по родительской таблице). Или порой вы можете решить, что вы хотите разделить две сущности в разные таблицы в то время, как они все еще имеют связь один-к-одному. Но обычно наличие двух таблиц в связи один-к-одному считается дурной практикой.

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

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

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

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

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

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

Источник

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

Проверьте, как это работает!

Что такое связь «многие-ко-многим»?

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

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

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

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

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

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

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

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

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

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

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

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

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

Выберите Сохранить .

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

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

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

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

Выберите переименованное поле.

На вкладке Поля в списке Тип данных выберите тип, как в соответствующем поле исходной таблицы, например Числовой или Короткий текст.

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

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

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

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

Откройте промежуточную таблицу в режиме конструктора.

Выберите обе строки с идентификаторами. (Если вы следовали предыдущим указаниям, это будут две первые строки.)

На вкладке Конструктор нажмите кнопку Ключевое поле.
Рядом с обоими полями будут отображаться значки ключей.

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

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

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

Источник

Руководство по связи «многие ко многим»

В этой статье описаны средства моделирования данных, работающие с 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) и две категории («Одежда» и «Аксессуары»). В таблице Товар три товара. Два из них относятся к категории «Одежда», а один — к категории «Аксессуары». Один из товаров зеленый, а остальные два синие.

Группирование в табличном визуальном элементе по столбцу Категория из таблицы Товар дает следующий результат:

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

Данные в этом визуальном элементе представлены неточно. Что же происходит?

Применение фильтра к столбцу Цвет из таблицы Товар дает две строки. Одна из них относится к категории «Одежда», а другая — к категории «Аксессуары». Значения для двух этих категорий распространяются как фильтры в таблицу Цель. Иными словами, поскольку синий цвет имеют товары из двух категорий, эти категории используются для фильтрации целевых показателей.

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

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

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

Итоговая схема модели выглядит следующим образом:

Рекомендации по связыванию фактов с более высокой детализацией

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

Дальнейшие действия

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

Источник