Наследование и Полиморфизм в реляционных БД, SQL

Question

У нас есть родительский класс A, классы-наследники B со своими полями, и классы C, которые ссылаются на A или одного из B; теоретически B могут иметь своих детей. Упрощённо в виде диаграммы классов это выглядит так:

В любом ООП ЯП такой полиморфизм легко реализовать, например в C++:

#include <iostream>

class A {
public:
    int a_value;
    A (int a) {a_value = a;}
};
class B : public A {
public:
    int b_value;
    B (int a, int b) : A(a) {b_value = b;}
};
class C {
public:
    A* target;
    int c_value;
    C (A* a, int c) {target = a; c_value = c;}
};

int main() {
    A a = A(3);
    B b = B(14, 15);
    C ca = C(&a, 92);
    C cb = C(&b, 65);
    std::cout << ca.target->a_value;
    std::cout << cb.target->a_value;
    std::cout << cb.target->b_value;
    return 0;
}

Python:

class A:
    def __init__(self, a: int):
        self.a_value = a

class B(A):
    def __init__(self, a: int, b: int):
        super().__init__(a)
        self.b_value = b

class C:
    def __init__(self, a: A, c: int):
        self.target = a
        self.c_value = c

if __name__ == "__main__":
    a = A(3)
    b = B(14, 15)
    ca = C(a, 92)
    cb = C(b, 65)
    print(ca.target.a_value)
    print(cb.target.a_value)
    print(cb.target.b_value)

Но как сотворить подобное в реляционных БД? Желательно с поддержкой ORM вроде Django, SQLAlchemy.

_{Вопрос и ответы созданы с целью стать гайдом по данной теме, любые предложения по улучшению приветствуются :)}

Answer 1

1. Подходы к наследованию

Для начала разберёмся с вариантами реализации наследования в рСУБД.

1.1. TPC: Table-Per-Concrete

Каждый класс-наследник имеет собственную независимую таблицу, в которой продублированы все поля классов-родителей.

CREATE TABLE A (
    a_id          int primary key,
    a_value       int
);

CREATE TABLE B1 (
    b_id         int primary key,
    a_value      int,
    b1_value     int
);

CREATE TABLE B2 (
    b_id         int primary key,
    a_value      int,
    b2_value     varchar(20)
);

Такой вариант поддерживается в SQLAlchemy как Concrete Table Inheritance

Описанный подход особенно уместен, когда родительский класс является абстрактным и таблицу для него создавать не нужно, только для наследников. Этот частный случай поддерживается в Django ORM как Abstract base classes и в SQLAlchemy как Abstract Concrete Classes.

1.2. TPT: Table-Per-Type

Наследование через композицию (в духе Composition over inheritance), где дети ссылаются на родителя, и в идеале имеют одинаковый Primary Key. В этом случае данные одного экземпляра класса будут физически разделены в разных таблицах, и извлечение всех полей объекта потребует JOIN-запроса.

CREATE TABLE A (
    id            int primary key,
    a_value       int
);

CREATE TABLE B1 (
    id           int primary key,
    b1_value     int,
    FOREIGN KEY (id) REFERENCES B (id)
);

CREATE TABLE B2 (
    id           int primary key,
    b2_value     varchar(20),
    FOREIGN KEY (id) REFERENCES B (id)
);

Такой вариант поддерживается в Django ORM как Multi-table inheritance и в SQLAlchemy как Joined Table Inheritance.

1.3. TPH: Table-Per-Hierarchy

Таблица на всю иерархию, т.е, одна таблица на все классы A, B и прочих возможных наследников. При этом, поля, не релевантные для типа текущей строчки/объекта, будут иметь значения NULL, а общие поля верхнего родителя можно пометить как NOT NULL. Этот вариант уместен, когда классов в иерархии или их уникальных полей не много. Опционально можно завести таблицу с описанием подтипов чтобы обеспечить их валидность.

CREATE TABLE ObjType (
    type_id       int primary key,
    type_name     varchar(20)
);

insert into ObjType (type_id, type_name) values
    (1, 'A'), (2, 'B1'), (3, 'B2');

CREATE TABLE Obj (
    id            int primary key,
    type_id       int not null,
    a_value       int not null,
    b1_value      int,
    b2_value      varchar(20),
    FOREIGN KEY (type_id) REFERENCES ObjType (type_id)
);

Такой вариант поддерживается SQLAlchemy как Single Table Inheritance.

2. Композиция и Полиморфизм

К сожалению, на уровне СУБД полноценно полиморфизм не поддерживается, но есть варианты по его реализации, в т.ч на уровне ORM:

2.1. Простое использование TPH

В случае, когда все классы иерархии лежат в одной таблице, можно легко ссылаться на любой объект, ничего дополнительно дорабатывать и не надо, соответственно, поддерживается тем же SQLAlchemy как Single Table Inheritance. Но вариант с TPH не подходит для большого числа классов с массой разнообразных полей.

2.2. Dual-purpose Foreign Key антипаттерн для TPC / TPT

В случае с TPC и TPT, многим на ум приходит вариант использовать в таблице C "неофициальный", но универсальный foreign key, который будет ссылаться на строчку в любой из таблиц B, а также поле с типом, по которому можно понять целевую таблицу. Однако это нарушает принцип согласованности данных по ACID и позволяет легко выстрелить себе в ногу, потому является антипаттерном.

2.3. TPT + родительское поле типа

При добавлении в родителя поля, описывающего тип объекта, можно производить SELECT-JOIN и извлекать только нужные поля в вызывающем коде. Такой вариант поддерживается в SQLAlchemy как Joined Table Inheritance, позволяя лениво подгружать у объекта поля классов-наследников. На уровне SQL выглядит это так:

CREATE TABLE ObjType (
    type_id       int primary key,
    type_name     varchar(20)
);

insert into ObjType (type_id, type_name) values
    (1, 'A'), (2, 'B1'), (3, 'B2');

CREATE TABLE A (
    id            int primary key,
    type_id       int not null,
    a_value       int
);

CREATE TABLE B1 (
    id           int primary key,
    b1_value     int,
    FOREIGN KEY (id) REFERENCES B (id)
);

CREATE TABLE B2 (
    id           int primary key,
    b2_value     varchar(20),
    FOREIGN KEY (id) REFERENCES B (id)
);

На основе ответов How do you effectively model inheritance in a database?, How can you represent inheritance in a database? и Polymorphism in SQL database tables? из enSO и документаций Django и SQLAlchemy.

БЛОГ НА HUSL