Классика баз данных - статьи

       

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


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

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



Содержание раздела