Task Livecoding SQL Org Directory Schema | IT Ментор

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

Условие задачи:
📌 Нужно спроектировать структуру БД для классического справочника организаций с иерархией уровней (холдинг → управление → отдел → бюро и т.д.).
Нужно ответить:

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

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

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

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

Подсказки

💡 Для иерархии чаще всего используют adjacency list: у каждой строки есть parent_id.
💡 Можно сделать одну таблицу org_unit, где верхний уровень (ТН) имеет parent_id = NULL.
💡 Полезно хранить:
– id
– parent_id
– name
– тип или уровень (type / level)
– сортировку в рамках одного родителя (sort_order).
💡 Если нужно несколько организаций, можно добавить root_id или tree_id.

Решение

Для типового справочника я бы сделал одну таблицу с иерархией по parent_id:

CREATE TABLE org_units (
    id           BIGINT PRIMARY KEY,          -- уникальный идентификатор узла
    parent_id    BIGINT NULL,                 -- ссылка на родителя (NULL для корня)
    name         VARCHAR(255) NOT NULL,       -- название: "ТН - Технологии", "Управление ИБ", "НПС 1" и т.п.
    type         VARCHAR(50)  NOT NULL,       -- тип узла: 'HOLDING', 'REGION', 'DEPARTMENT', 'BUREAU', 'NPS' и т.д.
    level        INT          NOT NULL,       -- уровень вложенности: 1,2,3,4...
    code         VARCHAR(50),                 -- внутренний код подразделения (если нужен)
    sort_order   INT          NOT NULL DEFAULT 0,  -- порядок внутри одного parent
    is_active    BOOLEAN      NOT NULL DEFAULT TRUE, -- актуальность записи
    created_at   TIMESTAMP    NOT NULL DEFAULT now(),
    updated_at   TIMESTAMP    NOT NULL DEFAULT now()
);

-- Внешний ключ на родителя
ALTER TABLE org_units
    ADD CONSTRAINT fk_org_units_parent
    FOREIGN KEY (parent_id) REFERENCES org_units(id);

Как это работает на вашем примере:

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

Почему одной таблицы достаточно:

Иерархия произвольной глубины — просто цепочка parent_id.
Не нужно плодить таблицы под каждый уровень (holdings, departments, bureaus и т.п.).
Легко добавлять новые уровни без миграций схемы (новое значение в type).
Запросы вида «все дочерние подразделения ТН - Технологии» строятся по parent_id/level/type.

Альтернатива: если хочется отделить организацию в целом от структурных единиц,
можно добавить таблицу organizations и в org_units хранить organization_id.
Но для классического «дерева подразделений» часто достаточно одной org_units.