9-3 閉包テーブルモデル

例

code:sql

CREATE TABLE OrgChart (

emp VARCHAR(32) PRIMARY KEY,

role VARCHAR(32) NOT NULL,

tree_id INT UNIQUE NOT NULL

);

CREATE TABLE Closure (

parent INT NOT NULL,

child INT NOT NULL,

CONSTRAINT pk_Closure PRIMARY KEY (parent, child),

CONSTRAINT fk_parent FOREIGN KEY (parent) REFERENCES OrgChart (tree_id),

CONSTRAINT fk_child FOREIGN KEY (child) REFERENCES OrgChart (tree_id)

);

table:OrgChart

emp（社員） role（役職） tree_id

山田 社長 1

佐藤 部長 2

田中 部長 3

後藤 課長 4

内藤 課長 5

小野 課長 6

大野 ヒラ 7

table:Clousure

parent child

1 1

1 2

1 3

1 4

1 5

1 6

1 7

2 2

3 3

3 4

...... ......

Closureが、各ノードそれぞれをルートとした配下ノードを網羅している

閉包テーブルモデルによる検索

例

田中さんをルートとしたノード（社員）全てを取得

code:sql

SELECT O2.emp

FROM (

SELECT O.emp, C.child, O.tree_id

FROM OrgChart O INNER JOIN Closure C

ON O.tree_id = C.parent

WHERE O.emp = '田中'

) TMP

INNER JOIN OrgChart O2

ON O2.tree_id = TMP.child;

閉包テーブルモデルによる更新

SQL文のロジックは難しくないが、データの整合性を保つために注意が必要

例

大野さんに山下さんという部下ができた

code:sql

INSERT INTO OrgChart VALUES('山下', 'ヒラ', 8);

INSERT INTO Closure

VALUES(7, (SELECT tree_id FROM OrgChart WHERE emp = '山下'));

INSERT INTO Closure

VALUES(4, (SELECT tree_id FROM OrgChart WHERE emp = '山下'));

INSERT INTO Closure

VALUES(3, (SELECT tree_id FROM OrgChart WHERE emp = '山下'));

INSERT INTO Closure

VALUES(1, (SELECT tree_id FROM OrgChart WHERE emp = '山下'));

INSERT INTO Closure

VALUES(

(SELECT tree_id FROM OrgChart WHERE emp = '山下',

(SELECT tree_id FROM OrgChart WHERE emp = '山下')

);