SQLアンチパターンを読み始めたので、1つ１つ書いてのメモです

目的

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

アンチパターン

常に親のみに依存する
親のIDをいれる
「隣接リスト」という
ノードの追加、サブツリーへの移動は簡単
ノードの削除は大変

用いてもいいパターン

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

解決策

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

経路列挙モデル

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

入れ子集合モデル

よくわからない...

閉包テーブルモデル

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

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でどうするんだろう？（面倒そう、調べていないけれど）