Adjacency List Model
Adjacency List Model is the simplest designed model in conventional models.
Model Design
I am going to express the following tree structure data indicated in Adjacency List 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 |
"Parent" column is added as a hierarchical structure column in Adjacency List Model. Save null for root node, because it has no parent of tree structure.
| id | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
|---|---|---|---|---|---|---|---|---|---|
| name | A | B | C | D | E | F | G | H | I |
| tree_parent | null | 1 | 1 | 2 | 2 | 3 | 3 | 7 | 7 |
CREATE TABLE `adjacency_list_models` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(225) DEFAULT NULL,
`tree_parent` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `parent_index` (`tree_parent`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO `adjacency_list_models`
(`id`, `name`, `tree_parent`)
VALUES
(1, 'A', NULL), (2, 'B', 1), (3, 'C', 1),
(4, 'D', 2), (5, 'E', 2), (6, 'F', 3),
(7, 'G', 3), (8, 'H', 7), (9, 'I', 7);