閉包テーブルモデル
このモデルは、階層データを管理するための 2つのテーブルを使用します。ひとつ目のテーブルには「各ノードの情報」が保存され、ふたつ目のテーブルには「総てのノードの親子関係」が保存されます。ふたつ目のテーブルは、「閉包テーブル」と呼ばれます。
閉包テーブルには、ふたつの階層構造カラムを持ちます。全ノードと、それぞれの祖先ノードとの関係が保存されます。
経路列挙モデルのパスをバラバラにして正規化したテーブルを新設する事で、柔軟なクエリが記述できるようになっています。
モデルデザイン
以下のツリー構造データを「閉包テーブルモデル」で表してみましょう。図中の [A] ~ [I] はノードを示します。
[A]
|
+---+---+
| |
[B] [C]
| |
+-+-+ +-+-+
| | | |
[D] [E] [F] [G]
|
+-+-+
| |
[H] [I]
ノードに関する最小限の情報として、各ノードの id とノード名だけのテーブルを考えます。このテーブルには階層構造カラムがないので、ツリー構造データを保存して復元することはできません。
| id | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
|---|---|---|---|---|---|---|---|---|---|
| name | A | B | C | D | E | F | G | H | I |
各ノードと祖先ノードとの関係性を格納するための「閉包テーブル」を新設します。この時、自分の祖先として自分自身も格納します。
| 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)
;