Как можно представить наследование в базе данных?

@Билл Karwin описывает три модели наследования в его SQL Antipatterns книга, предлагая решения для SQL Объект-Атрибут-Значение антиобразец. Это краткий обзор:

наследование одной таблицы (aka таблица на наследование иерархии):

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

+------+---------------------+----------+----------------+------------------+
| id   | date_issued         | type     | vehicle_reg_no | property_address |
+------+---------------------+----------+----------------+------------------+
|    1 | 2010-08-20 12:00:00 | MOTOR    | 01-A-04004     | NULL             |
|    2 | 2010-08-20 13:00:00 | MOTOR    | 02-B-01010     | NULL             |
|    3 | 2010-08-20 14:00:00 | PROPERTY | NULL           | Oxford Street    |
|    4 | 2010-08-20 15:00:00 | MOTOR    | 03-C-02020     | NULL             |
+------+---------------------+----------+----------------+------------------+

\------ COMMON FIELDS -------/          \----- SUBTYPE SPECIFIC FIELDS -----/

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

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

• база данных не сможет применить, какие атрибуты применяются, а какие нет, поскольку нет метаданных, чтобы определить, какие атрибуты принадлежат к каким подтипам.

• вы также не можете принудительно NOT NULL атрибуты подтипа, которые должны быть обязательными. Вы должны были бы справиться с этим в вашем приложении, которое в целом не является идеальный.

Наследование Конкретной Таблицы:

--// Table: policies_motor
+------+---------------------+----------------+
| id   | date_issued         | vehicle_reg_no |
+------+---------------------+----------------+
|    1 | 2010-08-20 12:00:00 | 01-A-04004     |
|    2 | 2010-08-20 13:00:00 | 02-B-01010     |
|    3 | 2010-08-20 15:00:00 | 03-C-02020     |
+------+---------------------+----------------+

--// Table: policies_property    
+------+---------------------+------------------+
| id   | date_issued         | property_address |
+------+---------------------+------------------+
|    1 | 2010-08-20 14:00:00 | Oxford Street    |   
+------+---------------------+------------------+

эта конструкция в основном решит проблемы, выявленные для метода одной таблицы:

• обязательные атрибуты теперь могут быть применены с NOT NULL.

• добавление нового подтип требует добавления новой таблицы вместо добавления столбцов к существующей.

• также нет риска, что для определенного подтипа будет установлен неподходящий атрибут, например vehicle_reg_no поле для политики имущества.

• нет необходимости в type атрибут, как в методе одной таблицы. Тип теперь определяется метаданными: имя таблицы.

однако эта модель также поставляется с несколько недостатков:

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

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

• поиск всех политик независимо от подтипа становится затруднительным, и потребуется куча UNION s.

вот как вы должны были бы запросить все политики независимо от типа:

SELECT     date_issued, other_common_fields, 'MOTOR' AS type
FROM       policies_motor
UNION ALL
SELECT     date_issued, other_common_fields, 'PROPERTY' AS type
FROM       policies_property;

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

наследование таблиц классов (aka Table Per Type Inheritance):

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

CREATE TABLE policies (
   policy_id          int,
   date_issued        datetime,

   -- // other common attributes ...
);

CREATE TABLE policy_motor (
    policy_id         int,
    vehicle_reg_no    varchar(20),

   -- // other attributes specific to motor insurance ...

   FOREIGN KEY (policy_id) REFERENCES policies (policy_id)
);

CREATE TABLE policy_property (
    policy_id         int,
    property_address  varchar(20),

   -- // other attributes specific to property insurance ...

   FOREIGN KEY (policy_id) REFERENCES policies (policy_id)
);

это решение решает проблемы, выявленные в двух других проектах:

• обязательные атрибуты могут быть применены с помощью NOT NULL.

• добавление нового подтипа требует добавления новой таблицы вместо добавления столбцов к существующему.

• нет риска, что неподходящий атрибут установлен для определенного подтипа.

• не нужно .

• теперь общие атрибуты больше не смешиваются с конкретными атрибутами подтипа.

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

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

• поиск всех политик независимо от подтипа теперь становится очень легко: нет UNION s необходимо-просто a SELECT * FROM policies.

я считаю подход к таблице классов наиболее подходящим в большинстве ситуаций.

---

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