マスターテーブル更新の弊害

RDB(リレーショナルデータベース)は,正規化が基本ですが,正規化することによって,検索や結合が複雑になってしまう場合があります.

次図のような承認書をデータベース化することを考えてみます.

00.承認書

まずは,1つの承認書を1レコードとして考えます.正規化前の状態は次図のとおり.

01.正規化前

これを正規化すると,次の3つのテーブルに分解できます.

承認テーブル

02.承認1

部署テーブル

03.部署1

職員テーブル

04.職員

それぞれのテーブルの関係(リレーション)は,

  • 「承認」テーブルと「部署」テーブルは,部署IDと部署.IDで「多対1」の関係
  • 「部署」テーブルと「職員」テーブルは,職員IDと職員.IDで「多対1」の関係

となり,上記3つのテーブルを結合する場合,SQLは次のようになります.

SELECT 承認.ID, 承認日, 部署ID, 部署名, 職員ID, 職員名 
FROM 承認 INNER JOIN 部署 ON 部署ID = 部署.ID 
INNER JOIN 職員 ON 職員ID = 職員.ID;

05.承認結合1

特にここまでは何も問題ないと思います.

ここで,次のような2017/04/01付け人事異動があったとします.

  • 総務部長(01)の「田中 一郎(102)」が退任,後任は「吉田 茂雄(103)」となる.
  • 企画部長(03)の「山本 耕太(104)」が退任,後任は「田中 一郎(102)」となる.

これに伴い,「部署」テーブルは,次図のように更新となります.

06.部署2

そして,2017/04/13に,総務部長承認案件があったとします.「承認」テーブルが次のように更新となりました.

07.承認2

再度先ほどの結合SQLを実行してみますと,次のようになります.

08.承認結合2

ここで,マスターテーブルの更新による弊害が出てしまいます.問題としては...

  • 2017/01/25の件の承認者は「田中 直人(102)」総務部長となるべき
  • 2017/03/04の件の承認者は「山本 耕太(104)」企画部長となるべき

マスターテーブルを更新したばかりに,過去のデータまで変わってしまっています.

なんとか過去のある時点のデータを再現することはできないのでしょうか.

一つの方法として,マスターテーブルを更新するたびに,更新前のデータを別の履歴テーブルに保持する方法があります.

次のような「部署履歴」テーブルを用意します.

部署履歴テーブル

09.部署履歴

このテーブルには,部長を退任した職員の在任期間を記録します.

  • 「田中 直人(102)」は,総務部長(01)として,2016/04/01〜2017/03/31の期間在任していた.
  • 「山本 耕太(104)」は,企画部長(03)として,2016/04/01〜2017/03/31の期間在任していた.

この「部署履歴」テーブルと,「承認」テーブルの結合から,既に退任された分,即ち元部長が承認したレコードのみを取り出します.

SELECT 承認.ID, 承認日, 部署ID, 職員ID 
FROM 承認 INNER JOIN 部署履歴 
ON 承認.部署ID = 部署履歴.部署ID 
AND 承認日 BETWEEN 任期開始 AND 任期終了;

10.元承認部署長

これにより,

  • 2017/01/25承認分の承認者は「田中 直人(102)」総務部長
  • 2017/03/04承認分の承認者は「山本 耕太(104)」企画部長

と,その当時(過去)の実際の承認者のデータが結びつくことになります.

とりあえず,このクエリ結果をビュー「元承認部署長」(余り,良いテーブル名ではないですね…)に残します.

CREATE VIEW 元承認部署長 AS
SELECT 承認.ID, 承認日, 部署ID, 職員ID 
FROM 承認 INNER JOIN 部署履歴 
ON 承認.部署ID = 部署履歴.部署ID 
AND 承認日 BETWEEN 任期開始 AND 任期終了;

先ほどのSELECTクエリーの前に「CREATE VIEW 元承認部署長 AS」を入れるだけでOKです.

次に,この「元承認部署長」ビューを使用して,それ以外(NOT EXISTS)の(現在の)部長承認分のレコードをUNIONで結合させます.

SELECT * FROM 元承認部署長
UNION
SELECT 承認ID, 承認日, 承認.部署ID, 職員ID FROM 承認
INNER JOIN 部署 ON 承認.部署ID = 部署.ID
WHERE NOT EXISTS (
SELECT * FROM 元承認部署長 WHERE 承認ID = 元承認部署長.ID
) ORDER BY ID;

11.承認部署長

この結果を「承認部署長」ビューとして保存します.

CREATE VIEW 承認部署長 AS 
SELECT * FROM 元承認部署長
UNION
SELECT 承認.ID, 承認日, 部署ID, 職員ID FROM 承認
INNER JOIN 部署 ON 承認.部署ID = 部署.ID
WHERE NOT EXISTS (
SELECT * FROM 元承認部署長 WHERE 承認ID = 元承認部署長.ID
) ORDER BY ID;

これで,過去それぞれの承認時点での部署長の情報が組み合わされた「承認部署長」ビューができましたので,あとはこれと「職員」テーブルを結合して...

SELECT 承認部署長.ID, 承認日, 部署ID, 部署名, 承認部署長.職員ID, 職員名
FROM 承認部署長
INNER JOIN 部署 ON 部署ID = 部署.ID
INNER JOIN 職員 ON 承認部署長.職員ID = 職員.ID
ORDER BY ID;

12.承認結合3

これで,過去の承認時点での承認者が再現されたデータセットを得ることができました.

一箇所一事実によりデータの整合性を保つためには,正規化は必須です.
ですが,このようにマスターを更新したばかりに,過去のある時点のデータが再現できない問題が起こり,それを解消させるために,わざわざ履歴管理用のテーブルを準備して,ごにょごにょと複雑な結合をしなければならなくなり,またビューも多用してしまうので,パフォーマンスに問題がでてきます.

いっそのこと,MongoDBなどのドキュメント型DB(NoSQL)で管理してしまえば楽なのかなと,思ったりもしています.

もっと良い方法があるのかもしれません.以下,今回作成したデータベースの一通りのSQL文です.よろしければご参考まで.

DROP VIEW IF EXISTS `承認部署長`;
DROP VIEW IF EXISTS `元承認部署長`;
DROP TABLE IF EXISTS `承認`;
DROP TABLE IF EXISTS `部署`;
DROP TABLE IF EXISTS `職員`;
DROP TABLE IF EXISTS `部署履歴`;

CREATE TABLE `承認` (
  `ID` INT(11) NOT NULL,
  `承認日` DATE,
  `部署ID` CHAR(2),
  PRIMARY KEY (`ID`)
);

INSERT INTO `承認` VALUES (1,'2017-01-25','01');
INSERT INTO `承認` VALUES (2,'2017-02-03','02');
INSERT INTO `承認` VALUES (3,'2017-03-04','03');
INSERT INTO `承認` VALUES (4,'2017-03-15','02');
INSERT INTO `承認` VALUES (5,'2017-04-13','01');

SELECT * FROM `承認`;

CREATE TABLE `部署` (
  `ID` CHAR(2) NOT NULL,
  `部署名` VARCHAR(20),
  `職員ID` CHAR(3),
  PRIMARY KEY (`ID`)
);

INSERT INTO `部署` VALUES ('01','総務部','103');
INSERT INTO `部署` VALUES ('02','経理部','101');
INSERT INTO `部署` VALUES ('03','企画部','102');

SELECT * FROM `部署`;

CREATE TABLE `職員` (
  `ID` CHAR(3) NOT NULL,
  `職員名` VARCHAR(20),
  PRIMARY KEY (`ID`)
);

INSERT INTO `職員` VALUES ('101','佐藤 一郎');
INSERT INTO `職員` VALUES ('102','田中 直人');
INSERT INTO `職員` VALUES ('103','吉田 茂雄');
INSERT INTO `職員` VALUES ('104','山本 耕太');
INSERT INTO `職員` VALUES ('105','加藤 恭正');

SELECT * FROM `職員`;

SELECT 承認.ID, 承認日, 部署ID, 部署名, 職員ID, 職員名 
FROM 承認 INNER JOIN 部署 ON 部署ID = 部署.ID 
INNER JOIN 職員 ON 職員ID = 職員.ID;

CREATE TABLE `部署履歴` (
  `ID` INT(11) NOT NULL,
  `部署ID` CHAR(2),
  `職員ID` CHAR(3),
  `任期開始` DATE,
  `任期終了` DATE,
  PRIMARY KEY (`ID`)
);

INSERT INTO `部署履歴` VALUES (1, '01', '102', '2016-04-01', '2017-03-31');
INSERT INTO `部署履歴` VALUES (2, '03', '104', '2016-04-01', '2017-03-31');

SELECT * FROM 部署履歴;

CREATE VIEW 元承認部署長 AS
SELECT 承認.ID, 承認日, 承認.部署ID, 職員ID 
FROM 承認 INNER JOIN 部署履歴 
ON 承認.部署ID = 部署履歴.部署ID 
AND 承認日 BETWEEN 任期開始 AND 任期終了;

SELECT * FROM 元承認部署長;

CREATE VIEW 承認部署長 AS 
SELECT * FROM 元承認部署長
UNION
SELECT 承認.ID, 承認日, 部署ID, 職員ID FROM 承認
INNER JOIN 部署 ON 承認.部署ID = 部署.ID
WHERE NOT EXISTS (
SELECT * FROM 元承認部署長 WHERE 承認.ID = 元承認部署長.ID
) ORDER BY ID;

SELECT * FROM 承認部署長;

SELECT 承認部署長.ID, 承認日, 部署ID, 部署名, 承認部署長.職員ID, 職員名
FROM 承認部署長
INNER JOIN 部署 ON 部署ID = 部署.ID
INNER JOIN 職員 ON 承認部署長.職員ID = 職員.ID
ORDER BY ID;
広告

コメントを残す

以下に詳細を記入するか、アイコンをクリックしてログインしてください。

WordPress.com ロゴ

WordPress.com アカウントを使ってコメントしています。 ログアウト / 変更 )

Twitter 画像

Twitter アカウントを使ってコメントしています。 ログアウト / 変更 )

Facebook の写真

Facebook アカウントを使ってコメントしています。 ログアウト / 変更 )

Google+ フォト

Google+ アカウントを使ってコメントしています。 ログアウト / 変更 )

%s と連携中