Path Enumeration Model
"Path enumeration model" is the model designed to be easier to find the relational nodes (such as sub-tree, ancestor nodes, etc.). The table of model has a column to save "path from the root node" for each node. The column manages a parent-child relationship in hierarchical data comprehensive. Also known as "Materialized Path Model".
Model Design
I am going to express the following tree structure data indicated in Path Enumeration Model. [A], [B], ... indicated in figure means nodes.
[A]-+-[B]-+-[D]
| |
| +-[E]
|
+-[C]-+-[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 |
A table of Path Enumeration Model has a column to save hierarchical data for "path from the root node" of each node. The path column contains a string of path ids from root node for each node, concatenated by a symbol charactor. In this example, uses the "/ (slash)," as a symbol.
| id | name | tree_path |
|---|---|---|
| 1 | A | 1/ |
| 2 | B | 1/2/ |
| 3 | C | 1/3/ |
| 4 | D | 1/2/4/ |
| 5 | E | 1/2/5/ |
| 6 | F | 1/3/6/ |
| 7 | G | 1/3/7/ |
| 8 | H | 1/3/7/8/ |
| 9 | I | 1/3/7/9/ |
Added only one column for hierarchical data, but it stores a plurality of data in a single column. In Path Enumeration Model, the number of total hierarchical structure columns is considered to vary by the depth of the hierarchy.
CREATE TABLE `path_enumeration_models` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(225) DEFAULT NULL,
`tree_path` varchar(225) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `path_index` (`tree_path`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO `path_enumeration_models`
(`id`, `name`, `tree_path`)
VALUES
(1, 'A', '1/' ), (2, 'B', '1/2/' ), (3, 'C', '1/3/' ),
(4, 'D', '1/2/4/'), (5, 'E', '1/2/5/' ), (6, 'F', '1/3/6/' ),
(7, 'G', '1/3/7/'), (8, 'H', '1/3/7/8/'), (9, 'I', '1/3/7/9/');