Trees and Hierarchies in SQL

Trees and Hierarchies in SQL
by Eduard Hildebrandt

In most projects we have to deal with
some kind of trees or hierarchies!

Think about…
• Categories
• Organisation
• Threads
• Folders
• Components
•…

How can we store an retrieve trees and hierarchies
effenciently from relational databases?

Agenda

• Adjacency List Model
• Closure Table Model
• Path Enumeration Model
• David Chandler Model
• Modified David Chandler Model
• Nested Set Model
• Hyprid Model
• Frequent Insertion in Nested Sets

What is a „tree“?
A tree is a connected, undirected, acyclic grap.

A

B C

D E F G H

I

What is a „tree“?
A tree is a connected, undirected, acyclic grap.

A

B C

D E F G H

I X

Adjacency List Model
A

B C

D E F G H

I
title parent Creating the table:
CREATE TABLE nodes
A null (title VARCHAR(255) NOT NULL PRIMARY KEY,
B A parent VARCHAR(255));

C A Finding the root node:
D B SELECT *
FROM nodes
E B WHERE parent IS NULL;
F C
Finding the leaf nodes:
G C
SELECT node1.*
H C FROM nodes AS node1
LEFT JOIN node AS node2 ON node1.title = node2.parent
I F WHERE node2.title = ‘A’;

UPDATE anomalies
A

B C

D E F G H

title parent I
A null UPDATE nodes
SET title = ‘X’
B A WHERE title = ‘C’;
C A
D B START TRANSACTION;
UPDATE nodes
E B SET title = ‘X’
WHERE title = ‘C’;
F C UPDATE nodes
G C SET parent = ‘X’
WHERE parent = ‘C’;
H C COMMIT;

I F

INSERT anomalies
INSERT INTO nodes (title, parent) VALUES
(‘X’, ‘Y’), X Y
(‘Y’, ‘X’);

INSERT INTO nodes (title, parent) VALUES X
(‘X’, ‘X’);

Parent must exist:
ALTER TABLE nodes ADD CONSTRAINT parent_fk FOREIGN KEY (parent)
REFERENCES nodes (id);

Node can not be it‘s own parent:
CHECK (title <> parent)

Prevent double connections:
UNIQUE (title, parent)

Connected graph (edges = nodes – 1):
CHECK ((SELECT COUNT(*) FROM nodes) – 1
= SELECT COUNT(parent) FROM nodes;

One root only:
CHECK ((SELECT COUNT(*) FROM nodes WHERE parent IS NULL) = 1);

DELETE anomalies

DELETE FROM nodes WHERE title = ‘C’;

A

B C

D E F G H

I

DELETE anomalies

What happens with nodes F, G, H and I?

A

B ?
D E F G H

I

ALTER TABLE nodes ADD CONSTRAINT parent_fk FOREIGN KEY (parent)
REFERENCES nodes (id)
ON DELETE ...;

DELETE anomalies

Solution 1: delete all children

A

B C

D E F G H

I

DELETE anomalies

Solution 2: move children to the level of the parent

A

B F G H

D E I

DELETE anomalies

Solution 3: replace parent with the first child

A

B F

D E I G H

with self -references
A

B C

D E F G H

title parent
I
A A Creating the table:
CREATE TABLE nodes
B A (title VARCHAR(255) NOT NULL PRIMARY KEY,
parent VARCHAR(255) NOT NULL);
C A
D B
E B Avoids NULL in the parent column!
F C
G C
H C
I F

Path Enumeration Model
A

B C

D E F G H

I
title path Creating the table:
CREATE TABLE nodes
A A (title VARCHAR(255) NOT NULL PRIMARY KEY
B A/B -- don’t use a separater in primary key
CHECK (REPLACE (title, ‘/’, ‘’) = title,
C A/C path VARCHAR(1024) NOT NULL);

D A/B/D
E A/B/E
F A/C/F
G A/C/G
H A/C/H
I A/C/F/I

Closure Table Model
A

B C

D E F G H

ID parent Ancestor Descendant
1 A 1 2
2 B 1 4
3 C 1 5
4 D 2 4
5 E 2 5
6 F 1 3
7 G 1 6
8 H 1 7
1 8

David Chandler Model
A

B C

D E F G H

ID T L1 L2 L3 Creating the table:
CREATE TABLE nodes
1 A 1 0 0 (id INTEGER NOT NULL PRIMARY KEY
2 B 1 1 0 title VARCHAR(255) NOT NULL
L1 INTEGER NOT NULL,
3 C 1 2 0 L2 INTEGER NOT NULL,
L3 INTEGER NOT NULL);
4 D 1 1 1
5 E 1 1 2 Algorithm is patened by David Chandler!
6 F 1 2 1
7 G 1 2 2 Hierarchy level is fixed!
8 H 1 2 3

Modified David Chandler Model
A

B C

D E F G H

ID T L1 L2 L3 Creating the table:
CREATE TABLE nodes
1 A 1 0 0 (id INTEGER NOT NULL PRIMARY KEY
2 B 1 2 0 title VARCHAR(255) NOT NULL
L1 INTEGER NOT NULL,
3 C 1 3 0 L2 INTEGER NOT NULL,
L3 INTEGER NOT NULL);
4 D 1 2 4
5 E 1 2 5 Not sure about implecations of David
6 F 1 3 6 Chandlers patent…
7 G 1 3 7 Hierarchy level is fixed!
8 H 1 3 8

Nested Set Model
1 A 18

2 B 7 8 C 17

3 D 4 5 E 6 9 F 12 13 G 14 15 H 16

10 I 11
title lft rgt Creating the table:
CREATE TABLE nodes
A 1 18 (title VARCHAR(255) NOT NULL PRIMARY KEY,
B 2 7 lft INTEGER NOT NULL,
rgt INTEGER NOT NULL);
C 8 17 Finding the root node:
D 3 4 SELECT *
FROM nodes
E 5 6 WHERE lft = 1;
F 9 12
Finding the leaf nodes:
G 13 14
SELECT *
H 15 16 FROM nodes
WHERE lft = (MAX(rgt) – 1)
I 10 11

Nested Set Model
1 A 18

2 B 7 8 C 17

3 D 4 5 E 6 9 F 12 13 G 14 15 H 16

10 I 11
title lft rgt Finding subtrees:
A 1 18 SELECT * FROM nodes
WHERE lft BETWEEN lft AND rgt
B 2 7 ORDER BY lft ASC;
C 8 17 Finding path to a node:
D 3 4 SELECT * FROM nodes
WHERE lft < ? AND rgt > ?
E 5 6 ORDER BY lft ASC;
F 9 12
G 13 14
H 15 16
I 10 11

Hybrid Models

Combining models

Adjacency List

David Chandler
Path Enumeration

Nested Set
Closure Table

Nested Set Model

Problem: Tree must be reorganized on every insertion (table lock).

1 A 100

2 B 49 50 C 99

D E F G H
3 24 25 48 51 72 73 14 15 16

Possible solution: Avoid overhead with larger spreads and bigger gaps

Nested Set Model

What datatype shout I use for left and right values?

INTEGER

FLOAT / REAL / DOUBLE

NUMERIC / DECIMAL

Nested Set Model
with rational numbers

0/5 A 5/5

1/5 B 2/5 3/5 C 4/5

a b

a+(b-a)/3 a+2(b-a)/3

Nested Set Model
with rational numbers

0/5 A 5/5

1/5 B 2/5 3/5 C 4/5

16/25 D 17/25

T ln ld rn rd
A 0 5 5 5
Adding an aditional child node is alway possible.
B 1 5 2 5 No need to reorganize the table!
C 3 5 4 5
D 16 25 17 25

Nested Set Model
Binary Fraction

node X Y
1 1 1/2
1.1 1 3/4
1.1.1 1 7/8
1.1.1.1 1 15/16
1.1.2 7/8 13/16
1.1.3 13/16 25/32
1.2 3/4 5/8
1.2.1 3/4 11/16
1.3 5/8 9/16

Literature

Titel: Trees and Hierarchies in SQL
Autor: Joe Celko
Verlag: Morgan Kaufmann
ISBN: 1558609202

Trees and Hierarchies in SQL

More Related Content

What's hot

Similar to Trees and Hierarchies in SQL

Recently uploaded

In this document

Trees and Hierarchies in SQL