Database Reference
In-Depth Information
Figure 14-12.
Closure Table
Listing 14-15.
Closure Table DDL
create table dbo.OrgChart
(
ID int not null,
Name nvarchar(64) not null,
Title nvarchar(64) not null,
constraint PK_OrgChart
primary key clustered(ID),
);
create table dbo.OrgTree
(
ParentId int not null,
ChildId int not null,
constraint PK_OrgTree
primary key clustered(ParentId, ChildId),
constraint FK_OrgTree_OrgChart_Parent
foreign key(ParentId)
references dbo.OrgChart(ID),
constraint FK_OrgTree_OrgChart_Child
foreign key(ChildId)
references dbo.OrgChart(ID)
);
Nested Sets
.
With Nested Sets, every node contains two values, called
Left
and
Right
Bowers
. Child node bower values are within the interval of the parent node bowers. As a
result, when you need to find all of the children of the parent, you can select all nodes with
left and right bower values in between the parent values. Figure
14-13
and Listing 14-16
show an example of Nested Sets
.
