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".
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.
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.
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/');