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

Связь многие ко многим в SQL: как установить отношения между таблицами

Связь многие ко многим (Many-to-Many) — это один из типов связей, применяемых в базе данных при проектировании сложных систем. Этот тип связи возникает, когда одна сущность может быть связана с произвольным количеством других сущностей, и наоборот. Например, каждая книга может быть написана несколькими авторами, и каждый автор может быть связан с несколькими книгами.

В SQL, связь многие ко многим обычно реализуется с использованием дополнительной таблицы, называемой таблицей-связью или таблицей-посредником. Эта таблица содержит внешние ключи, которые связывают две связанные таблицы. Например, для связи между таблицами «Книги» и «Авторы» можно создать таблицу «Книги_Авторы», которая содержит столбцы «ID_Книги» и «ID_Автора».

При создании запросов с использованием связи многие ко многим необходимо использовать операторы JOIN для объединения таблиц и выборки нужных данных. Например, чтобы получить всех авторов книги, необходимо объединить таблицы «Книги», «Книги_Авторы» и «Авторы» по соответствующим ключам.

Что такое связь многие ко многим в SQL и зачем она нужна?

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

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

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

Примеры использования связи многие ко многим в SQL:

Пример 1: Связь многие ко многим между таблицами «Студенты» и «Предметы»

Допустим, у нас есть две таблицы: «Студенты» и «Предметы». В таблице «Студенты» хранятся данные о студентах, а в таблице «Предметы» — данные о предметах. Один студент может изучать несколько предметов, а один предмет может изучаться несколькими студентами.

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

Пример структуры таблиц:

Таблица "Студенты": +------------+--------------+ | student_id | student_name | +------------+--------------+ | 1 | Алексей | | 2 | Иван | | 3 | Мария | +------------+--------------+ Таблица "Предметы": +-----------+-------------+ | subject_id| subject_name| +-----------+-------------+ | 1 | Математика| | 2 | Английский| | 3 | История | +-----------+-------------+ Таблица "Студенты-Предметы": +------------+-----------+ | student_id | subject_id| +------------+-----------+ | 1 | 1 | | 1 | 2 | | 2 | 1 | | 2 | 3 | | 3 | 2 | | 3 | 3 | +------------+-----------+

В данном примере студент с идентификатором 1 изучает предметы с идентификаторами 1 и 2, студент с идентификатором 2 изучает предметы с идентификаторами 1 и 3, а студент с идентификатором 3 изучает предметы с идентификаторами 2 и 3.

Смотрите также: Просто Python

Пример 2: Связь многие ко многим между таблицами «Авторы» и «Книги»

Представим, что у нас есть две таблицы: «Авторы» и «Книги». В таблице «Авторы» хранятся данные об авторах книг, а в таблице «Книги» — данные о самих книгах. Один автор может написать несколько книг, а одна книга может иметь нескольких авторов.

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

Пример структуры таблиц:

Таблица "Авторы": +-----------+-------------+ | author_id | author_name | +-----------+-------------+ | 1 | Иванов | | 2 | Петров | | 3 | Сидоров | +-----------+-------------+ Таблица "Книги": +----------+------------+ | book_id | book_name | +----------+------------+ | 1 | Роман | | 2 | Детектив | | 3 | Фантастика| +----------+------------+ Таблица "Авторы-Книги": +-----------+-----------+ | author_id | book_id | +-----------+-----------+ | 1 | 1 | | 1 | 2 | | 2 | 2 | | 3 | 1 | | 3 | 3 | +-----------+-----------+

В данном примере автор с идентификатором 1 написал книги с идентификаторами 1 и 2, автор с идентификатором 2 написал книгу с идентификатором 2, а автор с идентификатором 3 написал книги с идентификаторами 1 и 3.

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

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

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

Например, если у нас есть таблица «Студенты» и таблица «Курсы», и каждый студент может быть записан на несколько курсов, а каждый курс может иметь несколько студентов, мы создадим связующую таблицу «ЗаписьНаКурс» со столбцами «ID_Студента» и «ID_Курса». Оба столбца будут ссылаться на первичные ключи соответствующих таблиц.

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

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

Таблица «Студенты»	Таблица «ЗаписьНаКурс»	Таблица «Курсы»
ID	ID_Студента	ID
Имя	ID_Курса	Название
Фамилия
Возраст

В этом примере, чтобы получить список студентов и записанных на них курсов, мы использовали бы оператор JOIN следующим образом:

SELECT Студенты.Имя, Курсы.Название FROM Студенты JOIN ЗаписьНаКурс ON Студенты.ID = ЗаписьНаКурс.ID_Студента JOIN Курсы ON ЗаписьНаКурс.ID_Курса = Курсы.ID

Оператор JOIN объединяет таблицы «Студенты» и «ЗаписьНаКурс» на основе значения столбцов «ID» и «ID_Студента» соответственно, а затем объединяет результат со связанной таблицей «Курсы» на основе значения столбцов «ID_Курса» и «ID». Результатом будет список студентов с их курсами.

Смотрите также: Установка библиотеки Python без интернета: пошаговое руководство

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

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

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

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

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

Таблица «Студенты»	Таблица «Курсы»	Таблица «Студенты_Курсы»
id	id	id
имя	название	id_студента
возраст	преподаватель	id_курса

При использовании связи многие ко многим в SQL мы можем легко получить информацию о том, какие студенты записаны на определенный курс или какие курсы посещает определенный студент. Для этого мы будем использовать операторы JOIN и WHERE, чтобы объединить таблицы и выбрать нужные записи.

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

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

Проблемы, связанные с использованием связи многие ко многим в SQL

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

Дублирование данных: При использовании связи многие ко многим, многие записи могут иметь одинаковые связанные данные. Это может привести к дублированию данных и усложнить процесс обработки и анализа информации.
Сложная структура запросов: Использование связи многие ко многим требует сложной структуры запросов для извлечения необходимых данных. Это может сказаться на производительности системы и усложнить разработку и поддержку запросов.
Сложность поддержки целостности данных: Связь многие ко многим требует дополнительных механизмов для поддержки целостности данных, таких как триггеры или ограничения на уровне приложения. Это может увеличить сложность разработки и поддержки базы данных.
Проблемы с производительностью: Использование связи многие ко многим может привести к проблемам с производительностью базы данных, особенно при работе с большими объемами данных. Необходимо тщательно оценивать и настраивать индексы и оптимизировать запросы для обеспечения эффективной работы системы.
Проблема атомарности операций: В случае использования связи многие ко многим операции вставки, обновления и удаления данных могут стать более сложными и потребовать дополнительных механизмов для обеспечения атомарности операций.

Смотрите также: Установка модулей на Python: подробная инструкция

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

Вопрос-ответ:

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

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

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

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

Как выполнить запрос, используя связь многие ко многим в SQL?

Для выполнения запроса, используя связь многие ко многим в SQL, необходимо использовать операторы JOIN или INNER JOIN, связывая основные таблицы с промежуточной таблицей по соответствующим ключам. Это позволяет объединить данные из двух таблиц, чтобы получить нужный результат.

Какие преимущества и недостатки имеет связь многие ко многим в SQL?

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

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

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

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

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

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

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

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

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

ArticleId (идентификатор, первичный ключ)
BlogId (внешний ключ)
Title (название статьи)
Text (текст статьи)

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

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

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

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

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

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

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

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

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

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

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.

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

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

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

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

Общие сведения о связях модели не рассматриваются в этой статье. Если вы не знакомы с связями, их свойствами или настройкой, рекомендуем сначала прочитать связи модели в статье Power BI Desktop .

Важно также понимать схему звездочки. Дополнительные сведения см. в статье «Общие сведения о схеме звезды» и важности для Power BI.

На самом деле есть три сценария «многие ко многим». Они могут возникать при необходимости:

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

Power BI теперь изначально поддерживает связи «многие ко многим». Дополнительные сведения см. в статье «Применение связей «многие ко многим» в Power BI Desktop.

Связывание измерений «многие ко многим»

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

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

Ниже приведена упрощенная схема модели трех таблиц.

Diagram showing a model containing three tables. The design is described in the following paragraph.

Первая таблица называется Account, и она содержит два столбца: AccountID и Account. Вторая таблица называется AccountCustomer и содержит два столбца: AccountID и CustomerID. Третья таблица называется Customer и содержит два столбца: CustomerID и Customer. Связи не существуют между таблицами.

Для связи таблиц добавляются две связи «один ко многим». Ниже приведена обновленная схема модели связанных таблиц. Добавлена таблица типа фактов с именем Transaction . Он записывает транзакции учетной записи. Таблица бриджинга и все столбцы идентификаторов были скрыты.

Diagram showing that the model now contains four tables. One-to-many relationships have been added to relate all tables.

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

Невозможно отобразить строки таблицы на схеме модели Power BI Desktop. Это сделано в этой статье для поддержки обсуждения с четкими примерами.

Diagram showing that the model now reveals the table rows. The row details for the four tables are described in the following paragraph.

Сведения о строке для четырех таблиц описаны в следующем маркированном списке:

В таблице «Учетная запись» есть две строки:
- AccountID 1 предназначен для учетной записи-01
- AccountID 2 — для учетной записи-02
- CustomerID 91 предназначен для Customer-91
- CustomerID 92 предназначен для Customer-92
- AccountID 1 связан с CustomerID 91
- AccountID 1 связан с CustomerID 92
- AccountID 2 связан с CustomerID 92
- Дата 1 января 2019 года, AccountID 1, сумма 100
- Дата 2 февраля 2019 года, AccountID 2, сумма 200
- Дата 3 марта 2019 года, AccountID 1, сумма -25
Давайте посмотрим, что происходит при запросе модели.

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

Первый визуальный элемент называется «Баланс учетной записи» и имеет два столбца: «Учетная запись » и «Сумма«. Отображается следующий результат:
- Сумма баланса на счете-01 составляет 75
- Сумма баланса на счете 02 составляет 200
- Общий объем составляет 275
Второй визуальный элемент называется Customer Balance, и он имеет два столбца: Customer и Amount. Отображается следующий результат:
- Сумма баланса клиента-91 составляет 275
- Сумма баланса клиента-92 составляет 275
- Общий объем составляет 275
Краткий обзор строк таблицы и визуальный элемент «Баланс учетной записи » показывает, что результат правильный, для каждой учетной записи и суммы. Это связано с тем, что каждая группа учетных записей приводит к распространению фильтра в таблицу транзакций для этой учетной записи.

Однако что-то не отображается в визуальном элементе Customer Balance . Каждый клиент в визуальном элементе «Баланс клиента» имеет тот же баланс, что и общий баланс. Этот результат может быть правильным, только если каждый клиент был совместным владельцем каждой учетной записи. Это не так в этом примере. Проблема связана с распространением фильтров. Она не будет передаваться в таблицу транзакций .

Следуйте указаниям фильтра связей из таблицы Customer в таблицу транзакций . Должно быть очевидно, что связь между таблицей Account и AccountCustomer распространяется в неправильном направлении. Направление фильтра для этой связи должно иметь значение «Оба«.

Как ожидалось, не было изменений в визуальном элементе «Баланс учетной записи «.

Однако визуальные элементы Customer Balance теперь отображают следующий результат:
- Сумма баланса «Клиент-91» составляет 75
- Сумма баланса клиента-92 составляет 275
- Общий объем составляет 275
Визуальный элемент «Баланс клиентов» теперь отображает правильный результат. Следуйте указаниям фильтра для себя и узнайте, как были рассчитаны балансы клиентов. Кроме того, понять, что визуальный итог означает всех клиентов.

Кто-то не знакомы с связями модели может заключить, что результат неверный. Они могут попросить: Почему не общий баланс для Customer-91 и Customer-92 равны 350 (75 + 275)?

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

Руководство по использованию измерений «многие ко многим»

При наличии связи «многие ко многим» между таблицами типов измерений мы предоставляем следующие рекомендации:
- Добавление каждой связанной сущности «многие ко многим» в качестве таблицы модели, обеспечивая наличие уникального столбца идентификатора (идентификатора)
- Добавление таблицы бриджинга для хранения связанных сущностей
- Создание связей «один ко многим» между тремя таблицами
- Настройте двунаправленную связь, чтобы разрешить распространение фильтров продолжать работу с таблицами типа фактов
- Если у него нет отсутствующих значений идентификатора, задайте для свойства Is Nullable столбцов ИДЕНТИФИКАТОРа значение FALSE. Обновление данных завершится ошибкой, если отсутствующие значения являются источником
- Скрыть мостовую таблицу (если она не содержит дополнительные столбцы или меры, необходимые для создания отчетов)
- Скрытие столбцов идентификаторов, которые не подходят для создания отчетов (например, когда идентификаторы являются суррогатными ключами)
- Если имеет смысл оставить столбец идентификатора видимым, убедитесь, что он находится на слайде связи «один» — всегда скрывает боковой столбец «многие». Это обеспечивает лучшую производительность фильтра.
- Чтобы избежать путаницы или неправильной интерпретации, сообщите о объяснениях пользователям отчета— вы можете добавить описания с текстовыми полями или подсказками визуального заголовка.
Мы не рекомендуем напрямую связывать таблицы типа «многие ко многим». Этот подход к проектированию требует настройки связи с карта инальностью «многие ко многим». Концептуально его можно достичь, но подразумевается, что связанные столбцы будут содержать повторяющиеся значения. Однако это хорошо принятая практика проектирования, но в таблицах типов измерений есть столбец идентификатора. Таблицы типа измерения всегда должны использовать столбец идентификатора в качестве одной стороны связи.

Связь с фактами «многие ко многим»

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

Рассмотрим пример, который включает в себя две таблицы типа фактов: Порядок и выполнение. Таблица order содержит одну строку для каждой строки заказа, а таблица «Выполнение » может содержать ноль или несколько строк на строку заказа. Строки в таблице заказов представляют заказы на продажу. Строки в таблице «Выполнение » представляют элементы заказа, которые были отправлены. Связь «многие ко многим» относится к двум столбцам OrderID с распространением фильтров только из таблицы заказов (выполнение фильтров заказов).

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

Давайте рассмотрим строки таблицы. В таблице «Выполнение» обратите внимание, что линии заказов могут выполняться несколькими отгрузками. (Отсутствие строки заказа означает, что заказ еще не выполнен.)

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

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

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

Руководство по использованию фактов «многие ко многим»

Как правило, мы не рекомендуем использовать две таблицы типа фактов напрямую с использованием много ко многим карта inality. Основная причина заключается в том, что модель не обеспечивает гибкость в способах фильтрации визуальных элементов или групп. В примере визуальные элементы можно фильтровать или группировать по столбцу OrderID таблицы OrderID . Дополнительная причина связана с качеством данных. Если данные имеют проблемы целостности, возможно, некоторые строки могут быть опущены во время запроса из-за характера ограниченной связи. Дополнительные сведения см. в разделе «Отношения модели» в Power BI Desktop (оценка связей).

Вместо прямого связывания таблиц типа фактов рекомендуется внедрить принципы проектирования схемы Star. Это можно сделать, добавив таблицы типа измерения. Затем таблицы типа измерения относятся к таблицам типа фактов с помощью связей «один ко многим». Такой подход к проектированию является надежным, так как он предоставляет гибкие возможности создания отчетов. Он позволяет фильтровать или группировать с помощью любого столбца типа измерения и суммировать любую связанную таблицу типа фактов.

Рассмотрим лучшее решение.

Обратите внимание на следующие изменения дизайна:
- Теперь модель содержит четыре дополнительных таблицы: OrderLine, OrderDate, Product и FulfillmentDate
- Четыре дополнительные таблицы — это все таблицы типов измерений, а связи «один ко многим» относят эти таблицы к таблицам типа фактов.
- Таблица OrderLine содержит столбец OrderLineID, представляющий значение OrderID, умноженное на 100, а также значение OrderLine — уникальный идентификатор для каждой строки заказа.
- Таблицы Заказа и выполнения теперь содержат столбец OrderLineID, и они больше не содержат столбцы OrderID и OrderLine.
- Таблица «Выполнение» теперь содержит столбцы OrderDate и ProductID
- Таблица «Выполнение» относится только к таблице «Выполнение «
- Все столбцы уникальных идентификаторов скрыты
Использование принципов проектирования схемы звезды дает следующие преимущества:
- Визуальные элементы отчета могут фильтровать или группировать по любому видимому столбцу из таблиц типа измерения
- Визуальные элементы отчета могут суммировать любой видимый столбец из таблиц типа фактов
- Фильтры, применяемые к таблицам OrderLine, OrderDate или Product , будут распространяться в обе таблицы типа фактов.
- Все связи являются «один ко многим», и каждая связь является регулярной связью . Проблемы целостности данных не будут маскированы. Дополнительные сведения см. в разделе «Отношения модели» в Power BI Desktop (оценка связей).
Связь с более высокими зернистыми фактами

Этот сценарий «многие ко многим» очень отличается от других, уже описанных в этой статье.

Рассмотрим пример с четырьмя таблицами: Date, Sales, Product и Target. Таблицы типа «Дата » и «Продукт » — это таблицы типов измерений, а связи «один ко многим» относятся к таблице «Тип фактов продаж «. До сих пор он представляет хорошую схему звезды. Однако целевая таблица еще не связана с другими таблицами.

Таблица Target содержит три столбца: Category, TargetQuantity и TargetYear. Строки таблицы показывают степень детализации года и категории продуктов. Другими словами, целевые показатели, используемые для измерения производительности продаж, устанавливаются каждый год для каждой категории продуктов.

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

Связь с более высоким периодом времени зерна

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

При хранении фактов в более высокую степень детализации по сравнению с днем задайте для типа данных столбца значение Date (или Кто le число, если вы используете ключи даты). В столбце сохраните значение, представляющее первый день периода времени. Например, год записывается как 1 января года, а месячный период записывается в первый день этого месяца.

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

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

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

Рассмотрим следующее определение меры, использующее функцию DAX ISFILTERED . Он возвращает значение только в том случае, если столбцы Date или Month не фильтруются.
```
Target Quantity = IF( NOT ISFILTERED('Date'[Date]) && NOT ISFILTERED('Date'[Month]), SUM(Target[TargetQuantity]) ) 
```
В следующем визуальном элементе матрицы теперь используется мера «Целевое количество «. В нем показано, что все ежемесячные целевые объемы пусты.

Связь с более высоким уровнем зерна (не дата)

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

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

Давайте рассмотрим строки таблицы.

В таблице Target есть четыре строки: две строки для каждого целевого года (2019 и 2020) и две категории (одежда и аксессуары). В таблице Product есть три продукта. Два принадлежат к категории одежды, и один принадлежит категории аксессуаров. Один из цветов одежды зеленый, и остальные два являются голубыми.

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

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

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

Фильтр по столбцу Color из таблицы Product приводит к двум строкам. Одна из строк относится к категории «Одежда», а другая — для категории «Аксессуары». Эти два значения категорий распространяются в качестве фильтров в целевую таблицу. Другими словами, поскольку цвет синим цветом используется продуктами из двух категорий, эти категории используются для фильтрации целевых объектов.

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

Рассмотрим следующее определение меры. Обратите внимание, что все столбцы таблицы Product , находящиеся под уровнем категории, проверяются для фильтров.
```
Target Quantity = IF( NOT ISFILTERED('Product'[ProductID]) && NOT ISFILTERED('Product'[Product]) && NOT ISFILTERED('Product'[Color]), SUM(Target[TargetQuantity]) ) 
```
В следующем визуальном элементе таблицы теперь используется мера «Целевое количество «. В нем показано, что все целевые значения цвета пусты.

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

Руководство по более высокому зерню

Если необходимо связать таблицу типа измерения с таблицей типа фактов, а таблица типа фактов хранит строки на более высоком уровне, чем строки таблицы типа измерения, мы предоставляем следующее руководство.
- Для более высоких дат фактов зерна:
  - В таблице типа фактов сохраните первую дату периода времени.
  - Создание связи «один ко многим» между таблицей дат и таблицей типа фактов
  - Создание связи «многие ко многим» между таблицей типа измерения и таблицей фактов
  - Сводка элементов управления с помощью логики мер — возвращает значение BLANK, когда столбцы типа измерения нижнего уровня используются для фильтрации или группировки
  - Скрыть сводные столбцы таблицы типа фактов. Таким образом, можно использовать только меры для сводки таблицы типа фактов.
  Связанный контент
  
  Дополнительные сведения, связанные с этой статьей, проверка следующие ресурсы:
  - Связи модели в Power BI Desktop
  - Общие сведения о схеме звезд и важности Power BI
  - Руководство по устранению неполадок связи
  - Вопросы? Задайте их в сообществе Power BI.
  - Есть предложения? Участие в разработке идей по улучшению Power BI
  Создание связей типа «многие-ко-многим»
  
  Связи типа «многие-ко-многим» используются между таблицами чаще всего. С их помощью можно узнавать важные сведения, например, с какими клиентами связывались ваши менеджеры по продажам и какие продукты входили в заказы.
  
  Связь «многие-ко-многим» предполагает возможность связи одного или нескольких элементов из одной таблицы с одним или несколькими элементами из другой таблицы. Примеры:
  - В таблице «Заказы» указаны заказы, сделанные разными клиентами из таблицы «Клиенты». Каждый клиент мог сделать несколько заказов.
  - В таблице «Продукты» указаны продаваемые товары, каждый из которых может фигурировать в нескольких заказах из таблицы «Заказы».
  - Каждый продукт может входить в один заказ как в одном, так и в нескольких экземплярах.
  Например, в заказ Арины Ивановой № 1012 могут входить продукты № 12 и 15, а также пять продуктов № 30.
  
  Создание связи «многие-ко-многим»
  
  Вы создаете отношения «многие ко многим» иначе, чем «один к одному» или «один ко многим». Для этих связей необходимо просто соединить соответствующие поля с помощью строки. Чтобы создать связи «многие ко многим», необходимо создать новую таблицу для подключения двух других. Эта новая таблица называется промежуточной таблицей (или иногда таблицей связывания или соединения ).
  
  В рассмотренном ранее примере создавалась таблица «Сведения о заказах» с записями, в которых для каждого товара в нужном порядке указывались номер заказа из таблицы «Заказы» и код продукта из таблицы «Продукты». Первичный ключ для этой таблицы создавался путем объединения ключей из двух других таблиц.
  
  Ниже рассмотрим пример, когда в заказ Арины Ивановой № 1012 входят продукты № 12, 15 и 30. Это значит, что записи в таблице «Сведения о заказах» выглядят следующим образом:
  
  Арина заказала по одному продукту № 12 и 15, а также пять продуктов № 30. Создать другие строки с номером заказа 1012 и кодом продукта 30 нельзя, потому что поля «Номер заказа» и «Код продукта» вместе составляют первичный ключ, а первичные ключи должны быть уникальными. Вместо этого можно добавить в таблицу «Сведения о заказах» поле «Количество».
  
  Создание промежуточной таблицы
  1. Выберите Создание >Таблица.
  2. Выберите Сохранить
  Создание полей в промежуточной таблице
  
  В качестве первого столбца таблицы Access автоматически добавляет поле идентификатора. Измените имя этого поля на идентификатор вашей первой таблицы в связи «многие-ко-многим». Например, если первая таблица называется «Заказы», поле «Код» в ней переименовано в «Номер заказа», и его первичный ключ — число, измените имя поля «Код» в новой таблице на «Номер заказа», а в качестве типа данных выберите Числовой.
  1. В режиме таблицы выберите заголовок столбца Код и введите новое имя поля.
  2. Выберите переименованное поле.
  3. На вкладке Поля таблицы в разделе Тип данных выберите тип данных, соответствующий полю в исходной таблице, например Число или Короткий текст.
  4. Щелкните надпись Щелкните для добавления и выберите тип данных, соответствующий первичному ключу во второй таблице. В заголовке столбца введите имя поля первичного ключа из второй таблицы, например «Код продукта».
  5. Если вам требуется отслеживать другую информацию об этих записях, например количество товаров, создайте дополнительные поля.
  Объединение полей для создания первичного ключа
  
  Теперь, когда у вас есть поля с идентификаторами двух таблиц, между которыми вы хотите создать связь, в промежуточной таблице следует создать первичный ключ на основе этих идентификаторов.
  1. Откройте промежуточную таблицу в режиме конструктора.
  2. Выберите обе строки с идентификаторами. (Если вы следовали предыдущим указаниям, это будут две первые строки.)
  3. Выберите Конструктор таблиц >первичный ключ.
    Значки клавиш отображаются рядом с обоими полями идентификаторов.
  Соединение трех таблиц для создания связи «многие-ко-многим»
  
  Чтобы завершить создание связи «многие-ко-многим», создайте связь «один-ко-многим» между полем первичного ключа в каждой таблице и соответствующим полем в промежуточной таблице. Инструкции см. в статье Начало работы со связями между таблицами.
  
  После этого связи должны выглядеть следующим образом:
  Похожие публикации: