Logo Море(!) аналитической информации!
IT-консалтинг Software Engineering Программирование СУБД Безопасность Internet Сети Операционные системы Hardware
Бесплатный конструктор сайтов и Landing Page

Хостинг с DDoS защитой от 2.5$ + Бесплатный SSL и Домен

SSD VPS в Нидерландах под различные задачи от 2.6$

✅ Дешевый VPS-хостинг на AMD EPYC: 1vCore, 3GB DDR4, 15GB NVMe всего за €3,50!

🔥 Anti-DDoS защита 12 Тбит/с!

VPS в России, Европе и США

Бесплатная поддержка и администрирование

Оплата российскими и международными картами

🔥 VPS до 5.7 ГГц под любые задачи с AntiDDoS в 7 локациях

💸 Гифткод CITFORUM (250р на баланс) и попробуйте уже сейчас!

🛒 Скидка 15% на первый платеж (в течение 24ч)

2001 г

Моделирование иерархических объектов

Виноградов С. А.
04.06.2001 г.

Введение

Многим структурам и объектам свойственна иерархичность. За примерами далеко ходить не надо. Почти все объекты состоят из частей, которые, в свою очередь, могут состоять из более мелких деталей. Общественные структуры, как правило, отражают жесткую иерархическую модель подчинения, сходящуюся к одному подразделению или человеку.

Из-за внешнего сходства, иерархические модели часто называют деревьями, вершину иерархии - корнем дерева, самые низшие ответвления - листьями.

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

Иерархические структуры довольно часто приходиться моделировать в базах данных, при этом возникают некоторые, характерные только для иерархии, вопросы. Например, как узнать количество всех потомков у данного элемента, или, на каком уровне он находится, или, допустим, требуется получить список всех потомков заданного элемента, у которых нет детей. Попробуем получить ответы на эти и другие вопросы.

Простейшая иерархия

В качестве примера, возьмем часть схемы подразделений на предприятии:

A1
B1B2
C1C2C3

где А1 - подразделение верхнего уровня (возможно, не единственное на этом уровне),
В1 и В2 - входят в А1,
С1 - входит в В1, С2 и С3 - входят в В2.

Можно легко создать таблицу, которая содержит информацию об этих подразделениях (здесь идалее - синтаксис MS SQL):

create table Departments
(
Id int not null identity primary key,
Parent int not null references Departments(Id),
Name varchar(32) not null
)

Поле Parent является ссылкой на Id (первичный ключ) вышестоящего уровня в иерархии. В данном случае оно указывает, в какое подразделение входит каждый отдел.

Departments
Id Parent Name
1 0 A1
2 1 B1
3 1 B2
4 2 C1
5 3 C2
6 3 C3

Характерные вопросы

Пользуясь такой таблицей, легко можно найти родителя или детей у определенного элемента. Но обычно с иерархическими объектами возникают более сложные задачи.

Вероятно, мы захотим узнать, является ли данный элемент терминальным, то есть, отсутствуют ли другие элементы, входящие в него. Это нужно, например, для того, чтобы отличать такие элементы от нетерминальных при выводе иерархического справочника пользователю.

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

Может оказаться необходимым получить всех предков данного элемента, начиная сверху или снизу.

Часто бывает нужно получить всех потомков какого-нибудь элемента, при этом с разными условиями: например, только терминальных, или, находящихся только на определенном уровне и т. д.

Из приведенной выше схемы совсем не очевидны ответы на все эти вопросы. Конечно, если сервер базы данных специально поддерживает иерархию или допускает рекурсию в запросах, то большинство ответов можно получить одним запросом. В противном случае получение результатов будет крайне неэффективным. Как же организовать хранение иерархических объектов, чтобы легко и быстро получать ответы на перечисленные вопросы?

Обход дерева

Одно из решений было предложено Joe Celko (см. [1]). Он рекомендовал добавить в таблицу два дополнительных целочисленных поля: Left и Right, в которые заносятся результаты обхода дерева, начиная с корня. Обход организуется следующим образом: каждый раз, при прохождении какого-нибудь элемента указателем, счетчик увеличивается на единицу; при попадании в терминальный элемент, указатель возвращается в родителя и ищет следующего ребенка. В поле Left записывается значение счетчика при первом прохождении элемента, а в поле Right - при последнем. При таком варианте, наша таблица подразделений выглядела бы следующим образом:

create table Departments
(
Id int not null identity primary key,
Parent int not null references Departments(Id),
Name varchar(32) not null,
Left int not null,
Right int not null
)

Departments
Id Parent Name Left Right
1 0 A1 1 11
2 1 B1 2 4
3 1 B2 6 10
4 2 C1 3 3
5 3 C2 7 7
6 3 C3 9 9

Комбинируя эти поля и сравнивая их с такими же полями других элементов, такая схема позволяет получить ответы на все поставленные вопросы.

Терминальные элементы заметно сразу - у них Left = Right.

Отношения предок - потомок вычисляются тоже легко: Left потомка всегда больше чем у предка, а Right - меньше.

Информацию об уровне заданного элемента можно узнать, получив количество его предков.

Количество потомков = (Right - Left) / 2.

Главный недостаток этого решения в том, что при изменении в структуре дерева, приходится заново пересчитывать значения полей Left и Right во всей таблице. То есть, такой способ годится только для небольших, редко изменяемых таблиц.

Вспомогательная таблица

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

create table DepartmentsAncestors
(
Department int not null references Departments(Id),
Ancestor int not null references Departments(Id)
constraint DepartmentAncestor primary key (Department, Ancestor)
)

Поле Ancestor ссылается на Id предка каждого элемента. В данном случае оно позволяет узнать все подразделения, в которые входит данный отдел.

DepartmentsAncestors
Department Ancestor
2 1
3 1
4 2
4 1
5 3
5 1
6 3
6 1

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

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

Информацию об уровне заданного элемента можно узнать, получив количество его предков.

Терминальность элемента можно узнать, пользуясь только основной таблицей - достаточно проверить, есть ли элементы, Parent которых равен Id текущего.

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

Если запросы с использование информации об уровне и признаке терминальности встречаются часто, и требования к времени их выполнения высоки, то желательно создать соответствующие поля в основной таблице и поддерживать в них актуальные значения. Например:

create table Departments
(
Id int not null identity primary key,
Parent int not null references Departments(Id),
Name varchar(32) not null,
Level int not null,
Terminal bit not null defaul(1)
)

Departments
Id Parent Name Level Terminal
1 0 A1 1 0
2 1 B1 2 0
3 1 B2 2 0
4 2 C1 3 1
5 3 C2 3 1
6 3 C3 3 1

Пользуясь двумя такими таблицами, можно легко строить практически любые запросы, характерные для иерархических объектов.


Используемые источники

[1] Joe Celko "A Look at SQL Trees",
http://ib.demo.ru/DevInfo/DBMSTrees/9603d06.html
http://ib.demo.ru/DevInfo/DBMSTrees/9604d06.html
http://ib.demo.ru/DevInfo/DBMSTrees/9605d06.html

 

Скидка до 20% на услуги дата-центра. Аренда серверной стойки. Colocation от 1U!

Миграция в облако #SotelCloud. Виртуальный сервер в облаке. Выбрать конфигурацию на сайте!

Виртуальная АТС для вашего бизнеса. Приветственные бонусы для новых клиентов!

Виртуальные VPS серверы в РФ и ЕС

Dedicated серверы в РФ и ЕС

По промокоду CITFORUM скидка 30% на заказ VPS\VDS

VPS/VDS серверы. 30 локаций на выбор

Серверы VPS/VDS с большим диском

Хорошие условия для реселлеров

4VPS.SU - VPS в 17-ти странах

2Gbit/s безлимит

Современное железо!

Новости мира IT:

Архив новостей

IT-консалтинг Software Engineering Программирование СУБД Безопасность Internet Сети Операционные системы Hardware

Информация для рекламодателей PR-акции, размещение рекламы — adv@citforum.ru,
тел. +7 495 7861149
Пресс-релизы — pr@citforum.ru
Обратная связь
Информация для авторов
Rambler's Top100 TopList This Web server launched on February 24, 1997
Copyright © 1997-2000 CIT, © 2001-2019 CIT Forum
Внимание! Любой из материалов, опубликованных на этом сервере, не может быть воспроизведен в какой бы то ни было форме и какими бы то ни было средствами без письменного разрешения владельцев авторских прав. Подробнее...