【開発SE】上流工程の初心者に伝えたい基本設計のポイント（データ中心アプローチ編）

記事の概要

データ中心アプローチに発生し得る問題に対する考え方をシェアする。

対象者

下記の全てに該当する。

• 開発SE
• 上流工程の経験が浅い、または未経験。

注意点

あくまで筆者の経験から『あるべき論に近い内容』を記述するが、『現場ごとに最適な選択は異なる可能性がある』点を念頭に置き、周囲（上長や先輩）からのアドバイスも得ること。

題材とする案件（現仕様と要件の説明）

今回は、架空のメーカーでの『リベート（報奨金）』のシステム開発とする。
流通小売業に関わったり、簿記の勉強をしている方にはお馴染みかもしれない。
製造業や販売業には、ほぼ会社ごとに存在すると言って良いので、関わる機会も多いかもしれない。

リベートとは？

もし製造業や流通小売業の開発現場に携わる立場で知らない場合は、事前に確認した方が良いが簡単に言えば下記の通りである。

◆商品を卸す側の立場（※今回の題材はこちら側）

• 自社の商品を販売してくれる得意先(お客様) に対して、報奨金を支払う。

◆商品を仕入れる側の立場

• 仕入れた商品を販売し、契約による一定数以上の売上を達成した場合、報奨金が支払われる。

尚、勘定科目は『売上割戻』 だが、業務上での呼び名は企業によってルールが異なり『報奨金』『奨励金』『返戻金』『キックバック』などのキーワードが出てきたら、リベートのことである可能性が高い。

現在の仕様

では、題材となるメーカーのリベートに使われるものを記述する。

• 基幹系のマスタは整備されている（得意先や社員など）
• リベートの条件や実績は表計算ソフトで管理している。
• つまり、リベートはシステム化されていない。

マスタイメージ

社員マスタ

社員cd（※主キー）	社員名称
901	鈴木
902	田中

得意先マスタ

得意先cd（※主キー）	得意先名称	営業担当者cd（※外部キー）
1001	A社	901
1002	B社	902

業務要件～機能要件

プロジェクトは『リベートの完全システム化』だが、今回は簡潔とする。

• マスタ管理のみを対象とする。
• ユーザが業務で使用しているリベート用のマスタファイルを、そのままシステム化する。

システム化対象

ユーザから「これでお願いします。」と受け取ったファイルは、下記のようなフォーマットである。

契約年度	得意先cd	得意先名称	条件１	報奨率１	条件２	報奨率２	営業担当者cd	営業担当者名称	所属長承認
2023	1001	A社	\5,000	2%	\10,000	4%	901	鈴木	承認済
2024	1001	A社	\7,000	3%	\14,000	6%	901	鈴木	承認済
2023	1002	B社	\3,000	1%	\6,000	2%	902	田中	承認済
2024	1002	B社	\9,000	7%	\18,000	14%	902	田中	承認済

ファイルを読み解く

まずはユーザにヒアリングし、どういう仕組みで使われるのか確認する。
今回の場合は、下記の通りである。

• 得意先ごと且つ年度ごとに、売り上げて欲しい金額の条件を２つまで設定する（１つでも可）
• 該当の条件を達成したら『売上金額の〇％』を支払う。支払額は売上×報奨率である。

例）2023年度、A社による当社商品の売上は5,700円だった。

• 条件１を達成（5,000円以上10,000未満）
• 報奨率１が適用（売上の2%）
• A社に114円が報奨金として支払われる（5,700円×2%）

実装方法を考える

さて、ここからは上記のファイルを『リベートマスタ』と呼ぶことにする。
では、これをそのままの状態でテーブル定義に起こして良いのだろうか？

正規化

そのままテーブル定義にすると、システム化した際に色々な不具合が発生する。
そのため正規化する必要があるが、理由を考えながら実施する。

不具合の理由とは

まずは関連するマスタ間の不整合である。

• 得意先名称または営業担当者cdを変更する場合、リベートマスタと得意先マスタの２つを変更しなくてはならない。
• 営業担当者名称を変更する場合（苗字が変わったなど理由で）リベートマスタと社員マスタの２つを変更しなくてはならない。

つまり、片方の変更が漏れた場合に発生する。下記のような状態である。

• 得意先マスタの得意先cd『1001番』である 『A社』の社名を『AA社』に変更した。
• リベートマスタの得意先cd『1001番』の 『A社』は変更していない。
• 同じ『1001番』なのに 『A社』と『AA社』が存在することになる。

次にリベートマスタ『内』 の不整合である。

• 対象の『年度と得意先の組合せ』全てに上記と同じ変更をしなくてはならない。

つまり、１つの得意先で、年度ごとの変更が漏れた場合に発生する。下記のような状態である。

• リベートマスタの『2023年度』で得意先cd『1001番』である 『A社』の社名を『AA社』に変更した。
• リベートマスタの『2024年度』で得意先cd『1001番』である 『A社』の社名は変更していない。
• 同じ『1001番』なのに 『A社』と『AA社』が存在することになる。

ただし、新年度から社名が変わるなどの場合も想定される（年度の途中でも変わるかもしれない）
つまり、年度ごとに得意先名称が異なる場合もあり得る。
しかし、その場合は 『得意先マスタ』の定義変更 が必須となる。
そのため、今回はその点については度外視する（『リベートマスタ』のみ考える）

不具合を回避する方法

つまり上記の裏返しをする。
リベートマスタからキー項目以外（今回の場合は『契約年度』と『得意先cd』以外）で、他のマスタから取得可能な項目を削除すれば良い。

• 得意先名称：得意先マスタから取得するため、リベートマスタから削除。
• 営業担当者cd：得意先マスタから取得するため、リベートマスタから削除。
• 営業担当者名称：社員マスタから取得するため、リベートマスタから削除。

それらを削除したテーブル『リベートマスタ』 が、これである。

契約年度（※主キー）	得意先cd（※主キー）	条件１	報奨率１	条件２	報奨率２	所属長承認
2023	1001	\5,000	2%	\10,000	4%	承認済
2024	1001	\7,000	3%	\14,000	6%	承認済
2023	1002	\3,000	1%	\6,000	2%	承認済
2024	1002	\9,000	7%	\18,000	14%	承認済

実は他にも直すべき点があるのだが、後述の『仕様変更が発生！』で発覚すると想定し、ひとまず先へ進む。

ちなみに、要件通りのフォーマットに戻す場合の結合イメージは下記の通り。

リレーション(矢印) は全て『1対多（『多』が矢の先端）』である。理由は下記の通り。

• １人の社員は、複数の得意先を担当することがある。
• １つの得意先は、年度ごとにリベート契約を締結する。

これを実装する結合条件は下記の通り。

• 得意先マスタ.営業担当者cd = 社員マスタ.社員cd
• リベートマスタ.得意先cd = 得意先マスタ.得意先cd

また、簡略化のため全体のSQLおよび外部キー制約の説明を省略する。

仕様変更が発生！

リベートマスタの設計がある程度進んだところで、ユーザにこう告げられる。
「2025年度から、条件の上限が３つまでに増えました。」

現状、条件は２つまでで運用していたので、受け取ったファイルには無かった。
対応が必須かどうか検討した結果、対応することになった。

推奨できない対応

「なんだ、簡単じゃないか。」
そう思った場合、もしかしたら間違った方法を取るかもしれない。

単純に項目を追加

「こうすれば解決」と、テーブル自体を下記のような状態にしていないだろうか？

リベートマスタ（条件の追加版）

契約年度（※主キー）	得意先cd（※主キー）	条件１	報奨率１	条件２	報奨率２	条件３	報奨率３	所属長承認
2023	1001	\5,000	2%	\10,000	4%			承認済
2024	1001	\7,000	3%	\14,000	6%			承認済
2025	1001	\7,000	3%	\14,000	6%	\20,000	8%	未承認
2023	1002	\3,000	1%	\6,000	2%			承認済
2024	1002	\9,000	7%	\18,000	14%			承認済
2025	1002	\9,000	7%	\18,000	14%	\24,000	16%	未承認

問題点

ユーザが利用するためのアウトプットとしては、上記の状態で問題は無い。
しかし、システムとして実装するには大きな問題がある。なぜか？
下記の点が挙げられる。

• あとから再び「条件が４つになりました」「５つになりました」と言われるたびに、同じようなテーブル定義の変更が発生し、余計な工数がかかる。

この問題を起こさないために、別の方法を考える（次へ）

推奨する対応

正規化の見直し

要するに、考えるべき点は下記の通りだ。

• もしユーザから「また条件が追加」と依頼があっても、テーブル定義を変更しないで済む仕様にする。

上記を踏まえ、テーブルを更に分割する。

• リベート承認マスタ：契約年度と得意先cdの組合せに所属長承認を持たせる。
• リベート条件マスタ：契約年度と得意先cdと条件cdの組合せに条件と報奨率を持たせる。

リベート承認マスタ

契約年度（※主キー）	得意先cd（※主キー）	所属長承認
2023	1001	承認済
2024	1001	承認済
2025	1001	未承認
2023	1002	承認済
2024	1002	承認済
2025	1002	未承認

リベート条件マスタ

契約年度（※主キー）	得意先cd（※主キー）	条件cd（※主キー）	条件	報奨率
2023	1001	1	\5,000	2%
2023	1001	2	\10,000	4%
2024	1001	1	\7,000	3%
2024	1001	2	\14,000	6%
2025	1001	1	\7,000	3%
2025	1001	2	\14,000	6%
2025	1001	3	\20,000	8%
2023	1002	1	\3,000	1%
2023	1002	2	\6,000	2%
2024	1002	1	\9,000	7%
2024	1002	2	\18,000	14%
2025	1002	1	\9,000	7%
2025	1002	2	\18,000	14%
2025	1002	3	\24,000	16%

改善できたポイント

問題点をどう改善出来たのか、下記の通りである。

• もしユーザから「また条件が追加」と依頼があったら、リベート条件マスタにレコードを追加するだけで良い。（テーブル定義は変更しない）

元に戻せるのか？

もちろん。結合イメージは下記の通り。

概ね条件を追加する前の改善方法と同じではあるが、下記の通りである。

• １人の社員は、複数の得意先を担当することがある。
• １つの得意先は、年度ごとにリベート契約を締結する。
• 【追加】所属長は、年度とリベート契約の組合せごとに１つだけ承認する。
• 【追加】リベート条件と報奨率は、年度とリベート契約の組合せごとに複数保持することが出来る。

これを実装する結合条件は下記の通り。

• 得意先マスタ.営業担当者cd = 社員マスタ.社員cd
• 【変更】リベート承認マスタ.得意先cd = 得意先マスタ.得意先cd
• 【追加】リベート承認マスタ.得意先cd = リベート条件マスタ.得意先cd
• 【追加】リベート承認マスタ.年度 = リベート条件マスタ.年度

問題点があるとすれば

今回のケースはこれで完成ではあるが、問題点が無いわけではない。
例えば下記のような点である。

1. 単純なSQLでは、ユーザ要件通りにアウトプットにならない。
2. 分割によりテーブル数が増えたので、結合のオーバーヘッドが発生する（パフォーマンスの問題）

ただし、これらは微々たる問題である。
1と2それぞれの理由は下記の通り。

1. ユーザ要件通りのアウトプットにするためには、SQLで同じテーブルに対して幾つかJOINを駆使するか、またはウィンドウ関数を使うかなど、少し考える必要はあるが十分に可能である（※今回は省略する）
2. 結合のオーバーヘッドは、インデックスを上手く使うことでパフォーマンスを改善できる（チューニング）特に、今回のように結合が主キーのみで且つ等結合であれば、インデックスが使われる。

最後に

今回はデータ中心アプローチをテーマにしたが、本当にほぼデータのみに着目してみた。

冒頭で述べた通り『現場ごとに最適な選択は異なる可能性がある』ので、この記事も１つの参考として頂ければ幸いである。

ここまで読んで頂き、多謝！！ m(_ _)m