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

Slide 1

Slide 1 text

履歴データとの向き合い方げんえい（@gennei） 2024-03-08 @ PHPerKaigi 2024

Slide 2

Slide 2 text

ある日突然システムで変更履歴取りたいんだよねと言われたらどうしますか？

Slide 3

Slide 3 text

何に気をつけますか？どうやって設計をしますか？

Slide 4

Slide 4 text

自己紹介

Slide 5

Slide 5 text

自己紹介 ● アカウント: @gennei ● 所属: カオナビ ● 趣味 ○ ⚽ サンフレッチェ広島 ○ 📚 積読 ○ 📻 Podcast ○ ☕ コーヒー

Slide 6

Slide 6 text

ここに自宅の本棚の写真をいれる

Slide 7

Slide 7 text

履歴データの話

Slide 8

Slide 8 text

履歴データの種類 ● ログ ○ 注文履歴、発送履歴、アクセスログ ● バージョン ○ ソースコード、Wikipedia、ドキュメント ● 範囲履歴 ○ 所属履歴、学歴

Slide 9

Slide 9 text

設計する時考えていること ● データのライフサイクル ● 正規化をするのか/しないのか ● テーブル設計

Slide 10

Slide 10 text

データのライフサイクル ● ログテーブル、データが増え続ける問題 ○ タイムライン、送信履歴、注文履歴 ● データがいつ作られて、いつ削除可能になるかを考える ○ アプリケーションで使わなくなったら別の場所へ移行するなどを検討する

Slide 11

Slide 11 text

正規化するか/しないか ● 注文した商品の商品名を履歴データに含めるか ○ 商品IDが特定できれば商品名はJOINして取得できる ○ 商品名が変わってしまったら… ● どのように運用していくのかによって記録の仕方を考える必要がある

Slide 12

Slide 12 text

正規化するか/しないか ● 注文した商品の商品名を履歴データに含めるか ○ 商品IDが特定できれば商品名はJOINして取得できる ○ 商品名が変わってしまったら… ● どのように運用していくのかによって記録の仕方を考える必要がある「非正規化と履歴データの違い」を読んでくれ！！！！！！！

Slide 13

Slide 13 text

テーブル設計 ● ログ ○ 注文履歴、発送履歴、アクセスログ ● バージョン ○ ソースコード、Wikipedia、ドキュメント ● 範囲履歴 ○ 所属履歴、学歴

Slide 14

Slide 14 text

テーブル設計 ● ログ ○ 注文履歴、発送履歴、アクセスログ ● バージョン ○ ソースコード、Wikipedia、ドキュメント ● 範囲履歴 ○ 所属履歴、学歴この記事を読んでくれ！！履歴テーブルについて - 一休.com Developers Blog 変更履歴を持つテーブルの設計 #Database - Qiita だいたいみなさんの悩みについては書いてあります！

Slide 15

Slide 15 text

テーブル設計 ● ログ ○ 注文履歴、発送履歴、アクセスログ ● バージョン ○ ソースコード、Wikipedia、ドキュメント ● 範囲履歴 ○ 所属履歴、学歴今日はここのテーブル設計を話します

Slide 16

Slide 16 text

範囲履歴

Slide 17

Slide 17 text

所属履歴の参考データ社員ID 所属名開始日終了日所属期間 A００１製造部 2023-04-01 現在 11ヶ月 A001 管理部 2021-10-01 2023-03-31 1年６ヶ月 A002 人事部 2023-04-01 現在 11ヶ月

Slide 18

Slide 18 text

テーブル設計 ● メリット ○ わかりやすい ● デメリット ○ レコードをまたぐバリデーション ○ 現在所属の終了日の扱い ○ 更新時に終了日を更新 + INSERT が必要 ○ 過去のデータを変更したくなった時はどうする？所属履歴 id 社員ID 所属名開始日終了日

Slide 19

Slide 19 text

レコードをまたぐバリデーション社員ID 所属名開始日終了日 A００１製造部 2023-04-01 現在 A001 管理部 2021-10-01 2022-03-31 A001 人事部 2021-04-01 2023-03-31

Slide 20

Slide 20 text

レコードをまたぐバリデーション社員ID 所属名開始日終了日 A００１製造部 2023-04-01 現在 A001 管理部 2021-10-01 2022-03-31 A001 人事部 2021-04-01 2023-03-31 ３レコード目の開始終了の範囲内に２レコード目の値が入ってしまっている

Slide 21

Slide 21 text

図で説明すると 2021-04-01 2022-04-01 2023-04-01 現在管理部製造部人事部

Slide 22

Slide 22 text

現在所属の終了日の扱い社員ID 所属名開始日終了日 A００１製造部 2023-04-01 現在 A001 管理部 2021-10-01 2022-03-31 A001 人事部 2021-04-01 2023-03-31 現在の値どうする？

Slide 23

Slide 23 text

現在所属の終了日の扱い社員ID 所属名開始日終了日 A００１製造部 2023-04-01 現在 A001 管理部 2021-10-01 2022-03-31 A001 人事部 2021-04-01 2023-03-31 null にしよう！ nullを使う複数行存在した場合どうしますか？

Slide 24

Slide 24 text

現在所属の終了日の扱い社員ID 所属名開始日終了日 A００１製造部 2023-04-01 現在 A001 管理部 2021-10-01 2022-03-31 A001 人事部 2021-04-01 2023-03-31 9999-12-31 にして UK で制御アプリケーションコードで漏れなく変換を実装できますか？

Slide 25

Slide 25 text

更新時に終了日を更新 + INSERT が必要社員ID 所属名開始日終了日 A００１製造部 2023-04-01 現在 A001 管理部 2021-10-01 2022-03-31 A001 人事部 2021-04-01 2023-03-31 更新時２ステップ必要になる 1. 現在の値を更新する 2. 新しいレコードを作る

Slide 26

Slide 26 text

過去のデータを変更したくなった時はどうする？社員ID 所属名開始日終了日 A００１製造部 2023-04-01 現在 A001 管理部 2022-04-01 2023-03-31 A001 人事部 2021-04-01 2022-03-31 終了日を変更しようとすると別の行に影響がでる

Slide 27

Slide 27 text

図で説明すると 2021-04-01 2022-04-01 2023-04-01 現在管理部製造部人事部管理部の期間を変えるのに他の期間を変更する必要がある

Slide 28

Slide 28 text

問題点まとめ ● 開始日と終了日のバリデーションが難しい ● 現在の所属の終了日の扱い ○ nullにする -> 複数 null が入ってしまう ○ 9999-12-31 + UK -> アプリで工夫が必要。 ● 更新時に2レコード更新する必要がある ● 過去の開始日や終了日を変更を考えるのが大変

Slide 29

Slide 29 text

不安だなあ

Slide 30

Slide 30 text

人事異動業務を知る

Slide 31

Slide 31 text

所属が変わるってどういうこと？ 1. 各部署からの異動案を募る a. 人の異動、部署名変更 2. 人事部が全部署の案をまとめる 3. 異動を人事通達で知らせる

Slide 32

Slide 32 text

所属が変わるってどういうこと？ 1. 各部署からの異動案を募る a. 人の異動、部署名変更 2. 人事部が全部署の案をまとめる 3. 異動を人事通達で知らせる異動した事実だけを記録している開始日と終了日という概念は存在しない

Slide 33

Slide 33 text

業務の事実を記録する社員ID 所属名異動日 A００１製造部 2023-04-01 A001 管理部 2022-10-01 A001 人事部 2021-04-01

Slide 34

Slide 34 text

業務の事実を記録する社員ID 所属名異動日 A００１製造部 2023-04-01 A001 管理部 2022-10-01 A001 人事部 2021-04-01 イベントが発生した日　開始日 = 異動日　終了日 = 次の異動日 -1日

Slide 35

Slide 35 text

つまりそれってイミュータブルデータモデルでは？ ● 雑に言うとそうです ● 更新(UPDATE)を止める ○ UPDATE で変更前の事実を失わないようにする ● INSERT(or DELETE) だけでデータを記録していく

Slide 36

Slide 36 text

つまりそれってイミュータブルデータモデルでは？ ● 雑に言うとそうです ● 更新(UPDATE)を止める ○ UPDATE で事実を失わないようにする ● INSERT(or DELETE) だけでデータを記録していく詳しくはこちらの「イミュータブルデータモデルで始める実践データモデリング業務の複雑さをシンプルに表現！」を読んでくれ！

Slide 37

Slide 37 text

改善案 1. メリット a. 間違ったデータの混入が防げる b. INSERTだけで済むようになった 2. デメリット a. 終了日がなくなったので期間の導出が面倒になった

Slide 38

Slide 38 text

Slide 39

Slide 39 text

期間を計算する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 日する

Slide 40

Slide 40 text

Slide 41

Slide 41 text

まとめ ● 事業ドメインの理解をしよう ○ 要望を（何も考えずに）そのまま実現するのをやめよう ● データのライフサイクルを考えましょう ● 事実は消えないように設計しましょう ● 問題の捉え方を変えることによって不具合を未然に防ごう ● 設計をしても、よい設計はないか考え続けましょう

Slide 42

Slide 42 text

参考記事・スライド ● 履歴テーブルについて - 一休.com Developers Blog ● 変更履歴を持つテーブルの設計 #Database - Qiita ● 『失敗から学ぶ RDBの正しい歩き方』 ○ 「Column非正規化と履歴データの違い」 ● 『WEB+DB PRESS Vol.130』 ○ 「イミュータブルデータモデルで始める実践データモデリング業務の複雑さをシンプルに表現！」