RDBにおけるネスト可能なコメント機能のテーブル設計

本ドキュメントでは、リレーショナルデータベースでネスト可能なコメント機能を実装するための2つのアプローチを解説します。

前提条件

コメント対象のコンテンツは content_id で参照（詳細設計は省略）
ユーザー情報は考慮しない
木構造の扱い方にフォーカス

第一選択：隣接リストモデル + 再帰CTE

最も古典的かつ、現代では再帰CTEのサポートにより推奨されるモデルです。

テーブル定義

CREATE TABLE comments (
    comment_id    SERIAL PRIMARY KEY,
    content_id    BIGINT NOT NULL,
    parent_id     BIGINT,
    body          TEXT NOT NULL,
    created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_comments_parent
        FOREIGN KEY (parent_id) REFERENCES comments(comment_id)
        ON DELETE CASCADE
);

-- インデックス
CREATE INDEX idx_comments_content_id ON comments(content_id);
CREATE INDEX idx_comments_parent_id ON comments(parent_id);

ER図

サンプルデータ

INSERT INTO comments (comment_id, content_id, parent_id, body) VALUES
(1, 100, NULL,  'この記事とても参考になりました！'),
(2, 100, 1,     '同感です。特にパフォーマンスの部分が良かった。'),
(3, 100, 2,     'ベンチマーク結果も載せてほしいですね。'),
(4, 100, 1,     '初心者にはちょっと難しかったかも...'),
(5, 100, 4,     '公式ドキュメントを先に読むといいですよ。'),
(6, 100, 4,     '入門編の記事もあるので見てみてください。'),
(7, 100, 6,     'リンクありがとうございます！');

検索操作

特定コンテンツの全コメントをツリー構造で取得（深さ付き）

WITH RECURSIVE comment_tree (comment_id, content_id, parent_id, body, created_at, depth, path) AS (
    -- アンカーメンバー（ルートノード）
    SELECT
        c.comment_id,
        c.content_id,
        c.parent_id,
        c.body,
        c.created_at,
        1 AS depth,
        ARRAY[c.comment_id] AS path
    FROM comments c
    WHERE c.content_id = 100 AND c.parent_id IS NULL

    UNION ALL

    -- 再帰メンバー（子ノード）
    SELECT
        c.comment_id,
        c.content_id,
        c.parent_id,
        c.body,
        c.created_at,
        ct.depth + 1,
        ct.path || c.comment_id
    FROM comments c
    INNER JOIN comment_tree ct ON c.parent_id = ct.comment_id
)
SELECT
    comment_id,
    parent_id,
    body,
    depth,
    path
FROM comment_tree
ORDER BY path;

結果：

comment_id	parent_id	body	depth	path
1	NULL	この記事とても参考になりました！	1	1
2	1	同感です。特にパフォーマンスの部分が良かった。	2	2
3	2	ベンチマーク結果も載せてほしいですね。	3	3
4	1	初心者にはちょっと難しかったかも...	2	4
5	4	公式ドキュメントを先に読むといいですよ。	3	5
6	4	入門編の記事もあるので見てみてください。	3	6
7	6	リンクありがとうございます！	4	7

特定コメントの部分木を取得

WITH RECURSIVE subtree (comment_id, parent_id, body, depth) AS (
    SELECT comment_id, parent_id, body, 1 AS depth
    FROM comments
    WHERE comment_id = 4  -- 起点となるコメント

    UNION ALL

    SELECT c.comment_id, c.parent_id, c.body, st.depth + 1
    FROM comments c
    INNER JOIN subtree st ON c.parent_id = st.comment_id
)
SELECT * FROM subtree;

特定コメントの先祖（親をたどる）を取得

WITH RECURSIVE ancestors (comment_id, parent_id, body, depth) AS (
    SELECT comment_id, parent_id, body, 1 AS depth
    FROM comments
    WHERE comment_id = 7  -- 起点となるコメント

    UNION ALL

    SELECT c.comment_id, c.parent_id, c.body, a.depth + 1
    FROM comments c
    INNER JOIN ancestors a ON c.comment_id = a.parent_id
)
SELECT * FROM ancestors ORDER BY depth DESC;

ルートノードの取得

SELECT * FROM comments
WHERE content_id = 100 AND parent_id IS NULL;

リーフノード（返信のないコメント）の取得

SELECT c.*
FROM comments c
WHERE c.content_id = 100
  AND NOT EXISTS (
      SELECT 1 FROM comments child
      WHERE child.parent_id = c.comment_id
  );

更新操作

コメントの追加（リーフノード）

INSERT INTO comments (content_id, parent_id, body)
VALUES (100, 7, 'ありがとう！');

コメントの移動（別の親に付け替え）

UPDATE comments
SET parent_id = 1, updated_at = CURRENT_TIMESTAMP
WHERE comment_id = 6;

コメントの削除（サブツリーごと削除）

ON DELETE CASCADE を設定しているため、親を削除すると子孫も自動削除されます。

DELETE FROM comments WHERE comment_id = 4;
-- comment_id 4, 5, 6, 7 がすべて削除される

コメントの削除（子を昇格させる）

子コメントを親に付け替えてから削除します。

-- 1. 子を親の親に付け替え
UPDATE comments
SET parent_id = (SELECT parent_id FROM comments WHERE comment_id = 6)
WHERE parent_id = 6;

-- 2. 対象コメントを削除
DELETE FROM comments WHERE comment_id = 6;

MySQL での注意点

MySQLでは ARRAY 型がないため、パス表現には CONCAT を使用します。

重要: IDを単純に文字列結合すると、桁上がり時にソート順が壊れます（例: '1,10' < '1,2'）。
LPAD でゼロ埋めして桁数を揃える必要があります。

WITH RECURSIVE comment_tree (comment_id, parent_id, body, depth, path) AS (
    SELECT
        comment_id,
        parent_id,
        body,
        1 AS depth,
        LPAD(CAST(comment_id AS CHAR), 10, '0') AS path
    FROM comments
    WHERE content_id = 100 AND parent_id IS NULL

    UNION ALL

    SELECT
        c.comment_id,
        c.parent_id,
        c.body,
        ct.depth + 1,
        CONCAT(ct.path, ',', LPAD(CAST(c.comment_id AS CHAR), 10, '0'))
    FROM comments c
    INNER JOIN comment_tree ct ON c.parent_id = ct.comment_id
)
SELECT * FROM comment_tree ORDER BY path;

結果例（正しいソート順）:

comment_id	path
1	0000000001
2	0000000001,0000000002
3	0000000001,0000000002,0000000003
4	0000000001,0000000004
10	0000000001,0000000004,0000000010

隣接リストモデルのメリット・デメリット

メリット	デメリット
テーブル構造がシンプル（1テーブル）	再帰CTEをサポートしないDBでは検索が困難
更新が対象ノードに局所化される	大規模な木での再帰計算パフォーマンスは未知数
外部キー制約で参照整合性を維持可能	再帰クエリの理解に学習コストがかかる
ノードの挿入・移動・削除が容易

第二選択：閉包テーブルモデル

再帰CTEが使えない環境や、検索パフォーマンスを重視する場合の選択肢です。

テーブル定義

-- コメント本体テーブル
CREATE TABLE comments (
    comment_id    SERIAL PRIMARY KEY,
    content_id    BIGINT NOT NULL,
    body          TEXT NOT NULL,
    created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 閉包テーブル（経路情報）
CREATE TABLE comment_tree_paths (
    ancestor      BIGINT NOT NULL,
    descendant    BIGINT NOT NULL,
    depth         INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (ancestor, descendant),
    CONSTRAINT fk_ancestor
        FOREIGN KEY (ancestor) REFERENCES comments(comment_id) ON DELETE CASCADE,
    CONSTRAINT fk_descendant
        FOREIGN KEY (descendant) REFERENCES comments(comment_id) ON DELETE CASCADE
);

-- インデックス
CREATE INDEX idx_comments_content_id ON comments(content_id);
CREATE INDEX idx_tree_paths_ancestor ON comment_tree_paths(ancestor);
CREATE INDEX idx_tree_paths_descendant ON comment_tree_paths(descendant);
CREATE INDEX idx_tree_paths_depth ON comment_tree_paths(depth);

ER図

サンプルデータ

-- コメント本体
INSERT INTO comments (comment_id, content_id, body) VALUES
(1, 100, 'この記事とても参考になりました！'),
(2, 100, '同感です。特にパフォーマンスの部分が良かった。'),
(3, 100, 'ベンチマーク結果も載せてほしいですね。'),
(4, 100, '初心者にはちょっと難しかったかも...'),
(5, 100, '公式ドキュメントを先に読むといいですよ。'),
(6, 100, '入門編の記事もあるので見てみてください。'),
(7, 100, 'リンクありがとうございます！');

-- 閉包テーブル（全経路情報）
INSERT INTO comment_tree_paths (ancestor, descendant, depth) VALUES
-- 自己参照（depth=0）
(1, 1, 0), (2, 2, 0), (3, 3, 0), (4, 4, 0), (5, 5, 0), (6, 6, 0), (7, 7, 0),
-- 1からの経路
(1, 2, 1), (1, 3, 2), (1, 4, 1), (1, 5, 2), (1, 6, 2), (1, 7, 3),
-- 2からの経路
(2, 3, 1),
-- 4からの経路
(4, 5, 1), (4, 6, 1), (4, 7, 2),
-- 6からの経路
(6, 7, 1);

閉包テーブルの内容：

ancestor	descendant	depth
1	1	0
1	2	1
1	3	2
1	4	1
1	5	2
1	6	2
1	7	3
2	2	0
2	3	1
...	...	...

検索操作

特定コメントの子孫をすべて取得

SELECT c.*
FROM comments c
INNER JOIN comment_tree_paths tp ON c.comment_id = tp.descendant
WHERE tp.ancestor = 4;

特定コメントの先祖をすべて取得

SELECT c.*
FROM comments c
INNER JOIN comment_tree_paths tp ON c.comment_id = tp.ancestor
WHERE tp.descendant = 7
ORDER BY tp.depth DESC;

特定コメントの直接の子を取得

SELECT c.*
FROM comments c
INNER JOIN comment_tree_paths tp ON c.comment_id = tp.descendant
WHERE tp.ancestor = 4 AND tp.depth = 1;

特定コメントの直接の親を取得

SELECT c.*
FROM comments c
INNER JOIN comment_tree_paths tp ON c.comment_id = tp.ancestor
WHERE tp.descendant = 7 AND tp.depth = 1;

各コメントの深さを取得

SELECT c.comment_id, c.body, COUNT(*) AS depth
FROM comments c
INNER JOIN comment_tree_paths tp ON c.comment_id = tp.descendant
WHERE c.content_id = 100
GROUP BY c.comment_id, c.body
ORDER BY depth;

ルートノードの取得

最も多くの子孫を持つノード、または depth=0 のみの自己参照を持つノード。

SELECT c.*
FROM comments c
WHERE c.content_id = 100
  AND NOT EXISTS (
      SELECT 1 FROM comment_tree_paths tp
      WHERE tp.descendant = c.comment_id AND tp.depth > 0
  );

リーフノードの取得

SELECT c.*
FROM comments c
WHERE c.content_id = 100
  AND NOT EXISTS (
      SELECT 1 FROM comment_tree_paths tp
      WHERE tp.ancestor = c.comment_id AND tp.depth > 0
  );

更新操作

コメントの追加

新規コメントを追加する際は、自己参照と全先祖への経路を追加します。

-- 1. コメント本体を挿入
INSERT INTO comments (content_id, body)
VALUES (100, 'ありがとう！')
RETURNING comment_id;  -- 例: 8 が返る

-- 2. 自己参照を追加
INSERT INTO comment_tree_paths (ancestor, descendant, depth)
VALUES (8, 8, 0);

-- 3. 親の全先祖への経路を追加（親が comment_id=7 の場合）
INSERT INTO comment_tree_paths (ancestor, descendant, depth)
SELECT tp.ancestor, 8, tp.depth + 1
FROM comment_tree_paths tp
WHERE tp.descendant = 7;

コメントの削除（サブツリーごと削除）

テーブル定義で ON DELETE CASCADE を設定しているため、comments テーブルから削除すれば comment_tree_paths の関連レコードは自動的に削除されます。

-- PostgreSQL: サブツリーのコメントIDを特定して削除
DELETE FROM comments
WHERE comment_id IN (
    SELECT descendant FROM comment_tree_paths WHERE ancestor = 4
);
-- comment_tree_paths の関連レコードは CASCADE で自動削除

MySQL での注意点: MySQLでは、DELETE対象のテーブルを同一クエリ内のサブクエリで参照できません（Error 1093）。JOIN構文を使用します。

-- MySQL: JOIN構文を使用
DELETE c FROM comments c
INNER JOIN comment_tree_paths tp ON c.comment_id = tp.descendant
WHERE tp.ancestor = 4;
-- comment_tree_paths の関連レコードは CASCADE で自動削除

サブツリーの移動

サブツリーを別の親に移動する場合は、経路の再構築が必要です。

-- comment_id=6 を comment_id=3 の子に移動する場合

-- PostgreSQL: 1. 移動対象サブツリーと元の先祖との経路を削除
DELETE FROM comment_tree_paths
WHERE descendant IN (
    SELECT descendant FROM comment_tree_paths WHERE ancestor = 6
)
AND ancestor IN (
    SELECT ancestor FROM comment_tree_paths
    WHERE descendant = 6 AND ancestor != descendant
);

-- 2. 新しい先祖との経路を追加
INSERT INTO comment_tree_paths (ancestor, descendant, depth)
SELECT supertree.ancestor, subtree.descendant, supertree.depth + subtree.depth + 1
FROM comment_tree_paths supertree
CROSS JOIN comment_tree_paths subtree
WHERE supertree.descendant = 3  -- 新しい親
  AND subtree.ancestor = 6;     -- 移動するサブツリーのルート

MySQL での注意点: MySQLではDELETE対象テーブルをサブクエリで参照できないため、JOIN構文を使用します。

-- MySQL: 1. 移動対象サブツリーと元の先祖との経路を削除
DELETE tp1 FROM comment_tree_paths tp1
INNER JOIN comment_tree_paths tp2 ON tp1.descendant = tp2.descendant
INNER JOIN comment_tree_paths tp3 ON tp1.ancestor = tp3.ancestor
WHERE tp2.ancestor = 6
  AND tp3.descendant = 6
  AND tp3.ancestor != tp3.descendant;

-- 2. 新しい先祖との経路を追加（PostgreSQLと同じ）
INSERT INTO comment_tree_paths (ancestor, descendant, depth)
SELECT supertree.ancestor, subtree.descendant, supertree.depth + subtree.depth + 1
FROM comment_tree_paths supertree
CROSS JOIN comment_tree_paths subtree
WHERE supertree.descendant = 3
  AND subtree.ancestor = 6;

閉包テーブルモデルのメリット・デメリット

メリット	デメリット
検索が高速でシンプル（再帰不要）	2つのテーブルの同期が必要
外部キー制約で参照整合性を維持可能	挿入・移動時に複数レコードの操作が必要
ノードが複数ツリーに所属可能	閉包テーブルのレコード数が多くなる
深さ情報を直接取得可能	更新ロジックがやや複雑

モデル比較まとめ

観点	隣接リストモデル	閉包テーブルモデル
テーブル数	1	2
検索（子孫取得）	再帰CTE必要	JOIN のみ
検索（先祖取得）	再帰CTE必要	JOIN のみ
直接の親子取得	シンプル	depth=1 で絞り込み
挿入	1レコード	複数レコード
削除（サブツリー）	CASCADE で自動	CASCADE で自動（コメント本体削除のみ）
移動	1レコード更新	経路の再構築必要
参照整合性	維持可能	維持可能
推奨ケース	更新頻度が高い場合	検索頻度が高い場合

推奨

第一選択：隣接リストモデル + 再帰CTE
- PostgreSQL 8.4+, MySQL 8.0+, SQL Server 2005+ など主要DBMSで利用可能
- 更新が局所化され、実装がシンプル
- 大規模データでのパフォーマンス検証は必要
第二選択：閉包テーブルモデル
- 再帰CTEが使えない環境
- 読み取り頻度が圧倒的に高く、更新が少ない場合
- 複雑なツリー操作（ノードの複数ツリー所属など）が必要な場合

補足：PostgreSQL の ltree 拡張

PostgreSQL を使用する場合、標準拡張機能の ltree も有力な選択肢です。

ltree とは

ltree は階層的なラベルツリーを表現するためのデータ型で、ファイルパスのようなドット区切りの文字列でツリー構造を表現します。GiST/GIN インデックスに対応しており、大規模データでも高速な検索が可能です。

使用例

-- 拡張機能の有効化
CREATE EXTENSION ltree;

-- テーブル定義
CREATE TABLE comments (
    comment_id    SERIAL PRIMARY KEY,
    content_id    BIGINT NOT NULL,
    path          ltree NOT NULL,
    body          TEXT NOT NULL,
    created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- GiSTインデックスの作成（高速な階層検索）
CREATE INDEX idx_comments_path ON comments USING GIST (path);

-- サンプルデータ
INSERT INTO comments (content_id, path, body) VALUES
(100, '1',         'この記事とても参考になりました！'),
(100, '1.2',       '同感です。特にパフォーマンスの部分が良かった。'),
(100, '1.2.3',     'ベンチマーク結果も載せてほしいですね。'),
(100, '1.4',       '初心者にはちょっと難しかったかも...'),
(100, '1.4.5',     '公式ドキュメントを先に読むといいですよ。');

-- 特定コメントの子孫を取得（高速）
SELECT * FROM comments WHERE path <@ '1.4';

-- 特定コメントの先祖を取得
SELECT * FROM comments WHERE path @> '1.4.5';

-- パスの深さを取得
SELECT *, nlevel(path) AS depth FROM comments;

ltree の特徴

観点	説明
検索性能	GiST/GINインデックスで高速（大規模データに強い）
パターンマッチ	`lquery` 型でワイルドカード検索が可能
シンプルさ	1テーブルで完結、パスの表現が直感的
制約	PostgreSQL 専用、パスの更新は手動管理が必要

推奨ケース

PostgreSQL を使用している
読み取り頻度が非常に高い
大規模な木構造（数万〜数百万ノード）を扱う
パターンマッチによる柔軟な検索が必要

目次