Схема БД для иерархического справочника организаций

59. Спроектировать таблицу для иерархического справочника организаций

Условие задачи:
Необходимо спроектировать структуру БД для иерархического справочника подразделений.

Структура может иметь произвольную глубину, например:

ТН - Технологии
  Управление ИБ
    Отдел разработки решений ИБ
      Бюро разработки
      Бюро эксплуатации
  Управление ИТ
    Отдел ИТ
    Отдел архитектуры

ТН - Дальний восток
  РНУ
    НПС 1
    НПС 2

Нужно определить:

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

Спойлеры к решению

Подсказки

💡 Для дерева произвольной глубины удобно использовать модель Adjacency List.
💡 Каждая строка представляет один узел дерева и содержит ссылку parent_id на родительский узел.
💡 У корневых элементов parent_id = NULL.
💡 Необязательно создавать отдельные таблицы для управлений, отделов, бюро и других уровней.
💡 Для быстрого поиска непосредственных потомков полезен индекс по parent_id.

Решение

Для такого справочника обычно достаточно одной таблицы, которая ссылается сама на себя через parent_id.

CREATE TABLE org_unit (
    id BIGSERIAL PRIMARY KEY,
    parent_id BIGINT REFERENCES org_unit(id),
    name VARCHAR(255) NOT NULL,
    type VARCHAR(50),
    code VARCHAR(50),
    sort_order INT NOT NULL DEFAULT 0,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CHECK (parent_id IS NULL OR parent_id <> id)
);

Для поиска дочерних подразделений стоит добавить индекс:

CREATE INDEX idx_org_unit_parent_id
    ON org_unit (parent_id);

Пример хранения дерева:

id | parent_id | name                        | type
---+-----------+-----------------------------+-----------
1  | NULL      | ТН - Технологии             | HOLDING
2  | 1         | Управление ИБ               | MANAGEMENT
3  | 2         | Отдел разработки решений ИБ | DEPARTMENT
4  | 3         | Бюро разработки             | BUREAU
5  | 3         | Бюро эксплуатации           | BUREAU
6  | 1         | Управление ИТ               | MANAGEMENT
7  | 6         | Отдел ИТ                    | DEPARTMENT
8  | 6         | Отдел архитектуры           | DEPARTMENT
9  | NULL      | ТН - Дальний восток         | HOLDING
10 | 9         | РНУ                         | MANAGEMENT
11 | 10        | НПС 1                       | NPS
12 | 10        | НПС 2                       | NPS

Например, чтобы получить непосредственных потомков узла:

SELECT
    id,
    name,
    type
FROM org_unit
WHERE parent_id = 1
ORDER BY sort_order, name;

Для получения всего поддерева можно использовать рекурсивный CTE:

WITH RECURSIVE tree AS (
    SELECT
        id,
        parent_id,
        name,
        type,
        0 AS depth
    FROM org_unit
    WHERE id = 1

    UNION ALL

    SELECT
        child.id,
        child.parent_id,
        child.name,
        child.type,
        tree.depth + 1
    FROM org_unit child
    JOIN tree
        ON child.parent_id = tree.id
)
SELECT *
FROM tree
ORDER BY depth, id;

Хранить level в самой таблице обычно необязательно: глубину узла можно вычислить при обходе дерева. Отдельное поле level имеет смысл добавлять только как денормализованное значение, если это действительно требуется для производительности.

Если в одной БД нужно хранить несколько независимых организаций как отдельные бизнес-сущности, можно дополнительно создать таблицу organization и добавить в org_unit поле organization_id.

Основная идея схемы:

org_unit
   │
   ├── id
   ├── parent_id ──→ org_unit.id
   ├── name
   ├── type
   └── ...

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