目次
動作ログを追跡するためデータベースに「履歴テーブル」が存在するシステムがあります。保険や金融のシステムが良く採用していましたが、近年ではサービス向上の情報源ため、あらゆるデータにログを残すことが当たり前になりました。
動作ログについては近年ではイベントソーシングとCQRSが話題になっていますが、本記事では軽くそれらについて説明して、従来の履歴テーブルの構成のみ解説。
テーブル設計
設計は「達人に学ぶDB設計 徹底指南書」で紹介されていたタイムスタンプ式とインターバル式が最も最適だと思ったので紹介。
タイムスタンプ式
Datetime型のプライマリキーが存在するテーブル設計。データを更新する場合はUPDATEではなく、同じIDで記録日を現在日時でレコードを追加する。削除はできないので注文履歴など消すことがないテーブル向け。
| id(キー) | 記録日(キー) | 身長 | 体重 |
| 1 | 2020/04/30 | 150 | 40 |
| 1 | 2021/04/30 | 153 | 41 |
| 2 | 2020/04/30 | 151 | 45 |
| 2 | 2021/04/30 | 152 | 46 |
| 3 | 2020/04/30 | 160 | 50 |
| 3 | 2021/04/30 | 163 | 51 |
インターバル式
タイムスタンプ式に終了日を加えて削除できるようにしたテーブル設計。終了日は追加時にはDatetimeの最大値にして、削除したい時は終了日を更新します。
| id(キー) | 販売開始日(キー) | 販売終了日 | name | price |
| 1 | 2020/01/01 | 2021/02/31 | ハンバーグ弁当 | 580 |
| 1 | 2020/03/01 | 2021/04/31 | ハンバーグ弁当 | 550 |
| 1 | 2020/05/01 | 9999/12/31 | からあげハンバーグ弁当 | 580 |
| 2 | 2010/01/01 | 9999/12/31 | 梅おにぎり | 120 |
| 3 | 2020/01/01 | 2021/02/02 | からあげパン | 200 |
蛇足ですが、レコードの変更を予約する機能を実装したい場合は、現在のデータ取得に未来のstart_timeは除外するSQLを加えれば簡単に実装できます。
CQRSとは
履歴テーブルから現在データを取得するには、過去データを取り除くWhere文の記載が必要で、大量のレコードから検索するのでパフォーマンスが悪化します。また、更新・削除のSQLが従来のCURDとは異なり、コーディングが複雑になります。
そこで、履歴テーブルにはCQRSという設計手法を用いるのが適切です。
これは更新と取得でデータストアと責務を分離する手法で、具体的には履歴テーブルから最新テーブルというクエリテーブルを作成することです。
最新テーブルの更新方法は、履歴テーブルの追加と共に最新テーブルの更新するのが最もベストですが、要件を満たせるなら定期的にバッチを走らせる方法でも構わないと思います。
イベントソーシングとは？
CQRSとセットで紹介される設計手法。従来の履歴テーブルのように変更結果のスナップショットをレコードに記録するに加えて、操作ログを記録する手法のことをイベントソーシングと呼びます。
最新データを確認するには最初から最後までの操作ログを辿れば確認できますし、途中データは操作ログの任意の位置で出力することで確認できます。操作ログを取り消せたり、途中のデータから分岐できたりする。要するにGithub。