Пошук уроків, статей та іншого контенту
Розгляньте способи моделювання зв’язку одного типу запису з кількома таблицями та їхні компроміси.
Поліморфний зв’язок дає змогу одному запису посилатися на записи різних таблиць.
Наприклад, коментар може належати:
статті;
відео;
зображенню;
іншому типу контенту.
У звичайному реляційному зв’язку зовнішній ключ посилається на одну конкретну таблицю:
comment.article_id REFERENCES articles(id)Для поліморфного зв’язку потрібно, щоб
articlesvideosPostgreSQL не має вбудованого зовнішнього ключа, який динамічно змінює цільову таблицю залежно від значення іншого стовпця. Тому таку модель потрібно реалізувати одним із шаблонів.
Найпоширеніший варіант — зберігати назву типу та ідентифікатор запису:
comments
├── commentable_type
└── commentable_idПриклад даних:
commentable_type | commentable_id
-----------------+---------------
article | 10
video | 25Один коментар із commentable_type = 'article' належить запису articles.id = 10, а інший — запису videos.id = 25.
CREATE TABLE articles (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL
);
CREATE TABLE videos (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL
);
CREATE TABLE comments (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
commentable_type text NOT NULL
CHECK (commentable_type IN ('article', 'video')),
commentable_id bigint NOT NULL,
body text NOT NULL
);Таку таблицю легко заповнювати:
INSERT INTO articles (title)
VALUES ('Моделювання даних у PostgreSQL');
INSERT INTO videos (title)
VALUES ('Індекси та плани виконання');
INSERT INTO comments (commentable_type, commentable_id, body)
VALUES
('article', 1, 'Корисна стаття'),
('video', 1, 'Хороше пояснення');Однак у цій схемі commentable_id не є справжнім зовнішнім ключем. PostgreSQL не знає, що значення 1 має існувати в articles, якщо тип дорівнює article, або у videos, якщо тип дорівнює video.
Тому база даних прийме навіть некоректне значення:
INSERT INTO comments (commentable_type, commentable_id, body)
VALUES ('article', 999999, 'Посилання на неіснуючу статтю');Для підтримки цілісності можна перевіряти цільовий запис у тригері:
CREATE FUNCTION validate_comment_target()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
IF NEW.commentable_type = 'article' THEN
IF NOT EXISTS (
SELECT 1
FROM articles
WHERE id = NEW.commentable_id
) THEN
RAISE EXCEPTION
'Стаття з id % не існує',
NEW.commentable_id;
END IF;
ELSIF NEW.commentable_type = 'video' THEN
IF NOT EXISTS (
SELECT 1
FROM videos
WHERE id = NEW.commentable_id
) THEN
RAISE EXCEPTION
'Відео з id % не існує',
NEW.commentable_id;
END IF;
END IF;
RETURN NEW;
END;
$$;
CREATE TRIGGER comments_validate_target
BEFORE INSERT OR UPDATE OF commentable_type, commentable_id
ON comments
FOR EACH ROW
EXECUTE FUNCTION validate_comment_target();Тепер некоректний запис буде відхилено:
INSERT INTO comments (commentable_type, commentable_id, body)
VALUES ('article', 999999, 'Цей запис не вставиться');Тригер перевіряє існування цілі, але не створює повноцінний зовнішній ключ. Зокрема, він не захищає від видалення цільового запису після створення коментаря.
Якщо видалити статтю:
DELETE FROM articles
WHERE id = 1;PostgreSQL не видалить автоматично коментарі з:
commentable_type = 'article'
commentable_id = 1У результаті з’являться «сирітські» коментарі.
Можливі рішення:
забороняти видалення цільових записів на рівні застосунку;
створити тригери на кожній таблиці цілей;
виконувати каскадне видалення в одній транзакції;
зберігати всі можливі цілі в єдиній таблиці-батьку.
проста структура;
легко додавати нові типи цілей;
мало стовпців у таблиці зв’язку;
зручно використовувати в ORM, які підтримують polymorphic associations.
немає звичайного зовнішнього ключа;
перевірки цілісності потрібно реалізовувати тригерами або кодом застосунку;
каскадне видалення складніше;
запити часто містять умовну логіку.
Наприклад, для отримання назви цілі доведеться використовувати різні JOIN:
SELECT
c.id,
c.body,
COALESCE(a.title, v.title) AS target_title
FROM comments AS c
LEFT JOIN articles AS a
ON c.commentable_type = 'article'
AND c.commentable_id = a.id
LEFT JOIN videos AS v
ON c.commentable_type = 'video'
AND c.commentable_id = v.id;Інший підхід — створити для кожного типу окремий nullable-стовпець:
CREATE TABLE comments (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
article_id bigint REFERENCES articles(id),
video_id bigint REFERENCES videos(id),
body text NOT NULL,
CHECK (
(article_id IS NOT NULL)::integer +
(video_id IS NOT NULL)::integer = 1
)
);Один коментар має посилатися рівно на одну таблицю:
INSERT INTO comments (article_id, body)
VALUES (1, 'Коментар до статті');
INSERT INTO comments (video_id, body)
VALUES (1, 'Коментар до відео');CHECK гарантує, що:
не буде коментаря без цілі;
один коментар не посилатиметься одночасно на статтю та відео.
Умова:
(article_id IS NOT NULL)::integer +
(video_id IS NOT NULL)::integer = 1перетворює логічні значення на числа:
TRUE → 1;
FALSE → 0.
кожен зв’язок є справжнім зовнішнім ключем;
працюють ON DELETE CASCADE, ON DELETE RESTRICT та інші дії;
перевірки виконує сама база даних;
запити та індексація зрозумілі.
таблиця розростається при додаванні нових типів;
більшість стовпців містить NULL;
потрібно змінювати схему для кожного нового типу;
модель незручна, якщо типів багато або вони часто змінюються.
Цей варіант добре підходить, коли кількість типів невелика і відома заздалегідь.
Найбільш реляційний підхід — створити спільну таблицю, яка містить ідентифікатори всіх можливих цілей.
Таблиці конкретних типів стають підтипами цієї сутності:
content_items
├── id
└── kind
articles
├── id → content_items.id
└── title
videos
├── id → content_items.id
└── title
comments
└── content_item_id → content_items.idDROP SCHEMA IF EXISTS polymorphic_demo CASCADE;
CREATE SCHEMA polymorphic_demo;
SET search_path TO polymorphic_demo;
CREATE TABLE content_items (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
kind text NOT NULL
CHECK (kind IN ('article', 'video'))
);
CREATE TABLE articles (
id bigint PRIMARY KEY
REFERENCES content_items(id)
ON DELETE CASCADE,
title text NOT NULL
);
CREATE TABLE videos (
id bigint PRIMARY KEY
REFERENCES content_items(id)
ON DELETE CASCADE,
title text NOT NULL
);
CREATE TABLE comments (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
content_item_id bigint NOT NULL
REFERENCES content_items(id)
ON DELETE CASCADE,
body text NOT NULL
);
CREATE INDEX comments_content_item_id_idx
ON comments (content_item_id);
CREATE FUNCTION validate_content_item_kind()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
actual_kind text;
BEGIN
SELECT kind
INTO actual_kind
FROM content_items
WHERE id = NEW.id;
IF actual_kind IS NULL THEN
RAISE EXCEPTION
'Сутність content_items з id % не існує',
NEW.id;
END IF;
IF actual_kind <> TG_ARGV[0] THEN
RAISE EXCEPTION
'Сутність з id % має тип %, очікувався тип %',
NEW.id,
actual_kind,
TG_ARGV[0];
END IF;
RETURN NEW;
END;
$$;
CREATE TRIGGER articles_validate_kind
BEFORE INSERT OR UPDATE
ON articles
FOR EACH ROW
EXECUTE FUNCTION validate_content_item_kind('article');
CREATE TRIGGER videos_validate_kind
BEFORE INSERT OR UPDATE
ON videos
FOR EACH ROW
EXECUTE FUNCTION validate_content_item_kind('video');
CREATE FUNCTION prevent_content_item_kind_change()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
IF NEW.kind <> OLD.kind THEN
RAISE EXCEPTION
'Тип content_items.kind не можна змінювати';
END IF;
RETURN NEW;
END;
$$;
CREATE TRIGGER content_items_kind_immutable
BEFORE UPDATE OF kind
ON content_items
FOR EACH ROW
EXECUTE FUNCTION prevent_content_item_kind_change();
-- Спочатку створюємо записи в таблиці-батьку.
INSERT INTO content_items (kind)
VALUES ('article'), ('video');
-- Потім створюємо записи конкретних підтипів.
INSERT INTO articles (id, title)
VALUES (1, 'Моделювання даних у PostgreSQL');
INSERT INTO videos (id, title)
VALUES (2, 'Зовнішні ключі та цілісність даних');
INSERT INTO comments (content_item_id, body)
VALUES
(1, 'Коментар до статті'),
(2, 'Коментар до відео');
SELECT
c.id,
c.body,
ci.kind,
COALESCE(a.title, v.title) AS content_title
FROM comments AS c
JOIN content_items AS ci
ON ci.id = c.content_item_id
LEFT JOIN articles AS a
ON a.id = ci.id
LEFT JOIN videos AS v
ON v.id = ci.id
ORDER BY c.id;У цій моделі comments.content_item_id посилається лише на одну таблицю — content_items. Тому можна використовувати звичайний зовнішній ключ і каскадне видалення:
DELETE FROM content_items
WHERE id = 1;Разом із записом content_items будуть видалені:
відповідна стаття;
коментарі до цієї статті.
Зовнішні ключі гарантують, що:
articles.id існує в content_items.id;
videos.id існує в content_items.id;
comments.content_item_id існує в content_items.id.
Тригери додатково гарантують, що:
запис із content_items.kind = 'article' не можна вставити в videos;
запис із content_items.kind = 'video' не можна вставити в articles;
тип сутності не можна змінити після створення.
Наприклад, ця операція буде відхилена:
INSERT INTO videos (id, title)
VALUES (1, 'Некоректне відео');Ідентифікатор 1 належить сутності типу article, тому тригер не дозволить використати його для videos.
Під час створення об’єкта потрібно вставити два записи:
запис у content_items;
запис у таблицю конкретного підтипу.
Ці операції слід виконувати в одній транзакції:
BEGIN;
INSERT INTO content_items (kind)
VALUES ('article')
RETURNING id;
-- Припустімо, повернуто id = 3.
INSERT INTO articles (id, title)
VALUES (3, 'Нова стаття');
COMMIT;Якщо друга операція завершиться помилкою, транзакцію можна відкотити. У прикладі з ручним ідентифікатором це виглядає так:
BEGIN;
INSERT INTO content_items (kind)
VALUES ('article');
INSERT INTO articles (id, title)
VALUES (currval('content_items_id_seq'), 'Нова стаття');
COMMIT;У прикладному коді зручніше отримувати ідентифікатор через INSERT ... RETURNING та передавати його в наступний запит.
Використовуйте, коли:
типів багато;
типи можуть динамічно додаватися;
важлива простота структури;
частина перевірок цілісності може виконуватися тригерами або застосунком.
Основний компроміс — відсутність звичайного зовнішнього ключа.
Використовуйте, коли:
кількість типів невелика;
типи стабільні;
важлива максимальна декларативна цілісність;
кожен зв’язок повинен бути повноцінним FOREIGN KEY.
Основний компроміс — схема стає ширшою при кожному новому типі.
Використовуйте, коли:
усі типи мають спільну концепцію;
для всіх типів потрібен єдиний ідентифікатор;
потрібні звичайні зовнішні ключі та каскади;
спільні операції над різними типами є типовим сценарієм.
Основний компроміс — складніше створення об’єктів і додаткова таблиця-батько.
Для зв’язку «тип + ідентифікатор» індекс потрібно будувати на обох стовпцях:
CREATE INDEX comments_target_idx
ON comments (commentable_type, commentable_id);Порядок стовпців важливий для запитів, які фільтрують за типом ідентифікатора:
SELECT *
FROM comments
WHERE commentable_type = 'article'
AND commentable_id = 10;Для моделі зі спільною таблицею достатньо індексу на зовнішньому ключі:
CREATE INDEX comments_content_item_id_idx
ON comments (content_item_id);PostgreSQL автоматично створює індекс для первинного ключа, але не створює його автоматично для зовнішнього ключа.
Такий запис не є допустимою моделлю зовнішнього ключа:
-- Це не працює як динамічний FOREIGN KEY.
FOREIGN KEY (commentable_type, commentable_id)
REFERENCES commentable_type(commentable_id)REFERENCES завжди посилається на конкретну таблицю та конкретні стовпці.
commentable_idЗначення ідентифікатора без типу недостатньо:
commentable_id = 10Незрозуміло, чи це articles.id, videos.id або ідентифікатор іншого ресурсу.
Якщо для commentable_type немає CHECK, можна випадково зберегти довільний рядок:
commentable_type = 'artcile'Краще використовувати CHECK, enum або окремий довідник типів. Для невеликої фіксованої множини значень CHECK зазвичай достатньо.
Перевірка існування цілі перед вставкою в застосунку не захищає від паралельних операцій. Інший процес може видалити ціль між перевіркою та вставкою.
Якщо використовується модель «тип + ідентифікатор», перевірку потрібно продумати на рівні тригерів і транзакцій.
Перевірка під час INSERT не вирішує проблему видалення. Для кожного типу потрібно окремо визначити політику:
каскадно видаляти залежні записи;
забороняти видалення;
залишати запис, але позначати ціль видаленою;
архівувати ціль.
Під час вибору моделі поставте такі запитання:
Чи повинна база даних гарантувати існування цілі через звичайний зовнішній ключ?
Чи відомі всі типи цілей наперед?
Чи потрібне каскадне видалення?
Чи мають різні типи спільну предметну сутність?
Як часто додаватимуться нові типи?
Чи повинні всі цілі мати єдиний простір ідентифікаторів?
Якщо потрібні сильні декларативні гарантії та спільна ідентичність, зазвичай найнадійнішим є варіант зі спільною таблицею сутностей.
Якщо типів багато, а зв’язок має просту структуру, може підійти пара «тип + ідентифікатор», але потрібно самостійно реалізувати контроль цілісності.
PostgreSQL не підтримує динамічний зовнішній ключ на одну з кількох таблиць.
Поліморфний зв’язок можна змоделювати трьома основними способами:
пара type + id;
кілька nullable-зовнішніх ключів;
спільна таблиця сутностей.
Пара type + id проста, але не має повноцінної підтримки FOREIGN KEY.
Кілька nullable-зовнішніх ключів забезпечують сильну цілісність, але погано масштабуються за кількістю типів.
Спільна таблиця сутностей дозволяє зберегти звичайні зовнішні ключі, каскадне видалення та єдиний простір ідентифікаторів.
Вибір моделі залежить від кількості типів, вимог до цілісності, політики видалення та спільності предметної сутності.