部署マスタの設計について考えた
背景と目的
原料・製品のスペックを管理するアプリケーションを開発する上で、「どの部署が登録したレコードか」を管理する必要がありました。
一見シンプルに作れそうに思える部署マスタですが、組織変更を頻繁に行う企業の場合、設計を誤ると組織改変のたびにデータの整合性が崩れ、運用が破綻してしまいます。
※プロフィールに書いているとおり、私はIT部門の人間ではありません。しかしIT部門が壊滅的な会社の場合、IT部門でない人間がシャドーITを作って回していかなければならないことも多いです。
こういったIT部門が壊滅的な企業のためのコンサルティングサービスを現在開発中です。
ここの例では、無印良品のような日用品雑貨と食品を扱う会社を想像してください。
無印良品の内部組織はわかりませんが、日用品雑貨と食品では、対応しなければならない法令や開発システムが大いに異なるので、扱う部門は分けられているはずです。
1. シンプルな構成（モックアップ用）
サンプルデータ
まずは最もシンプルな構成として、以下の部署マスタを考える。
| dept_id | dept_group_id | dept_code | dept_name | dept_name_en | level | parent_dept_id | valid_from | valid_to |
|---|---|---|---|---|---|---|---|---|
| 1 | 1 | RD-HQ | 商品開発本部 | Product Development Headquarters | 1 | NULL | 2024-04-01 | NULL |
| 2 | 2 | DG-DIV | 日用品雑貨事業部 | Daily Goods Division | 2 | 1 | 2024-04-01 | NULL |
| 3 | 3 | FD-DIV | 食品事業部 | Food & Beverage Division | 2 | 1 | 2024-04-01 | NULL |
| 4 | 4 | ST-DEV | 文房具開発部 | Stationery Development Dept. | 3 | 2 | 2024-04-01 | NULL |
| 5 | 5 | KT-DEV | キッチン用品開発部 | Kitchen Products Development Dept. | 3 | 2 | 2024-04-01 | NULL |
| 6 | 6 | CS-DEV | ビューティー・ケア開発部 | Beauty & Care Development Dept. | 3 | 2 | 2024-04-01 | NULL |
| 7 | 7 | IN-DEV | インテリア・収納開発部 | Interior & Storage Development Dept. | 3 | 2 | 2024-04-01 | NULL |
| 8 | 8 | DG-QA | 日用品品質保証部 | Daily Goods Quality Assurance Dept. | 3 | 2 | 2024-04-01 | NULL |
| 9 | 9 | FD-DEV | 食品開発部 | Food Development Dept. | 3 | 3 | 2024-04-01 | NULL |
| 10 | 10 | FD-QA | 食品品質保証部 | Food Quality Assurance Dept. | 3 | 3 | 2024-04-01 | NULL |
階層構造をツリーで表すと以下のようになる。
商品開発本部
├── 日用品雑貨事業部
│ ├── 文房具開発部
│ ├── キッチン用品開発部
│ ├── ビューティー・ケア開発部
│ ├── インテリア・収納開発部
│ └── 日用品品質保証部
└── 食品事業部
├── 食品開発部
└── 食品品質保証部
解説：なぜ末端部署だけでなく全階層をレコードにするのか
部署マスタの設計でよく見られるアプローチは、末端の部署（最下位の部署）だけをレコードとして持ち、それぞれに上位の部門・本部を属性として持たせる方法
-- 末端部署のみを持つ方式（非推奨）
文房具開発部 | 事業部=日用品雑貨事業部 | 本部=商品開発本部
キッチン用品開発部 | 事業部=日用品雑貨事業部 | 本部=商品開発本部
食品開発部 | 事業部=食品事業部 | 本部=商品開発本部
一見シンプルに見えますが、この設計には致命的な欠点があります。
階層が変わるとテーブル定義を変える必要がある。 グループ会社の追加などで階層が1段増えた場合、カラムを追加しなければならない。
名称変更時に大量の UPDATE が走る。 「日用品雑貨事業部」が「生活用品事業部」に改称された場合、その事業部に属するすべての末端部署レコードを更新しなければならない。
これに対して、全階層をレコードとして持ち、parent_dept_idで親子関係を表現する自己参照テーブルにすると、これらの問題が解消されます。
- 事業部・本部の名称変更が 1レコードの修正 で完結する
- 階層が何段になっても対応できる
- 部署の新設・廃止・移管がデータ操作だけで完結し、DDL変更が不要
なお、このモックアップ段階では dept_group_id の値を dept_id と同じ値にしています。
これは次節で説明する組織変更への対応のための「予約済みカラム」であり、将来の拡張時に定義変更なしでスムーズに移行するための布石です。
2. 組織変更に対応できるようにする
サンプルデータ
以下は、キッチン用品開発部とインテリア・収納開発部が統合されて「生活用品開発部」に改編されたケースです。
-- 旧部署を廃止（valid_to を設定）
UPDATE departments SET valid_to = '2025-09-30' WHERE dept_id IN (5, 7);
-- 新部署を追加
INSERT INTO departments VALUES
(11, 5, 'LF-DEV', '生活用品開発部', 'Lifestyle Products Development Dept.', 3, 2, '2025-10-01', NULL);
統合後のデータは以下のようになる。
| dept_id | dept_group_id | dept_name | valid_from | valid_to |
|---|---|---|---|---|
| 5 | 5 | キッチン用品開発部 | 2024-04-01 | 2025-09-30 |
| 7 | 7 | インテリア・収納開発部 | 2024-04-01 | 2025-09-30 |
| 11 | 5 | 生活用品開発部 | 2025-10-01 | NULL |
解説：dept_group_id で継承関係を表現する
有効期間（valid_from / valid_to）を持つだけでは、「この部署は過去のあの部署の後継である」という継承関係が表現できない。
シンプルな解決策として successor_dept_id（後継部署ID）を持つ方法もあるが、これは 1対1の継承しか表現できない。2つの部署が統合されて1つになるケースに対応できない。
そこで dept_group_id を導入する。同じ dept_group_id を持つレコードが「同一系譜の部署」であることを示す。
dept_id=5 (キッチン用品開発部) dept_group_id=5 ←── 同じ系譜
dept_id=11 (生活用品開発部) dept_group_id=5 ←── 同じ系譜
これにより、「生活用品開発部が登録したスペックデータ」を遡ると、組織改変前の「キッチン用品開発部が登録したスペックデータ」まで辿れるようになる。
なお、dept_group_id を多対多の関係で管理したい場合（例：インテリア・収納開発部の系譜も生活用品開発部に紐づけたい場合）は、中間テーブルに切り出すことで完全に対応できる。モックアップ段階では単一カラムで十分だが、将来的にそのような要件が生じた場合の移行先として意識しておくとよい。
3. テーブル定義とCRUD
テーブル定義
CREATE TABLE departments (
dept_id INTEGER PRIMARY KEY,
dept_group_id INTEGER NOT NULL,
dept_code VARCHAR(10) NOT NULL UNIQUE,
dept_name VARCHAR(100) NOT NULL,
dept_name_en VARCHAR(100) NOT NULL,
level INTEGER NOT NULL,
parent_dept_id INTEGER REFERENCES departments(dept_id),
valid_from DATE NOT NULL,
valid_to DATE, -- NULL = 現在有効
CONSTRAINT chk_valid_period CHECK (valid_to IS NULL OR valid_to > valid_from)
);
データ追加（INSERT）
-- 初期データの投入
INSERT INTO departments
(dept_id, dept_group_id, dept_code, dept_name, dept_name_en, level, parent_dept_id, valid_from, valid_to)
VALUES
(1, 1, 'RD-HQ', '商品開発本部', 'Product Development Headquarters', 1, NULL, '2024-04-01', NULL),
(2, 2, 'DG-DIV', '日用品雑貨事業部', 'Daily Goods Division', 2, 1, '2024-04-01', NULL),
(3, 3, 'FD-DIV', '食品事業部', 'Food & Beverage Division', 2, 1, '2024-04-01', NULL),
(4, 4, 'ST-DEV', '文房具開発部', 'Stationery Development Dept.', 3, 2, '2024-04-01', NULL),
(5, 5, 'KT-DEV', 'キッチン用品開発部', 'Kitchen Products Development Dept.', 3, 2, '2024-04-01', NULL),
(6, 6, 'CS-DEV', 'ビューティー・ケア開発部', 'Beauty & Care Development Dept.', 3, 2, '2024-04-01', NULL),
(7, 7, 'IN-DEV', 'インテリア・収納開発部', 'Interior & Storage Development Dept.', 3, 2, '2024-04-01', NULL),
(8, 8, 'DG-QA', '日用品品質保証部', 'Daily Goods Quality Assurance Dept.', 3, 2, '2024-04-01', NULL),
(9, 9, 'FD-DEV', '食品開発部', 'Food Development Dept.', 3, 3, '2024-04-01', NULL),
(10, 10, 'FD-QA', '食品品質保証部', 'Food Quality Assurance Dept.', 3, 3, '2024-04-01', NULL);
組織変更（UPDATE + INSERT）
-- 旧部署を廃止
UPDATE departments
SET valid_to = '2025-09-30'
WHERE dept_id IN (5, 7);
-- 新部署を追加（dept_group_id はキッチン用品開発部の系譜を引き継ぐ）
INSERT INTO departments
(dept_id, dept_group_id, dept_code, dept_name, dept_name_en, level, parent_dept_id, valid_from, valid_to)
VALUES
(11, 5, 'LF-DEV', '生活用品開発部', 'Lifestyle Products Development Dept.', 3, 2, '2025-10-01', NULL);
データ取得（SELECT）
現在有効な部署一覧を取得する
SELECT *
FROM departments
WHERE valid_to IS NULL
ORDER BY level, dept_id;
特定の日時点で有効だった部署一覧を取得する（過去参照）
SELECT *
FROM departments
WHERE valid_from <= '2025-06-01'
AND (valid_to IS NULL OR valid_to >= '2025-06-01')
ORDER BY level, dept_id;
ある部署の系譜（過去から現在まで）を取得する
SELECT *
FROM departments
WHERE dept_group_id = 5 -- キッチン用品開発部〜生活用品開発部の系譜
ORDER BY valid_from;
親部署を辿って全階層を取得する（再帰クエリ）
WITH RECURSIVE dept_tree AS (
-- 起点：対象の部署
SELECT dept_id, dept_name, parent_dept_id, 1 AS depth
FROM departments
WHERE dept_id = 11 -- 生活用品開発部
AND valid_to IS NULL
UNION ALL
-- 再帰：親を辿る
SELECT d.dept_id, d.dept_name, d.parent_dept_id, dt.depth + 1
FROM departments d
INNER JOIN dept_tree dt ON d.dept_id = dt.parent_dept_id
WHERE d.valid_to IS NULL
)
SELECT * FROM dept_tree ORDER BY depth DESC;
4. 製品マスタとの関連づけ
基本的な関連づけ
スペック管理アプリでは、製品マスタに「どの部署が担当しているか」を持たせる。
CREATE TABLE products (
product_id INTEGER PRIMARY KEY,
product_code VARCHAR(20) NOT NULL UNIQUE,
product_name VARCHAR(200) NOT NULL,
dept_id INTEGER NOT NULL REFERENCES departments(dept_id),
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
dept_id は登録時点で有効な部署のIDを格納する。これにより、登録時の組織状態をそのまま記録できる。
過去データの参照
組織改変後に「キッチン用品開発部が登録した製品の一覧を見たい」というケースを考える。dept_group_id を使えば、旧部署と新部署をまとめて検索できる。
-- 系譜をまとめて検索（旧・キッチン用品開発部 〜 新・生活用品開発部）
SELECT p.*
FROM products p
INNER JOIN departments d ON p.dept_id = d.dept_id
WHERE d.dept_group_id = 5;
dept_group_id がなければ、廃止された dept_id を事前に調べて IN 句に列挙するしかなく、組織改変のたびにアプリ側のクエリを修正する羽目になる。
注意点：登録時点の組織を保持する
製品マスタは登録時点の dept_id を保持する。これは意図的な設計だ。
たとえば、キッチン用品開発部が2024年に登録したスペックデータは、2025年の組織改変後も dept_id = 5（キッチン用品開発部）のまま保持される。「誰が（どの組織が）、いつ登録したか」という事実は変えてはならない。
現在の組織名で表示したい場合はアプリ側で dept_group_id を介して最新レコードを引くか、表示用の名称解決ロジックを別途設ける。
-- 製品の登録部署を、現在の組織名で表示する
SELECT
p.product_name,
current_dept.dept_name AS current_dept_name, -- 現在の組織名
original_dept.dept_name AS original_dept_name -- 登録時の組織名
FROM products p
INNER JOIN departments original_dept
ON p.dept_id = original_dept.dept_id
INNER JOIN departments current_dept
ON original_dept.dept_group_id = current_dept.dept_group_id
AND current_dept.valid_to IS NULL;
まとめ
| 設計要素 | 目的 |
|---|---|
自己参照テーブル（parent_dept_id） |
階層構造を柔軟に表現。DDL変更なしで階層の増減に対応 |
有効期間（valid_from / valid_to） |
組織改変の履歴を保持。過去時点の状態を正確に参照できる |
dept_group_id |
部署の継承関係（統廃合・改称）を表現。系譜をまたいだ検索を可能にする |
製品マスタは登録時の dept_id を保持 |
「いつ・どの組織が登録したか」という事実を不変として記録する |
組織改変はシステムの外側で起きる事象だが、その影響をデータ設計でどこまで吸収できるかが、長期運用できるアプリの鍵になる。
Discussion
普通、複数部署で商品開発または管理する場合、商品カテゴリ別に担当部署がいるから商品マスタには商品カテゴリをつけといて、商品カテゴリマスタに担当部署コードを付けると思うよ。
そうでなければ部門変更、特に部門分裂が起きたときに、一括で変更することはほぼできず、商品単位でメンテなんて地獄図、想像に難しくない。