データベースで世代を管理出来るテーブル設計を教えてください

Question

実現したいこと

理想的な世代管理が出来るテーブル設計を知りたい。

悩んでいる点

試しに下記のケースを考えたのですが、解決策が思い浮かばす悩んでいます・・・

---

従業員テーブルと売上テーブルがあり、売上テーブルは外部キーとして従業員マスターの従業員コードを参照します。
ですが、以下の制約があります。

①従業員は登録/登録解除があり、解除後、同じ人を再度従業員として登録する場合、前回と同じ従業員コードにする必要があります。
②売上に割り当てられた従業員コードは変更可能である必要があります。
※売上実績を、ほかの人に付け替える事になります。
③売上に割り当てられた従業員コードを変更する時、売上が計上された当日に登録されている従業員の中だけから選択出来るようにする。
④新規に売り上げ登録する時は、現在存在する従業員からしか選択出来ないので、世代を意識する必要はない。

従業員コードが下記のように重複する前提です。
従業員CD　従業員名　登録日　登録解除日
CD00001　 山本一郎　2020-01-01　2020-02-01
CD00001　 山本一郎　2021-12-01　2022-10-10

ですので、従業員テーブルには世代を持たせる必要があると思っていて、シンプルに考えてこうなりました。
従業員CD　世代　従業員名　登録日　登録解除日
CD00001　　1　　山本一郎　2020-01-01　2020-02-01
CD00001　　2　　山本一郎　2021-12-01　2022-10-10

ですが、従業員マスターのレコードがどんどん増加していくのが目に見えており、
JOIN のコストも上がっていきそうなのが気がかりです。
こういう場合はどうするのがベストなのでしょうか？

Answer 1

回答

素直に正規化すると

• 従業員マスタ(従業員コード(PK)、氏名)
• 従業員登録履歴テーブル（従業員コード(FK)、登録開始日、登録解除日 ）←登録と解除があるたびに履歴が増える
• 売上テーブル(売上番号（PK）、売上日時、従業員コード（FK）)

として、

• 売り上げ発生時→従業員登録履歴テーブルから登録解除日が存在しない従業員コードを選択
• 売り上げ付け替え時→売り上げ日時が従業員登録履歴テーブルの登録開始日～登録解除時の従業員コードを選択
• 従業員登録時→マスターに登録＋従業員登録履歴テーブルに従業員コードと登録開始日でレコード投入
• 従業員登録解除時→従業員登録履歴テーブルの最新該当レコードに登録終了日を入れて更新
• 従業員再登録時→従業員登録履歴テーブルに従業員コードと登録開始日でレコード投入

という感じになると思います。

補足

ですが、従業員マスターのレコードがどんどん増加していくのが目に見えており、

マスターテーブルが履歴を兼ねていて肥大していくのは設計としては微妙だとは思いますが、

JOIN のコストも上がっていきそうなのが気がかりです。

パフォーマンスについてはまずは採用するRDBMSと最大データ量とハードウェアスペック、パフォーマンス要件を想定しないことには検討のしようが無いので、まずはその辺りを想定してみてはいかがでしょうか？

正直なところ、適切にインデックスが設定されており、適切なクエリになっていれば質問のテーブル設計でも一生困らないくらいのデータ量にしかならない気がします。

Comments

[otn]

履歴を持つ従業員登録履歴テーブルと別に従業員マスターを持つ意味は何でしょうか？

お書きの仕様だと、「現時点で有効な従業員」ではなく「一度でも登録したことのある従業員・元従業員」が入っているようですが、「無効な人も含まれて有効か無効かは他のテーブルを見ないと分からない従業員マスター」を使うのはどういう場面を想定されていますか？

[tanat]

> otnさん

＊私の解釈では[従業員マスターの有効/無効]と[従業員登録/解除履歴によるその期間の売り上げ登録可能/不可能]は全く別の情報なので、この辺の質問の解釈の違いからくるご質問に思っています。

> 履歴を持つ従業員登録履歴テーブルと別に従業員マスターを持つ意味は何でしょうか？
従業員マスターと従業員の履歴情報は1:nで紐づく情報なので、素直に正規化すると別テーブルになるのが妥当と考えます。
正規化そのものに意味があるかというと、他の要件の妨げにならない限り、まずは正規化するのがベターというか常道だと考えです。

> 「無効な人も含まれて有効か無効かは他のテーブルを見ないと分からない従業員マスター」を使うのはどういう場面を想定されていますか？

質問の範囲では従業員マスターを単体で見るようなケースは想定されていないので、回答の範囲でも想定していませんが、例えば「過去の全従業員一覧の表示」の様なケースは想定してもおかしくは無いと思います。

また、それ以外のケースで従業員マスターの属性を想定する場合、例えば「過去履歴は保持したいが再登録は拒否しないといけない人」とかがあるのであれば、そのケースに合わせてフィールドなりテーブルなりを追加する必要もあるでしょう。

Answer 2

他の回答者様にもご意見・同意・反論を頂きたいのですが、
実際に使う時はアプリでDBに接続するでしょうから、敢えてリレーションさせないというのはどうでしょうか？

①売り上げ登録時
⇒ 従業員マスターから選択肢を表示。
（従業員マスターは売上登録時だけ参照される想定）
②売り上げ付け替え時
⇒ 従業員マスター履歴から、売上日時のその時に登録されていた従業員を選択肢として表示。

登録時は従業員現在マスターから選択させる。

SELECT
employee_code,
employee_name
FROM
m_current_employees;

売り上げ付け替え時は、従業員マスター履歴から、その売り上げ登録時に存在していた従業員を抜き出して、その中から選択させる。

SELECT
employee_code,
employee_name
FROM
m_employee_log
WHERE
registration_date <= (SELECT sales_datetime FROM sales_number = '売上番号' )
AND
(SELECT sales_datetime FROM sales_number = '売上番号' ) <= deregistration_date;

Comments

[tanat]

> 他の回答者様にもご意見・同意・反論を頂きたいのですが、
> 実際に使う時はアプリでDBに接続するでしょうから、敢えてリレーションさせないというのはどうでしょうか？

RDBMSの機能やメリットを捨てることになるので、よほどメリットがデメリットを上回るケースで無いと採用しにくい方針かと思います。

例えば今回のケースだと、アプリ側にバグがあった際に
不正な従業員コードが売り上げテーブルや従業員マスター履歴に存在するようなケースがあり得ますが、それを許容出来るかというような判断を随時していく必要が出てきます。
そこまでするならRDBMSでは無くて何らかのNoSQLで管理する方がいいのでは？と思ってしまいます。

Answer 3

世代ではなく重複しない登録IDで従業員を識別すれば、世代管理しなくてもよさそうですが。