履歴データテーブルとの向き合い方_PHPerKaigi2024

PHPerKaigi2024 の登壇資料です。

履歴データテーブルとの向き合い方
https://fortee.jp/phperkaigi-2024/proposal/47cf9f17-825a-4021-bf33-86e4a62bc222

1. 履歴データとの向き合い方 げんえい（@gennei） 2024-03-08 @ PHPerKaigi 2024
2. ある日突然 システムで変更履歴取りたいんだよね と言われたらどうしますか？
3. 何に気をつけますか？ どうやって設計をしますか？
4. 自己紹介
5. 自己紹介 • アカウント: @gennei • 所属: カオナビ • 趣味 ◦ ⚽ サンフレッチェ広島 ◦ 📚 積読 ◦ 📻 Podcast ◦ ☕ コーヒー
6. ここに自宅の本棚の写真をいれる
7. 履歴データの話
8. 履歴データの種類 • ログ ◦ 注文履歴、発送履歴、アクセスログ • バージョン ◦ ソースコード、Wikipedia、ドキュメント • 範囲履歴 ◦ 所属履歴、学歴
9. 設計する時考えていること • データのライフサイクル • 正規化をするのか/しないのか • テーブル設計
10. データのライフサイクル • ログテーブル、データが増え続ける問題 ◦ タイムライン、送信履歴、注文履歴 • データがいつ作られて、いつ削除可能になるかを考える ◦ アプリケーションで使わなくなったら別の場所へ移行するなどを検 討する
11. 正規化するか/しないか • 注文した商品の商品名を履歴データに含めるか ◦ 商品IDが特定できれば商品名はJOINして取得できる ◦ 商品名が変わってしまったら… • どのように運用していくのかによって記録の仕方を考える必要がある
12. 正規化するか/しないか • 注文した商品の商品名を履歴データに含めるか ◦ 商品IDが特定できれば商品名はJOINして取得できる ◦ 商品名が変わってしまったら… • どのように運用していくのかによって記録の仕方を考える必要がある 「非正規化と履歴データの違い」 を読んでくれ！！！！！！！
13. テーブル設計 • ログ ◦ 注文履歴、発送履歴、アクセスログ • バージョン ◦ ソースコード、Wikipedia、ドキュメント • 範囲履歴 ◦ 所属履歴、学歴
14. テーブル設計 • ログ ◦ 注文履歴、発送履歴、アクセスログ • バージョン ◦ ソースコード、Wikipedia、ドキュメント • 範囲履歴 ◦ 所属履歴、学歴 この記事を読んでくれ！！ 履歴テーブルについて - 一休.com Developers Blog 変更履歴を持つテーブルの設計 #Database - Qiita だいたいみなさんの悩みについては書いてあります！
15. テーブル設計 • ログ ◦ 注文履歴、発送履歴、アクセスログ • バージョン ◦ ソースコード、Wikipedia、ドキュメント • 範囲履歴 ◦ 所属履歴、学歴 今日はここのテーブル設計を話します
16. 範囲履歴
17. 所属履歴の参考データ 社員ID 所属名 開始日 終了日 所属期間 A００１ 製造部 2023-04-01 現在 11ヶ月 A001 管理部 2021-10-01 2023-03-31 1年６ヶ月 A002 人事部 2023-04-01 現在 11ヶ月
18. テーブル設計 • メリット ◦ わかりやすい • デメリット ◦ レコードをまたぐバリデーション ◦ 現在所属の終了日の扱い ◦ 更新時に終了日を更新 + INSERT が必要 ◦ 過去のデータを変更したくなった時はどうする？ 所属履歴 id 社員ID 所属名 開始日 終了日
19. レコードをまたぐバリデーション 社員ID 所属名 開始日 終了日 A００１ 製造部 2023-04-01 現在 A001 管理部 2021-10-01 2022-03-31 A001 人事部 2021-04-01 2023-03-31
20. レコードをまたぐバリデーション 社員ID 所属名 開始日 終了日 A００１ 製造部 2023-04-01 現在 A001 管理部 2021-10-01 2022-03-31 A001 人事部 2021-04-01 2023-03-31 ３レコード目の開始終了の範囲内に ２レコード目の値が入ってしまっている
21. 図で説明すると 2021-04-01 2022-04-01 2023-04-01 現在 管理部 製造部 人事部
22. 現在所属の終了日の扱い 社員ID 所属名 開始日 終了日 A００１ 製造部 2023-04-01 現在 A001 管理部 2021-10-01 2022-03-31 A001 人事部 2021-04-01 2023-03-31 現在の値どうする？
23. 現在所属の終了日の扱い 社員ID 所属名 開始日 終了日 A００１ 製造部 2023-04-01 現在 A001 管理部 2021-10-01 2022-03-31 A001 人事部 2021-04-01 2023-03-31 null にしよう！ nullを使う 複数行存在した場合 どうしますか？
24. 現在所属の終了日の扱い 社員ID 所属名 開始日 終了日 A００１ 製造部 2023-04-01 現在 A001 管理部 2021-10-01 2022-03-31 A001 人事部 2021-04-01 2023-03-31 9999-12-31 にして UK で制御 アプリケーションコードで 漏れなく変換を実装できますか？
25. 更新時に終了日を更新 + INSERT が必要 社員ID 所属名 開始日 終了日 A００１ 製造部 2023-04-01 現在 A001 管理部 2021-10-01 2022-03-31 A001 人事部 2021-04-01 2023-03-31 更新時２ステップ必要になる 1. 現在の値を更新する 2. 新しいレコードを作る
26. 過去のデータを変更したくなった時はどうする？ 社員ID 所属名 開始日 終了日 A００１ 製造部 2023-04-01 現在 A001 管理部 2022-04-01 2023-03-31 A001 人事部 2021-04-01 2022-03-31 終了日を変更しようとすると 別の行に影響がでる
27. 図で説明すると 2021-04-01 2022-04-01 2023-04-01 現在 管理部 製造部 人事部 管理部の期間を変えるのに 他の期間を変更する必要がある
28. 問題点まとめ • 開始日と終了日のバリデーションが難しい • 現在の所属の終了日の扱い ◦ nullにする -> 複数 null が入ってしまう ◦ 9999-12-31 + UK -> アプリで工夫が必要。 • 更新時に2レコード更新する必要がある • 過去の開始日や終了日を変更を考えるのが大変
29. 不安だなあ
30. 人事異動業務を知る
31. 所属が変わるってどういうこと？ 1. 各部署からの異動案を募る a. 人の異動、部署名変更 2. 人事部が全部署の案をまとめる 3. 異動を人事通達で知らせる
32. 所属が変わるってどういうこと？ 1. 各部署からの異動案を募る a. 人の異動、部署名変更 2. 人事部が全部署の案をまとめる 3. 異動を人事通達で知らせる 異動した事実だけを記録している 開始日と終了日という概念は存在しない
33. 業務の事実を記録する 社員ID 所属名 異動日 A００１ 製造部 2023-04-01 A001 管理部 2022-10-01 A001 人事部 2021-04-01
34. 業務の事実を記録する 社員ID 所属名 異動日 A００１ 製造部 2023-04-01 A001 管理部 2022-10-01 A001 人事部 2021-04-01 イベントが発生した日 　開始日 = 異動日 　終了日 = 次の異動日 -1日
35. つまりそれってイミュータブルデータモデルでは？ • 雑に言うとそうです • 更新(UPDATE)を止める ◦ UPDATE で変更前の事実を失わないようにする • INSERT(or DELETE) だけでデータを記録していく
36. つまりそれってイミュータブルデータモデルでは？ • 雑に言うとそうです • 更新(UPDATE)を止める ◦ UPDATE で事実を失わないようにする • INSERT(or DELETE) だけでデータを記録していく 詳しくはこちらの 「イミュータブルデータモデルで始める実践データモデリン グ 業務の複雑さをシンプルに表現！」 を読んでくれ！
37. 改善案 1. メリット a. 間違ったデータの混入が防げる b. INSERTだけで済むようになった 2. デメリット a. 終了日がなくなったので期間の導出が面倒になった
38. 期間を計算するSQL (MySQL 8.0.35 で試しました) SELECT 社員ID, 所属名, 異動日 AS 開始日, COALESCE( LEAD(異動日) OVER (PARTITION BY 社員ID ORDER BY 異動日) - INTERVAL '1 day', CURRENT_DATE() ) AS 終了日 FROM 異動履歴 ORDER BY 社員ID,異動日;
39. 期間を計算するSQL (MySQL 8.0.35 で試しました) SELECT 社員ID, 所属名, 異動日 AS 開始日, COALESCE( LEAD(異動日) OVER (PARTITION BY 社員ID ORDER BY 異動日) - INTERVAL '1 day', CURRENT_DATE() ) AS 終了日 FROM 異動履歴 ORDER BY 社員ID,異動日; LEAD() OVER () で1行下の値を取得 その後 -1 日する
40. 期間を計算するSQL (MySQL 8.0.35 で試しました) SELECT 社員ID, 所属名, 異動日 AS 開始日, COALESCE( LEAD(異動日) OVER (PARTITION BY 社員ID ORDER BY 異動日) - INTERVAL '1 day', CURRENT_DATE() ) AS 終了日 FROM 異動履歴 ORDER BY 社員ID,異動日; LEAD() OVER () で1行下の値を取得 その後 -1 日する COALESCEで 1行下がないときは現在の日付を使う
41. まとめ • 事業ドメインの理解をしよう ◦ 要望を（何も考えずに）そのまま実現するのをやめよう • データのライフサイクルを考えましょう • 事実は消えないように設計しましょう • 問題の捉え方を変えることによって不具合を未然に防ごう • 設計をしても、よい設計はないか考え続けましょう
42. 参考記事・スライド • 履歴テーブルについて - 一休.com Developers Blog • 変更履歴を持つテーブルの設計 #Database - Qiita • 『失敗から学ぶ RDBの正しい歩き方』 ◦ 「Column非正規化と履歴データの違い」 • 『WEB+DB PRESS Vol.130』 ◦ 「イミュータブルデータモデルで始める実践データモデリング業務 の複雑さをシンプルに表現！」
43. まとめ • 事業ドメインの理解をしよう ◦ 要望を（何も考えずに）そのまま実現するのをやめよう • データのライフサイクルを考えましょう • 事実は消えないように設計しましょう • 問題の捉え方を変えることによって不具合を未然に防ごう • 設計をしても、よい設計はないか考え続けましょう