SQLアンチパターン：ナイーブツリー（素朴な木）

[SQLアンチパターン](https://www.amazon.co.jp/dp/4873115892/)を読み始めたので、1つ１つ書いてのメモです

# 目的

- 階層構造を格納し、クエリーを実行する
- ツリー構造
 - 親（ルート）、子（ノード）、葉（リーフ）

# アンチパターン

- 常に親のみに依存する
 - 親のIDをいれる
 - 「隣接リスト」という

- ノードの追加、サブツリーへの移動は簡単
 - ノードの削除は大変

## 用いてもいいパターン

- ノードの直近の親と子を簡単に取得できること
- 階層構造を持つデータに対して必要な操作が、列の挿入のみである場合
- WITH キーワードの後に共通テーブル式が使える場合
 - 再帰クエリ構文

# 解決策

- 代替ツリーモデルを使用する
 - 経路列挙モデル (Path Enumeration)
 - 入れ子集合モデル(Nested Set)
 - 閉包テーブルモデル(Closure Table)

## 経路列挙モデル

- 経路を `1/5/7` など入れる
- 信号無視と同じアンチパターンが発生する

## 入れ子集合モデル

- よくわからない...

## 閉包テーブルモデル

- エントリーと関連構造を示すテーブルでツリー構造を示す

```sql
CREATE TABLE Comments (
  comment_id serial primary key,
  bug_id bigint unsigned not null,
  author bigint unsigned not null,
  comment_date datetime not null,
  comment text not null
  -- FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
  -- FOREIGN KEY (author) REFERENCES Accounts(account_id)
);

CREATE TABLE TreePaths (
  ancestor BIGINT UNSIGNED NOT NULL,
  descendant BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (ancestor, descendant)
  -- FOREIGN KEY (ancestor) REFERENCES Comments(comment_id)
  -- FOREIGN KEY (descendant) REFERENCES Comments(comment_id)
);

CREATE TABLE Authors (
  author_id serial primary key,
  author_name VARCHAR(1000)
);

INSERT INTO Authors (author_id, author_name) VALUES (DEFAULT, 'ガイア');
INSERT INTO Authors (author_id, author_name) VALUES (DEFAULT, 'マッシ');
INSERT INTO Authors (author_id, author_name) VALUES (DEFAULT, 'オルテガ');

INSERT INTO Comments (comment_id, bug_id, author, comment_date, comment) VALUES (DEFAULT, 1, 1, now(), 'このバグの原因は何かな?');
INSERT INTO Comments (comment_id, bug_id, author, comment_date, comment) VALUES (DEFAULT, 1, 2, now(), 'ヌルポインターのせいじゃないかな?');
INSERT INTO Comments (comment_id, bug_id, author, comment_date, comment) VALUES (DEFAULT, 1, 1, now(), 'そうじゃないよ。それは確認済みだ。');
INSERT INTO Comments (comment_id, bug_id, author, comment_date, comment) VALUES (DEFAULT, 1, 3, now(), '無効なインプットを調べてみたら?');
INSERT INTO Comments (comment_id, bug_id, author, comment_date, comment) VALUES (DEFAULT, 1, 2, now(), 'そうか、バグの原因はそれだな。');
INSERT INTO Comments (comment_id, bug_id, author, comment_date, comment) VALUES (DEFAULT, 1, 1, now(), 'よし、じゃあチェック機能を追加して もらえるかな?');
INSERT INTO Comments (comment_id, bug_id, author, comment_date, comment) VALUES (DEFAULT, 1, 3, now(), '了解。修正したよ。');

INSERT INTO TreePaths (ancestor, descendant) VALUES (1, 1);
INSERT INTO TreePaths (ancestor, descendant) VALUES (1, 2);
INSERT INTO TreePaths (ancestor, descendant) VALUES (1, 3);
INSERT INTO TreePaths (ancestor, descendant) VALUES (1, 4);
INSERT INTO TreePaths (ancestor, descendant) VALUES (1, 5);
INSERT INTO TreePaths (ancestor, descendant) VALUES (1, 6);
INSERT INTO TreePaths (ancestor, descendant) VALUES (1, 7);
INSERT INTO TreePaths (ancestor, descendant) VALUES (2, 2);
INSERT INTO TreePaths (ancestor, descendant) VALUES (2, 3);
INSERT INTO TreePaths (ancestor, descendant) VALUES (3, 3);
INSERT INTO TreePaths (ancestor, descendant) VALUES (4, 4);
INSERT INTO TreePaths (ancestor, descendant) VALUES (4, 5);
INSERT INTO TreePaths (ancestor, descendant) VALUES (4, 6);
INSERT INTO TreePaths (ancestor, descendant) VALUES (4, 7);
INSERT INTO TreePaths (ancestor, descendant) VALUES (5, 5);
INSERT INTO TreePaths (ancestor, descendant) VALUES (6, 6);
INSERT INTO TreePaths (ancestor, descendant) VALUES (6, 7);
INSERT INTO TreePaths (ancestor, descendant) VALUES (7, 7);
```

# まとめ

- エントリーと関連構造で成り立つ
- 要件によって、設計を決めると良い

# 感想

- やっぱり、やったことある。けれど、そこまで大変ではなかった記憶があって、ブログのカテゴリーなどで使ったぐらいだからかな？
- 閉包テーブルがありなのかな、と思うけれど、Railsでどうするんだろう？（面倒そう、調べていないけれど）

[![](https://images-na.ssl-images-amazon.com/images/I/41qHKrFZi0L._SX389_BO1,204,203,200_.jpg)](https://www.amazon.co.jp/dp/4873115892/)