履歴を持ったテーブルの設計

概要

データベースの設計や不具合の調査をしていて、ふと思ったことは無いでしょうか？
ログやバックアップに頼らずに、データベースに全ての変更履歴が残るようにできないだろうか、と。
試しに、設計してみるとFOREIGN KEY制約を保ちながら履歴を持ったテーブルを設計するというのは、なかなか厄介なことに気づきます。
しかし、突飛なアイディアという訳ではなく、ネット検索してみると結構ヒットします。
変更履歴を持つテーブルの設計
履歴テーブルからデータを取得するSQL
リレーショナルデータベースでは履歴の管理をすべきでない？
データベース設計 ～ マスタデータを含めて、全ての履歴を残したいという要望
先人の試行錯誤を自分なりに咀嚼して、設計してみた方法を文章として残して置こうかと思います。（※実際のプロジェクトに適用してみた気付きを加筆しました）

設計方針

履歴を持つことに意味のあるテーブルのみを対象とする。全てのテーブルの履歴を残そうと思わないこと。

履歴を持ったテーブルを扱うとディスク容量増加やレコードが増えることによるパフォーマンス低下が発生します。そして、コーディングが複雑になるという問題もあるため履歴が必要不可欠なテーブルに限定します。

ただし、クラウドでの開発であれば、容量もパフォーマンスもプロジェクト規模に合わせて変更できるため制御は可能だと思いますし、プログラムの複雑さも共通処理にまとめることで、緩和は可能です。

履歴には、2種類の考え方がある。

(1)履歴を参照するテーブルの過去情報が、変更されるタイプ(商品名を変更したら過去のトランザクションテーブルの商品名も変更になる)

(2)履歴を参照するテーブルの過去情報が、変更されないタイプ(商品名を変更しても過去のトランザクションテーブルの商品名は、取引した時の商品名のままにする)

※上記2つのアプローチの違いは、テーブル構造上は、小さな変更で対応可能です。

履歴情報だけを別テーブルへ分離する。

絶対に変更されない情報を持ったテーブルAと履歴情報だけを持ったテーブルBの２つをワンセットで扱います。

2テーブル1セットな構造となるためコーディングの負担を軽減するためViewを使って、纏めます。

※EntityFramework Coreは、Viewに対応してないので、私は使っていませんが、使える環境の方は使ってみてください

実際のER図

社員マスタテーブルに履歴を持たせてみます。
生年月日と性別が後から変更になることは、あまり想定できませんが、この例では社員の属性は全て、履歴テーブルへ分離しています。
社員マスタには、論理削除フラグと登録日時と更新日時を持たせています。
社員情報を論理削除する場合には、こちらのテーブルを操作します。
社員マスタ履歴テーブルには、論理削除フラグは持たせません。
履歴の最新の情報を取得する場合には、社員マスタ履歴Idの最も大きな番号のテーブルを取得します。

基本構造

過去トランザクションへ反映するパターン

出退勤履歴テーブル（トランザクションテーブル）には、社員マスタIdを持たせます。
社員マスタテーブルから社員情報を引いてくると最新の社員情報が、取得されます。
つまり、社員マスタ履歴テーブルを更新すると過去のトランザクションテーブルの情報も更新されることになります。

過去トランザクションへ反映しないパターン

出退勤履歴テーブル（トランザクションテーブル）には、社員マスタ履歴Idを持たせます。
社員マスタテーブルから社員情報を引くときには、直接社員マスタ履歴テーブルから引きます。
つまり、トランザクションした時の社員情報を引くので、過去のトランザクションテーブルの情報は更新されないことになります。

処理例

C#のLinqを使って処理を書いてみます。

新規追加/更新

新規追加と更新は、新レコードを追加する処理となるため同じになります。
レコードを追加するだけなので、追加更新系の処理は非常に簡単です。

特定社員の最新レコードを取得

OrderByでソートして、履歴IDが最大値のレコードが、最新レコードとなります。

var result = db.EmployeeHis.Where(o => o.EmployeeMstId == employeeId).OrderBy(o => o.EmployeeHisId).Last();

最新情報のリストを取得

最新のリストを取得するのは、少し複雑です。

var employees = db.EmployeeMst.Where(o => o.DelFlag == false)
                              .Join(db.EmployeeHis, inf => inf.EmployeeMstId, his => his.EmployeeMstId, (inf, his) => his)
                              .GroupBy(o => o.EmployeeMstId)
                              .Select(o => new { EmployeeHisId = o.Max(row => row.EmployeeHisId) })
                              .Join(db.EmployeeHis, maxid => maxid.EmployeeHisId, his => his.EmployeeHisId, (maxid, his) => his);

ImportedLinqを利用した方法

using ImportedLinq;

var employees = db.EmployeeMst.Where(o => o.DelFlag == false)
                              .Join(db.EmployeeHis, inf => inf.EmployeeMstId, his => his.EmployeeMstId, (inf, his) => his)
                              .GroupBy(o => o.EmployeeMstId)
                              .Select(g => g.MaxBy(o => o.EmployeeHisId).First()));