Microservices Thoughts

⚡Пара подходов к описанию деревьев в реляционной БД



Зачастую требуется хранить какие-то иерархичные данные, например, подобие файловой системы или какую-то организационную структуру.



1. id + parent_id

create table folders

(

    id              bigserial not null,

    parent_id bigint      null references folders (id),

    data          jsonb      not null

); 

Самый простой подход, при котором храним идентификатор родительской сущности (либо null, если сущность и так корневая).



Плюсы:

- Простота модели

- Простая вставка

- Простой перенос поддерева



Минусы:

- “Дерево” может стать не деревом - МД не запрещает циклические ссылки

- Рекурсивные запросы, чтобы доставать поддерево по id корня



2. id, path + parent_id, parent_path



Помимо id добавляется path - путь по айдишникам до текущей сущности. Это нам позволяет сделать более интересные констрейнты и гарантировать, что мы имеем дело реально с деревом.

create table folders

(

    id          bigserial not null,

    path        varchar   not null,

    parent_id   bigint    null,

    parent_path varchar   null,

    data        jsonb     not null



    constraint ck__folders__path

            check (path = coalesce(parent_path, '/') || id || '/'),



    -- нужен для FK

    constraint uc__folders__path__id

        unique (path, id),



    constraint fk__folders__parent_id__parent_path

        foreign key (parent_path, parent_id) references folders (path, id)

            match full

);

Здесь мы гарантируем, что

1) path корневой папки - это /<id>/

2) path некорневой папки - это <parent_path><id>/



Что позволяет обеспечить отсутствие циклов



Плюсы:

- Гарантия отсутствия циклов

- Простой запрос поддерева - where path like ‘/1/2/3/%’



Минусы:

- Сложность модели

- Более сложная вставка

- Сложный перенос поддерева