閉方テーブル (Closure Table)

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

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

f:id:knt_mr:20200810160928p:plain

ルートアクセス

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

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

f:id:knt_mr:20200814014507p:plain

サブフォルダアクセス

ルートアクセスと同様。祖先フォルダ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

f:id:knt_mr:20200814014946p:plain

パンくずリスト

カレントフォルダまでのパス。子孫フォルダ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

f:id:knt_mr:20200814015643p:plain

フォルダ削除

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

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

f:id:knt_mr:20200814020724p:plain

フォルダ追加

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

f:id:knt_mr:20200814021102p:plain