Как соотношения супер- и подтипов на диаграммах ER представлены в виде таблиц?

Question

Я учусь интерпретировать диаграммы отношений сущностей в операторы SQL DDL, и меня смущают различия в обозначениях. Рассмотрим непересекающиеся отношения, как на следующей диаграмме:

Будет ли это представлено как:

1. Таблицы транспортных средств, 2WD и 4WD (2WD и 4WD будут указывать на PK транспортного средства); или же
2. ТОЛЬКО таблицы 2WD и 4WD (и НЕТ таблицы транспортных средств), которые будут дублировать любые атрибуты транспортного средства?

Я думаю, что это другие способы написания отношений:

Я ищу четкое объяснение разницы в том, какие таблицы вы бы в конечном итоге для каждой диаграммы.

Answer 1

Обозначение ER

Есть несколько обозначений ER. Я не знаком с тем, который вы используете, но достаточно ясно, что вы пытаетесь представить подтип (иначе говоря, наследование, категория, подкласс, иерархия обобщения...). Это родственная родственница наследования ООП.

Делая подтипы, вы обычно заботитесь о следующих дизайнерских решениях:

• Абстрактное или конкретное: может ли быть создан родитель? В вашем примере: может ли Vehicle существуют, не будучи 2WD или же 4WD?¹
• Инклюзивное или исключительное: можно ли создать несколько экземпляров для одного и того же родителя? В вашем примере, может Vehicle быть обоими 2WD а также 4WD?²
• Полный или неполный: Ожидаете ли вы, что в будущем будет добавлено больше детей? В вашем примере, вы ожидаете Bike или Plane (и т.д...) можно было бы позже добавить в модель базы данных?

В нотации "Информационная инженерия" проводится различие между инклюзивными и исключительными отношениями подтипов. Нотация IDEF1X, с другой стороны, не (прямо) распознает эту разницу, но она различает полный и неполный подтип (который IE не делает).

Следующая диаграмма из Руководства по методам ERwin (Глава 5, Связи подтипов) иллюстрирует разницу:

Ни IE, ни IDEF1X напрямую не позволяют указывать абстрактный или конкретный родительский элемент.

Физическое Представление

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

1. Поместите все классы в одну таблицу и оставьте дочерние поля пустыми. После этого вы можете выполнить ПРОВЕРКУ, чтобы убедиться в правильности подмножества полей, отличных от NULL.
   • Плюсы: нет присоединения, поэтому некоторые запросы могут принести пользу. Может применять ключи родительского уровня (например, если вы хотите избежать 2WD а также 4WD транспортные средства, имеющие одинаковый идентификатор). Может легко обеспечить инклюзивные и исключительные дети и абстрактные против конкретного родителя (просто изменив ПРОВЕРКУ).
   • Минусы: некоторые запросы могут быть медленнее, поскольку они должны отфильтровывать "неинтересных" детей. В зависимости от вашей СУБД, специфичные для ребенка ограничения могут быть проблематичными. Многие NULL могут тратить хранилище. Менее подходит для неполного подтипирования - добавление нового дочернего элемента требует изменения существующей таблицы, что может быть проблематично в производственной среде.
2. Поместите все дочерние элементы в отдельные таблицы, но у вас нет таблицы для родителя (вместо этого повторите поля и ограничения родителя для всех дочерних элементов). Обладает большинством характеристик (3), избегая при этом СОЕДИНЕНИЯ, ценой более низкой ремонтопригодности (из-за всех этих повторений полей и ограничений) и неспособности принудительно применять ключи родительского уровня или представлять конкретного родителя.
3. Положите родителей и детей в отдельные таблицы.
   • Плюсы: чистый. Никакие поля / ограничения не должны быть искусственно повторены. Применяет ключи родительского уровня и легко добавляет ограничения для детей. Подходит для неполного подтипа (относительно легко добавить больше дочерних таблиц). Определенные запросы могут принести пользу, только взглянув на "интересные" дочерние таблицы.
   • Минусы: некоторые запросы могут быть тяжелыми. Может быть трудно применить принудительно включенные и исключительные дочерние элементы и абстрактные и конкретные родительские (они могут быть принудительно применены декларативно, если СУБД поддерживает циклические и отложенные внешние ключи, но применение их на уровне приложения обычно считается меньшим злом).

Как видите, ситуация далеко не идеальная - вам нужно идти на компромиссы независимо от того, какой подход вы выберете. Подход (3), вероятно, должен быть вашей отправной точкой, и выбрать одну из альтернатив только в том случае, если для этого есть веская причина.

---

¹ Я предполагаю, что это то, что обозначает толщину линии на ваших диаграммах.

² Я предполагаю, что это то, что означает наличие или отсутствие "непересекающихся" в ваших диаграммах.

Answer 2

То, что сказали другие респонденты, плюс следующее, что входит в первичные ключи для таблиц подклассов.

Ваш случай выглядит как пример шаблона проектирования, известного как "Специализация обобщения", или сокращенно Gen-Spec. Вопрос о том, как моделировать gen-spec с использованием таблиц базы данных, постоянно возникает в SO.

Если бы вы моделировали gen-spec в OOPL, таком как Java, вы бы использовали возможность наследования подкласса, чтобы позаботиться о деталях для вас. Вы просто определили бы класс, который позаботится об обобщенных объектах, а затем определите коллекцию подклассов, по одному для каждого типа специализированного объекта. Каждый подкласс расширил бы обобщенный класс. Это легко и просто.

К сожалению, реляционная модель данных не имеет встроенного наследования подклассов, и, насколько мне известно, системы баз данных SQL не предлагают никаких таких возможностей. Но тебе не повезло. Вы можете спроектировать свои таблицы для моделирования gen-spec таким образом, чтобы это соответствовало структуре классов ООП. Затем вам необходимо организовать собственный механизм наследования при добавлении новых элементов в обобщенный класс. Подробности следуют.

Структура класса довольно проста, с одной таблицей для класса gen и одной таблицей для каждого подкласса спецификации. Вот хорошая иллюстрация с сайта Мартина Фаулера. Наследование таблицы классов. Обратите внимание, что на этой диаграмме Cricketer является одновременно подклассом и суперклассом. Вы должны выбрать, какие атрибуты идут в какие таблицы. Диаграмма показывает один примерный атрибут в каждой таблице.

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

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

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

Answer 3

Обычно, когда вы делаете отношение Супер-тип / Подтип в дизайне вашей базы данных, вам нужно создать отдельную таблицу для вашего Типа общего объекта (Супер-тип) и отдельные таблицы для версий / версий вашего Специализированного объекта (Подтип) несвязно или нет. В вашем случае вам нужно будет создать таблицу для VEHICLE и первичный ключ, а также некоторые атрибуты, которые являются общими или общими для всех подтипов. Затем вам нужно будет создать отдельные таблицы для 2WD и 4WD вместе с атрибутами, специфичными только для этих таблиц. Например

тогда вы можете запросить эти таблицы с помощью SQL-соединений

Answer 4

There is not always only one way to implement any particular data model. Often, there is a transformation that occurs when you move from a logical model to a physical model.

Стандартный SQL не имеет четкого способа применения ограничений для непересекающихся подтипов.

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

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

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