こんにちは、ns-iedです。
今回、記事を投稿するにあたり何を書こうか迷いました（iOS系、Android系、色の話など）。どうせなら記事を書いている2021年12月あたりに出会った出来事を書こうと思いました。
結局データベースの話を書くことにしました。業務でデータベースに触れるのは３年くらい振りで、専門ではありません。データベースの世界では当たり前のトピックなのかも知れませんが、直近で「なるほど」と思ったので、これにしました。
結論から書くと「組織図など木構造のデータをDBに登録する際は、階層問い合わせを使用する構造にしよう」という話です。組織の階層がいくら増えようと、どう変わろうと、この形式にすれば設定する値を変えるだけなど、簡易な修正ですみます。
組織図は以下の図を使用して説明します。
最終的に欲しいテーブルは、指定した部署・チームの長が誰で、副長が誰というテーブルです。組織図のデータベースを作成することになった当初の構成は以下の通りのような形でした。本来、DirectorID、SubDirectorIDとチーム・部の紐付けは他の方法でJOINした方が良いですが、今回は省略して、既にJOINしてあることとします。
チームマスタ
| ID | GroupName | 本部ID | DirectorID | SubDirectorID |
| 1 | 技術チーム | 2 | 5 | |
| 2 | 設計チーム | 2 | 6 | 12 |
| 3 | 法務チーム | 3 | 7 | |
| 4 | 経理チーム | 3 | 8 | |
| 5 | 総務チーム | 3 | 9 |
部マスタ
| ID | GroupName | DirectorID | SubDirectorID |
| 1 | 営業部 | 2 | 10 |
| 2 | 技術本部 | 3 | |
| 3 | 管理本部 | 4 | 11 |
人事マスタ
| ID | Name |
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| 5 | E |
| 6 | F |
| 7 | G |
| 8 | H |
| 9 | I |
| 10 | J |
| 11 | K |
| 12 | L |
ただ、このテーブル構成では組織変更があった場合など、組織階層が増えた場合に対応出来ないという問題があり少し困っていました。
そんな時、弊社のDB神からお告げがありました。「階層クエリを使用するんだよ」と。この、ありがたい一言で全てが変わりました。
階層問い合わせを使用すれば。組織の階層がいくら増えたとしても簡単なメンテナンスで対応出来ます。そこで作成したテーブルがこちら。
組織マスタ
| ID | GroupName | ParentID | DirectorID | SubDirectorID |
| 1 | 株式会社A | null | 1 | |
| 2 | 営業部 | 1 | 2 | 10 |
| 3 | 技術本部 | 1 | 3 | |
| 4 | 管理本部 | 1 | 4 | 11 |
| 5 | 技術チーム | 3 | 5 | |
| 6 | 設計チーム | 3 | 6 | 12 |
| 7 | 法務チーム | 4 | 7 | |
| 8 | 経理チーム | 4 | 8 | |
| 9 | 総務チーム | 4 | 9 |
再帰的問い合わせを使用したSQLはこちらです。
SQL Server を使用する場合は 「WITH r AS」と書き、その他のDBでは「WITH recursive r AS」と記載するようです。今回はSQL Serverの文法で記載します。
WITH r AS ( SELECT * FROM DepartmentMaster WHERE DepartmentName = '設計チーム' UNION ALL SELECT dm.* FROM DepartmentMaster AS DM, r WHERE DM.ID= r.ParentID ) SELECT * FROM r
| ID | GroupName | ParentID | DirectorID | SubDirectorID |
| 6 | 設計チーム | 3 | 6 | 12 |
| 3 | 技術本部 | 1 | 3 | NULL |
| 1 | Root | NULL | 1 | NULL |
上記のままだと、長と副長が分からないため人事マスタをJOINして最終的に得たいテーブルを作成することができました。
WITH r AS ( SELECT * FROM DepartmentMaster WHERE DepartmentName = '設計チーム' UNION ALL SELECT dm.* FROM DepartmentMaster AS DM, r WHERE DM.ID= r.ParentID ) SELECT GroupName, EM.Name AS DirectorName, SUB_EM.Name AS SubDirectorName FROM r LEFT JOIN EmployeeMaster AS EM ON r.DirectorID = EM.ID LEFT JOIN EmployeeMaster AS SUB_EM ON r.SubDirectorID = SUB_EM.ID
| GroupName | DirectorName | SubDirectorName | |
| 1 | 設計チーム | F | L |
| 2 | 技術本部 | D | NULL |
| 3 | Root | A | NULL |
お読みいただきありがとうございました。
