閉包テーブル (Closure Table)

閉包テーブル (Closure Table) は RDB で階層構造を表現する際に使われるデータモデルです。数年前にとある案件で使ったのですが、内容を忘れかけてるので復習します。備忘録。

閉包テーブルでは直接の親子関係だけでなく、階層全体の関係を持つ。閉包テーブルを利用すると階層構造をシンプルに表現できる。特にクエリがシンプルになるのが利点。ただし、階層全体の関係を持つため、階層が深くなるほどデータサイズは大きくなる。

ルートアクセス

ルート直下 (最上位階層) のフォルダを取得する場合、祖先フォルダIDにルートのIDを指定して、深さ 1 で検索する。ルートは特別な扱いになるため、IDには何かしらの固定値を用いる。

SELECT * FROM FolderPath as fp
JOIN Folder as f ON fp.祖先フォルダID = f.id
WHERE fp.祖先フォルダID = {ルート} AND fp.深さ = 1

サブフォルダアクセス

ルートアクセスと同様。祖先フォルダIDにカレントのフォルダIDを指定して、深さ 1 で検索する。

-- フォルダAを基点にする場合
SELECT * FROM FolderPath as fp
JOIN Folder as f ON fp.祖先フォルダID = f.id
WHERE fp.祖先フォルダID = {フォルダA} AND fp.深さ = 1

パンくずリスト

カレントフォルダまでのパス。子孫フォルダIDにカレントのフォルダIDを指定して検索する。深さを降順で並べ替えるとパンくずリストになる。

-- フォルダCを基点にする場合
SELECT f.* FROM FolderPath as fp
JOIN Folder as f ON fp.祖先フォルダID = f.id
WHERE fp.子孫フォルダID = {フォルダC}
ORDER BY fp.深さ DESC

フォルダ削除

閉包テーブルでは階層全体の関係を持つ。フォルダ削除では、子孫フォルダIDに対象のフォルダIDを持つすべての階層データを削除する。

-- フォルダDを削除する場合
DELETE FROM FolderPath as fp
WHERE fp.子孫フォルダID = {フォルダD}

フォルダ追加

閉包テーブルでは階層全体の関係を持つ。フォルダ追加では、子孫フォルダIDに対象のフォルダIDを持つすべての階層データを作成する。対象のフォルダ自身を深さ 0 として、親フォルダのパンくずリストでルートまで辿りながら深さを +1 する。以下はフォルダE配下にフォルダXを追加する場合。