古いSuicaから見た未来！ 高輪ゲートウェイ駅の謎からリレーショナルデータベースの論理設計を考える。
動機
「家を整理してたら古いSuicaが出てきて、2005年のデータを読み取ることに成功したが、はて？ 当時存在しない高輪ゲートウェイが出てきたぞ？」というツイートを見て、
「コードはそのままで、名称を変えるようなマスターの変更をした結果、古いトランザクションデータを参照したら最新の名称になってしまった」的な、あるあるネタか？と思ったら、
ツイートの続きで「高輪ゲートウェイ駅開業にあわせて未来への決断をおこなったJR東日本」に詳しい解説があり、駅順コードと駅名が
4:浜松町
5:空き番
6:田町
となっていて、5が空いてるので、ここに「高輪ゲートウェイ」を入れればよいのに、駅の順番を気にして
4:浜松町
5:田町
6:高輪ゲートウェイ
としたようです。
あー、これもソート順あるあるネタだ。ということで、リレーショナルデータベース(RDB)の論理設計についてちょこっと書きます。
コードをそのままソート順に使ってますか？
上記例だとシステムライフサイクルを考えてコードを飛び番にして設計していたようですが、ソート順が意図した形にならないので、コードと名称の関係を変えることで対応したようです。
おそらくこんな設計だったと想定しています。
業務ヒアリングのコツとRDB論理設計
長く運用保守しているシステムではあるあるネタです。「なんでソート順カラムを入れとかないんだよ」と。
業務要件のヒアリングでは、業務側担当者は「ソート順カラムを入れてください」なんて言わないので、こちらが気を利かせる必要があります。
「将来空き番がなくなったときにソート順で困らないようにソート順カラムをいれませんか？」とヒアリングします。
ただ、実装側からすると面倒です。毎度マスタテーブルとJOINして「ソート順」カラムでソートする必要があるので。
とはいえ、長い運用保守を考えると、「ソート順カラム」を入れるのを強くお勧めします。
安易に第3正規形を作ってませんか？
ここからは、別のRDB論理設計あるあるネタ。「コードはそのままで名称だけを変えた後に、過去のトランザクションデータを参照したら最新の名称になってしまった」というものです。
例えば、商品マスタと注文マスタを次のように論理設計しました。下図は第3正規形まで行ってます。
正規化のざっくり定義:
・非正規形：正規化していない。(←なんだこのイケてない定義は)
・第1正規形：列の繰り返しをなくす。
・第2正規形：主キーの一部に依存するデータを別テーブルに切り出す。
・第3正規形：主キーじゃない列に依存するデータを別テーブルに切り出す。
業務ヒアリングのコツとRDB論理設計①
一見これでよいのですが、システムライフサイクルのある時点で、商品コードはそのままで商品名が変わることがあります(個人の経験でしょと言われそうですが、コードと名称の関係で、コードはそのままで名称を変えたいというのは結構なケースでありました)。
業務側の意図は、注文した時点の商品名であって欲しいかもしれないです。業務要件ヒアリングでは、「今」に着目しがちですが、システムライフサイクルを考えると、「コードはそのままで名称を変えたくなる」ことがあります。
ですので、こちらが気を利かせる必要があります。「コードはそのままで、名称変更することはありますか？その際、名称変更は即時に反映したいですか？」とヒアリングします。
例えば、こんな論理設計があります。
トランザクションに名称を持ち込んでしまう設計です。これだと第3正規形に違反してますが、この設計、まぁまぁやります。ですが、次のようなケースもあるので、さらに業務ヒアリングが必要です。
業務ヒアリングのコツとRDB論理設計②
さらに、マスタ改廃でよくある業務要件として、「4月1日からの注文は新しい商品名を選択できるようにしたいんだけど」というものです。
上記のトランザクションに名称を持ちこむ論理設計で、この要件を実現しようとすると、4月1日のサービス開始前にレコードを登録するとか、システムを停止して登録作業をするとか、結構めんどくさいことになります。
次のように利用開始年月日を主キーに加えることで、レコードを履歴で持てるようにすると、この要件に対応できます。
業務側のヒアリングとしては、「マスタの登録を今すぐ行うけれど、その適用は未来の日付で行いたいですか？」とか「モデルチェンジがありますか？ある時点でコードはそのままで新旧名称が混在することはありますか？」とかいったヒアリングをします。
ただですね、実装側からするととにかく面倒です。JOINの条件にタイムスタンプとマスタの利用開始、終了年月を入れる必要があるので。
とはいえ、個人の経験でしょと言われそうですが、この利用開始年月日、利用停止年月日を入れる設計は結構やります。
覚書としてのRDB論理設計と実装
覚書として、自分としての定番のRDB論理設計と実装を以下に記します。
RDB論理設計
商品価格を履歴で管理できる商品マスタテーブルと、その商品マスタを参照する注文トランザクションテーブルです。
商品名と価格は利用開始年月日と利用終了年月日の期間で有効になり、利用開始年月日と利用終了年月日ペアでその期間を表します。現在の商品名と価格は利用終了年月日がNULLのレコードです。
実装
以下、ORACLEデータベースでテストしてます。
テーブルの作成
-- 商品マスタテーブル
DROP TABLE T_PRODUCT CASCADE CONSTRAINTS;
CREATE TABLE T_PRODUCT (
PRODUCT_ID DECIMAL(7, 0) NOT NULL,
START_DATE DATE NOT NULL,
END_DATE DATE,
PREV_START_DATE DATE NOT NULL,
SORT_RANK DECIMAL(7, 0) NOT NULL,
PRODUCT_NAME VARCHAR2(100) NOT NULL,
UNIT_PRC DECIMAL(7, 3) NOT NULL,
NOTE VARCHAR2(256),
CONSTRAINT ERR_T_PRODUCT_001 CHECK ( START_DATE < END_DATE ),
CONSTRAINT ERR_T_PRODUCT_002 CHECK ( START_DATE = PREV_START_DATE + INTERVAL '1' DAY ),
CONSTRAINT ERR_T_PRODUCT_003 CHECK ( UNIT_PRC > 0.0 ),
CONSTRAINT PK_T_PRODUCT PRIMARY KEY ( PRODUCT_ID,
START_DATE )
);
Insert into T_PRODUCT (PRODUCT_ID,START_DATE,END_DATE,PREV_START_DATE,SORT_RANK,PRODUCT_NAME,UNIT_PRC,NOTE) values (1,to_date('2023/04/01','yyyy/MM/DD'),to_date('2023/06/30','yyyy/MM/DD'),to_date('2023/03/31','yyyy/MM/DD'),1,'リンゴ',100,'古い単価');
Insert into T_PRODUCT (PRODUCT_ID,START_DATE,END_DATE,PREV_START_DATE,SORT_RANK,PRODUCT_NAME,UNIT_PRC,NOTE) values (1,to_date('2023/07/01','yyyy/MM/DD'),null,to_date('2023/06/30','yyyy/MM/DD'),1,'リンゴ',199,'新単価');
Insert into T_PRODUCT (PRODUCT_ID,START_DATE,END_DATE,PREV_START_DATE,SORT_RANK,PRODUCT_NAME,UNIT_PRC,NOTE) values (999,to_date('2023/04/01','yyyy/MM/DD'),null,to_date('2023/03/31','yyyy/MM/DD'),2,'みかん',50,'右オンリー');
COMMIT;
-- 注文トランザクションテーブル
DROP TABLE T_ORDER_ITEM CASCADE CONSTRAINTS;
CREATE TABLE T_ORDER_ITEM (
ORDER_ITEM_ID DECIMAL(7, 0) NOT NULL,
PRODUCT_ID DECIMAL(7, 0) NOT NULL,
ORDER_QTY DECIMAL(7, 0) NOT NULL,
ORDER_DATE DATE NOT NULL,
NOTE VARCHAR2(256),
CONSTRAINT PK_T_ORDER_ITEM PRIMARY KEY ( ORDER_ITEM_ID )
);
Insert into T_ORDER_ITEM (ORDER_ITEM_ID,PRODUCT_ID,ORDER_QTY,ORDER_DATE,NOTE) values (1,1,12,to_date('2023/06/30','yyyy/MM/DD'),'古い単価を参照');
Insert into T_ORDER_ITEM (ORDER_ITEM_ID,PRODUCT_ID,ORDER_QTY,ORDER_DATE,NOTE) values (2,1,13,to_date('2023/07/01','yyyy/MM/DD'),'現在単価を参照');
Insert into T_ORDER_ITEM (ORDER_ITEM_ID,PRODUCT_ID,ORDER_QTY,ORDER_DATE,NOTE) values (3,9,13,to_date('2023/07/01','yyyy/MM/DD'),'左オンリー(存在しない商品コード)');
Insert into T_ORDER_ITEM (ORDER_ITEM_ID,PRODUCT_ID,ORDER_QTY,ORDER_DATE,NOTE) values (4,1,13,to_date('2023/03/31','yyyy/MM/DD'),'左オンリー(開始年月日外れ)');
COMMIT;
商品マスタテーブルの初期値
| PRODUCT_ID | START_DATE | END_DATE | PREV_START_DATE | SORT_RANK | PRODUCT_NAME | UNIT_PRC | NOTE |
|---|---|---|---|---|---|---|---|
| 1 | 2023/04/01 | 2023/06/30 | 2023/03/31 | 1 | "リンゴ" | 100 | "古い単価" |
| 1 | 2023/07/01 | 2023/06/30 | 1 | "リンゴ" | 199 | "新単価" | |
| 999 | 2023/04/01 | 2023/03/31 | 2 | "みかん" | 50 | "右オンリー" |
注文トランザクションテーブルの初期値
| ORDER_ITEM_ID | PRODUCT_ID | ORDER_DATE | PRODUCT_NAME | START_DATE | END_DATE | ORDER_QTY | UNIT_PRC | PRICE |
|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 2023/06/30 | リンゴ | 2023/04/01 | 2023/06/30 | 12 | 100 | 1200 |
| 2 | 1 | 2023/07/01 | リンゴ | 2023/07/01 | 13 | 199 | 2587 | |
| 3 | 9 | 2023/07/01 | 13 | |||||
| 4 | 1 | 2023/03/31 | 13 |
ある時点の商品をSELECTするSQL
ポイント1:
バインド変数にしている「:SALES_DATE」が1つ目のポイント。BETWEENの構文を簡単に書くと次の通り。
「式1 (NOT)? BETWEEN 式2 AND 式3」
式1はテーブルのカラムでなくてもOK(数式や文字式、日時式でOK)。
ポイント2:
END_DATEはNULLがありえるので、COALESCE関数でNULLの場合はCURRENT_DATEを使うようにしてます。
/*
* ある時点の価格を調べる
*
* 期間:2023/4/1から2023/6/30、旧単価のレコード
* 期間:2023/7/1からnull、現在単価のレコード
* のような履歴を持つレコードがあり、バインド変数:SALES_DATEに2023/5/1を指定した場合は、旧単価のレコードが返ります。
* バインド変数:SALES_DATEに2023/8/1を指定した場合は、現在単価のレコードが返ります。
*/
SELECT
*
FROM
T_PRODUCT
WHERE
:SALES_DATE BETWEEN START_DATE AND COALESCE(END_DATE, CURRENT_DATE);
現在の商品マスタを一覧するViewの定義
END_DATE IS NULLが現在の商品。
-- 現在の商品マスタビュー
DROP VIEW V_CURRENT_PRODUCT;
CREATE VIEW V_CURRENT_PRODUCT AS
SELECT
*
FROM
T_PRODUCT
WHERE
END_DATE IS NULL;
商品を追加するストアードプロシージャ
他のプログラミング言語で書いてもよいです。ポイントは、arg_new_start_date - INTERVAL '1' DAYのところ。
前の期間の終了日と次の期間の開始日が重ならないようにしています。こうすることで、比較条件でBETWEENを使うことができます。
create or replace PROCEDURE prc_update_t_product (
arg_product_id IN DECIMAL,
arg_start_date IN DATE,
arg_new_start_date IN DATE,
arg_new_unit_prc IN DECIMAL,
arg_product_name IN VARCHAR2,
arg_note IN VARCHAR2
) IS
v_product_name VARCHAR2(100);
v_sort_rank DECIMAL;
v_note VARCHAR2(256);
BEGIN
SELECT
product_name,
sort_rank,
note
INTO
v_product_name,
v_sort_rank,
v_note
FROM
t_product
WHERE
product_id = arg_product_id
AND start_date = arg_start_date;
UPDATE t_product
SET
end_date = arg_new_start_date - INTERVAL '1' DAY
WHERE
product_id = arg_product_id
AND start_date = arg_start_date;
INSERT INTO t_product (
product_id,
start_date,
end_date,
prev_start_date,
sort_rank,
product_name,
unit_prc,
note
) VALUES (
arg_product_id,
arg_new_start_date,
NULL,
arg_new_start_date - INTERVAL '1' DAY,
v_sort_rank,
COALESCE(arg_product_name, v_product_name),
arg_new_unit_prc,
COALESCE(arg_note, v_note)
);
END;
こんな感じで使います。プライマリキーのPRODUCT_IDが1でSTART_DATEが2023/04/01のレコードの単価を2023/07/01から199円にするイメージです。
exec PRC_UPDATE_T_PRODUCT(1, TO_DATE('2023/04/01'), TO_DATE('2023/07/01'), 199, null, '新単価');
注文トランザクションの注文日付で該当する商品マスタの単価を変えてSELECT
-- 注文トランザクションと商品マスタをJOIN
-- 注文トランザクションの注文日付によりどの商品マスタの単価を適用するか分けてます
SELECT
A.ORDER_ITEM_ID,
A.PRODUCT_ID,
A.ORDER_DATE,
B.PRODUCT_NAME,
B.START_DATE,
B.END_DATE,
A.ORDER_QTY,
B.UNIT_PRC,
A.ORDER_QTY * B.UNIT_PRC AS PRICE,
A.NOTE
FROM
T_ORDER_ITEM A
LEFT JOIN T_PRODUCT B ON ( A.PRODUCT_ID = B.PRODUCT_ID
AND A.ORDER_DATE BETWEEN B.START_DATE AND COALESCE(B.END_DATE, CURRENT_DATE) )
ORDER BY
A.ORDER_ITEM_ID,
A.ORDER_DATE;
こんな感じ。
| ORDER_ITEM_ID | PRODUCT_ID | ORDER_DATE | PRODUCT_NAME | START_DATE | END_DATE | ORDER_QTY | UNIT_PRC | PRICE | NOTE |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 2023/06/30 | リンゴ | 2023/04/01 | 2023/06/30 | 12 | 100 | 1200 | 古い単価を参照 |
| 2 | 1 | 2023/07/01 | リンゴ | 2023/07/01 | 13 | 199 | 2587 | 現在単価を参照 | |
| 3 | 9 | 2023/07/01 | 13 | 左オンリー(存在しない商品コード) | |||||
| 4 | 1 | 2023/03/31 | 13 | 左オンリー(開始年月日外れ) |