Prompt
RDBMSで、
同種のものが自身に入っている(例: ディレクトリ)
自身と繋がっている(例: コメントへの返信コメント)
ことを表現するにはどうするのが一般的？
おう、面白そうな質問だね！👍 RDBMSで、同種のものが自身を内包するような階層構造や、同種のものが互いに参照しあうような再帰的な関係性を表現する方法だね。これは「自己参照リレーションシップ」って呼ばれてて、データベース設計ではよく使うパターンだよ。
自己参照リレーションシップ
これを実現する一番一般的な方法は、テーブル内に自分自身を参照する外部キー（Foreign Key）を設けることだよ。
たとえば、「ディレクトリ」や「コメントの返信」を例に考えてみよう。
ディレクトリ構造の表現
ファイルシステムみたいな階層構造をデータベースで表現したい場合だね。この場合、directoriesテーブルにparent_idというカラムを追加するんだ。このparent_idは、同じdirectoriesテーブルのid（主キー）を参照する外部キーになる。
SQL
CREATE TABLE directories (
id INT PRIMARY KEY,
name VARCHAR(255),
parent_id INT, -- 自分自身を参照する外部キー
FOREIGN KEY (parent_id) REFERENCES directories(id)
);
-
ルートディレクトリ: ルートディレクトリには親ディレクトリがないから、
parent_idにNULLを格納する。 -
サブディレクトリ: サブディレクトリには親ディレクトリの
idをparent_idに格納する。
この設計だと、以下のようにデータを挿入できる。
SQL
INSERT INTO directories (id, name, parent_id) VALUES
(1, 'root', NULL),
(2, 'documents', 1), -- 'documents'の親は'root'
(3, 'photos', 2), -- 'photos'の親は'documents'
(4, 'videos', 1); -- 'videos'の親は'root'
コメントへの返信の表現
「コメント」と「そのコメントへの返信」っていう関係もこれと全く同じパターンだ。commentsテーブルにparent_comment_idという外部キーを追加して、同じcommentsテーブルのidを参照させる。
SQL
CREATE TABLE comments (
id INT PRIMARY KEY,
content TEXT,
timestamp TIMESTAMP,
parent_comment_id INT, -- 自身を参照する外部キー
FOREIGN KEY (parent_comment_id) REFERENCES comments(id)
);
-
トップレベルのコメント: 返信ではない最初のコメントは、
parent_comment_idにNULLを格納する。 -
返信コメント: どのコメントへの返信かを示すため、返信先のコメントの
idをparent_comment_idに格納する。
この設計を使えば、SQLで簡単に階層構造を辿ったり、特定の階層にある要素を取得したりできるよ。
SQLでのクエリ
SQLで再帰的なクエリを行うには、**共通テーブル式（CTE）**という機能が便利だ。特にWITH RECURSIVE句を使うと、階層構造を上から下へ、または下から上へ辿ってデータを取得できる。
たとえば、photosディレクトリ（id=3）の親ディレクトリを全部取得したい場合は、こんなクエリが使える。
SQL
WITH RECURSIVE parent_path AS (
SELECT id, name, parent_id
FROM directories
WHERE id = 3
UNION ALL
SELECT d.id, d.name, d.parent_id
FROM directories d
JOIN parent_path pp ON d.id = pp.parent_id
)
SELECT * FROM parent_path;
SQLは奥が深いけど、こういう機能を使いこなせるとめちゃくちゃ面白いよね！😎
自己参照リレーションシップは、データベース設計の基本中の基本だから、ぜひマスターしておこう！分からないことがあったら、いつでも気軽に聞いてね！