データベース設計 〜 マスタデータを含めて、全ての履歴を残したいという要望
データベースの「正規化」を学んで間もない頃に実際のデータベース設計をやってみて、悩んだことをお話をしたいと思います。
理想と現実の壁にぶち当たる
下記の属性を持つ「売上ファイル」を考えてみます。
売上ファイル ---------------- 売上日 顧客名 住所 担当者名 商品名 単価 数量
これを第3正規形にすると、こんな感じになります。
売上テーブル ---------------- 売上コード 売上日 顧客コード 売上明細テーブル ---------------- 売上コード 商品コード 数量 顧客マスタ ---------- 顧客コード 顧客名 住所 担当者名 商品マスタ ---------- 商品コード 商品名 単価
ところが、このままでは商品マスタの単価が変更されると、売上金額が変わってしまいます。そこで、売上明細テーブルにも「単価」列を追加し、売上日時点の単価を設定するようにしました。*1
それだけならまだ良かったのですが、下記の２つの問題を突きつけられました。
- 顧客や商品が削除されてしまうと、売上一覧を作成した時にその部分が空欄になってしまう。
- 売上を集計するときは、顧客名、住所、担当者名、商品名も売上時点のものであって欲しい。(お客様の要望)
壁を乗り越えるために
結局、さらに逆正規化を進めて、全てのマスタテーブルの列を売上テーブルと売上明細テーブルに組み込みました。もはや、最初の「売上ファイル」とほとんど同じ状態です。
その後、1点目の問題を解決するために、マスタテーブルに「論理削除フラグ」を持たせることを考えつきました。*2顧客や商品の削除を論理削除フラグの変更で対処すれば、売上を集計した時にその部分が空白になってしまうことはありません。
しかし、2点目の問題は最後まで解決できず、「マスタの変更によって売上一覧の顧客名、住所、担当者名、商品名が変わってしまうのは制限事項である」として、お客様に渋々納得していただきました。
どうしていればよかったか
当時と今ではマシン性能もデータベース性能も異なるので、今の考え方を当時にそのまま当てはめることはできませんが、今の自分ならこんな感じにすると思います。
売上テーブル ---------------- 売上コード 売上日 顧客履歴コード 売上明細テーブル ---------------- 売上明細コード 売上コード 商品履歴コード 数量 顧客マスタ ---------- 顧客コード 顧客履歴コード 顧客マスタ履歴 -------------- 顧客履歴コード 顧客コード 顧客名 住所 担当者名 商品マスタ ---------------- 商品コード 商品履歴コード 商品マスタ履歴 -------------- 商品履歴コード 商品コード 商品名 単価
こうすることで、第3正規形や整合性をほとんど崩さず要件を満たすことができます。
マスタの更新は、マスタ履歴テーブルへの INSERT と、マスタテーブルの履歴コードの UPDATE です。少し手間が増えますが、マスタの更新処理は単純なので１つ作成すれば後は横展開できるでしょう。売上一覧についても、マスタテーブルを結合せず、マスタ履歴テーブルを結合するだけで作成できます。
参考までに、これを DDL に落とすと下記のようになります。
CREATE TABLE cust_hist (
cust_hist_cd INTEGER,
cust_cd INTEGER,
cust_name VARCHAR(200),
address VARCHAR(500),
person_in_charge VARCHAR(200),
CONSTRAINT pk_cust_hist PRIMARY KEY ( cust_hist_cd ),
CONSTRAINT uk_cust_hist UNIQUE ( cust_cd, cust_hist_cd ));
CREATE TABLE cust (
cust_cd INTEGER,
cust_hist_cd INTEGER,
CONSTRAINT pk_cust PRIMARY KEY ( cust_cd ),
CONSTRAINT fk_cust FOREIGN KEY ( cust_cd, cust_hist_cd )
REFERENCES cust_hist ( cust_cd, cust_hist_cd ));
CREATE TABLE item_hist (
item_hist_cd DECIMAL,
item_cd DECIMAL,
unit_price DECIMAL,
CONSTRAINT pk_item_hist PRIMARY KEY ( item_hist_cd ),
CONSTRAINT uk_item_hist UNIQUE ( item_cd, item_hist_cd ));
CREATE TABLE item (
item_cd DECIMAL,
item_hist_cd DECIMAL,
CONSTRAINT pk_item PRIMARY KEY ( item_cd ),
CONSTRAINT fk_item FOREIGN KEY ( item_cd, item_hist_cd )
REFERENCES item_hist ( item_cd, item_hist_cd ));
CREATE TABLE sales (
sales_cd INTEGER,
sales_at TIMESTAMP,
cust_hist_cd INTEGER,
CONSTRAINT pk_sales PRIMARY KEY ( sales_cd ),
CONSTRAINT fk_cust_cust_hist FOREIGN KEY ( cust_hist_cd )
REFERENCES cust_hist );
CREATE TABLE sales_detail (
sales_detail_cd INTEGER,
sales_cd INTEGER,
item_hist_cd INTEGER,
quantity INTEGER,
CONSTRAINT pk_sales_detail PRIMARY KEY ( sales_detail_cd ),
CONSTRAINT fk_sales_detail_sales FOREIGN KEY ( sales_cd )
REFERENCES sales,
CONSTRAINT fk_sales_detail_item_hist FOREIGN KEY ( item_hist_cd )
REFERENCES item_hist );複雑な要件をどう実装するかについては意見が分かれるところですが、1つの方法として参考にしていただければと思います。
