Как хранить иерархическую структуру в базе данных?

Иерархические структуры в базе данных

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

Подход 1: Parent ID (Adjacent List)

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

CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    parent_id INT REFERENCES categories(id)
);

Примеры данных:

id=1, name="Электроника", parent_id=NULL
id=2, name="Компьютеры", parent_id=1
id=3, name="Ноутбуки", parent_id=2
id=4, name="Мобильные", parent_id=1

Плюсы:

• Простая реализация
• Быстрая вставка/обновление
• Минимум памяти

Минусы:

• Сложные рекурсивные запросы
• Медленное получение поддерева
• Сложный поиск предков

WITH RECURSIVE subtree AS (
    SELECT id, name FROM categories WHERE id = 2
    UNION ALL
    SELECT c.id, c.name FROM categories c
    JOIN subtree s ON c.parent_id = s.id
)
SELECT * FROM subtree;

Подход 2: Nested Sets

Узлам присваиваются левая и правая границы. Потомки находятся внутри этого диапазона.

CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    lft INT NOT NULL,
    rgt INT NOT NULL
);

Плюсы:

• Очень быстрые запросы поддеревьев
• Эффективное получение иерархии в один запрос

Минусы:

• Сложность при добавлении/удалении
• Нужна перенумерация
• Сложнее для понимания

SELECT * FROM categories WHERE lft BETWEEN 2 AND 7;

Подход 3: Closure Table

Дополнительная таблица хранит все пути предков-потомков.

CREATE TABLE category_closure (
    ancestor_id INT,
    descendant_id INT,
    depth INT,
    PRIMARY KEY (ancestor_id, descendant_id)
);

Плюсы:

• Быстрые запросы всех путей
• Гибкое добавление/удаление
• Явное представление отношений

Минусы:

• Дополнительная таблица (много строк)
• Сложность при изменении
• Больше памяти

SELECT descendant_id FROM category_closure WHERE ancestor_id = 1;

Подход 4: JSON (PostgreSQL)

Хранить иерархию как JSONB для гибкости.

CREATE TABLE categories (
    id INT PRIMARY KEY,
    data JSONB
);

Плюсы:

• Естественное представление
• Гибкая структура

Минусы:

• Сложнее поиск
• Не нормализовано

Рекомендации

Parent ID: используй для небольших деревьев и частых редакций

Nested Sets: для больших деревьев, которые редко меняются

Closure Table: для сложных запросов и анализа отношений

JSON: для небольших динамичных структур

В Python используй SQLAlchemy с relationship() для self-referential связей, или DjangoORM пакеты django-mppt и django-treebeard. Выбирай подход в зависимости от баланса между скоростью чтения и записи для вашего случая.