Closure Table Model
This model uses the two table for managing hierarchical data. First table is for information of each node, and second table is for relationship of all nodes. Second table is referred as "closure table".
The closure table has two columns as hierarchical structure column. The columns contains all relationship between the ancestor nodes and each node.
Roles of closure table is to normalize path-column of Path Enumeration Model. Can describe flexible queries by the normalizing.
Model Design
I am going to express the following tree structure data indicated in Closure Table Model. [A], [B], ... indicated in figure means nodes.
[A]
|
+---+---+
| |
[B] [C]
| |
+-+-+ +-+-+
| | | |
[D] [E] [F] [G]
|
+-+-+
| |
[H] [I]
I am going to consider the table that has columns of id and name of each node, as a minimum of information about the node. Because of the table has no columns for hierarchical structure, can not restore tree data from the records in the table.
| id | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
|---|---|---|---|---|---|---|---|---|---|
| name | A | B | C | D | E | F | G | H | I |
Creates "closure Table" for storing a relationship between each node and its ancestor node. At this time, stores each node as own ancestors.
| tree_ancestor_id | 1 | 1 | 2 | 1 | 3 | 1 | 2 | 4 | 1 |
|---|---|---|---|---|---|---|---|---|---|
| tree_descendant_id | 1 | 2 | 2 | 3 | 3 | 4 | 4 | 4 | 5 |
| tree_ancestor_id | 2 | 5 | 1 | 3 | 6 | 1 | 3 | 7 | 8 |
|---|---|---|---|---|---|---|---|---|---|
| tree_descendant_id | 5 | 5 | 6 | 6 | 6 | 7 | 7 | 7 | 8 |
| tree_ancestor_id | 3 | 7 | 8 | 1 | 3 | 7 | 9 |
|---|---|---|---|---|---|---|---|
| tree_descendant_id | 8 | 8 | 8 | 9 | 9 | 9 | 9 |
CREATE TABLE `closure_table_models` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(225) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `closure_table_relations` (
`tree_ancestor_id` int(11) NOT NULL,
`tree_descendant_id` int(11) NOT NULL,
PRIMARY KEY (`tree_ancestor_id`, `tree_descendant_id`),
KEY `ancestors_index` (`tree_ancestor_id`),
KEY `descendants_index` (`tree_descendant_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO `closure_table_models` (`id`, `name`)
VALUES
(1, 'A'), (2, 'B'), (3, 'C'),
(4, 'D'), (5, 'E'), (6, 'F'),
(7, 'G'), (8, 'H'), (9, 'I');
INSERT INTO `closure_table_relations`
(`tree_descendant_id`, `tree_ancestor_id`)
VALUES
(1, 1),
(2, 1), (2, 2),
(3, 1), (3, 3),
(4, 1), (4, 2), (4, 4),
(5, 1), (5, 2), (5, 5),
(6, 1), (6, 3), (6, 6),
(7, 1), (7, 3), (7, 7),
(8, 1), (8, 3), (8, 7), (8, 8),
(9, 1), (9, 3), (9, 7), (9, 9)
;