SmartHRにおけるBiTemporal Data Modelの実践のその後 / After the practice of BiTemporal Data Model in SmartHR

Transcript

1. CONFIDENTIAL SmartHRにおける BiTemporal Data Modelのその後 2021.09.15 Wed. iCARE Dev Meetup #25 wata727 SmartHR プロダクトエンジニア
2. CONFIDENTIAL
3. CONFIDENTIAL
4. CONFIDENTIAL
5. CONFIDENTIAL 従業員データベース 部署の異動や引越しなど、従業員に関わるさまざまな変更の履歴をすべて残したい 入社 異動 結婚 出産
6. CONFIDENTIAL (Bi)Temporal Data Model 各レコードが有効な時間の開始時点と終了時点を持つ ID 名前 役職 開始 終了 追加 削除 1 田中 - 9/1 ∞ 9/1 9/15 1 田中 - 9/1 9/15 9/15 ∞ 1 田中 部長 9/15 ∞ 9/15 ∞
7. CONFIDENTIAL (Bi)Temporal Data Model 各レコードが有効な時間の開始時点と終了時点を持つ ID 名前 役職 開始 終了 追加 削除 1 田中 - 9/1 ∞ 9/1 9/15 1 田中 - 9/1 9/15 9/15 ∞ 1 田中 部長 9/15 ∞ 9/15 ∞ 9/10に役職なしだったという記録
8. CONFIDENTIAL (Bi)Temporal Data Model 各レコードが有効な時間の開始時点と終了時点を持つ ID 名前 役職 開始 終了 追加 削除 1 田中 - 9/1 ∞ 9/1 9/15 1 田中 - 9/1 9/15 9/15 ∞ 1 田中 部長 9/15 ∞ 9/15 ∞ 9/10に役職なしだったという記録 9/20に部長だったという記録
9. CONFIDENTIAL ActiveRecord::Bitemporal Active Record の _create_record や _update_row を上書きして、直接更新する代わりに 履歴レコードを積み上げる拡張Gem 通常のクエリを書き換えて、現在時点（または指定された時点）で有効なレコードのみを返 すことができる（WHERE句による絞り込みを暗に付与する） https://github.com/kufu/activerecord-bitemporal
10. CONFIDENTIAL ActiveRecord::Bitemporal Timecop.freeze("2021/09/01") { employee = Employee.create(name: "田中") } ID 名前 役職 開始 終了 追加 削除 1 田中 - 9/1 ∞ 9/1 ∞
11. CONFIDENTIAL ActiveRecord::Bitemporal Timecop.freeze("2021/09/15") { employee.update!(job_title: "部長") } ID 名前 役職 開始 終了 追加 削除 1 田中 - 9/1 ∞ 9/1 ∞ → 9/15 1 田中 - 9/1 9/15 9/15 ∞ 1 田中 部長 9/15 ∞ 9/15 ∞
12. CONFIDENTIAL ActiveRecord::Bitemporal Timecop.freeze("2021/08/30") { employee.reload # => RecordNotFound } Timecop.freeze("2021/09/10") { employee.reload.job_title # => nil } Timecop.freeze("2021/09/20") { employee.reload.job_title # => 部長 } ID 名前 役職 開始 終了 追加 削除 1 田中 - 9/1 ∞ 9/1 9/15 1 田中 - 9/1 9/15 9/15 ∞ 1 田中 部長 9/15 ∞ 9/15 ∞
13. CONFIDENTIAL 詳しい話は以前の発表で
14. CONFIDENTIAL https://speakerdeck.com/f440/implementing-command-history-and-temporal-access
15. CONFIDENTIAL その後、どうですか？
16. CONFIDENTIAL いろいろつらい！！！
17. CONFIDENTIAL 今日は (Bi)Temporal Data Model + Active Record の難しさについてお話しします
18. CONFIDENTIAL 最古のレコード追加時の起点 どうするの問題
19. CONFIDENTIAL 最古のレコード追加時の起点どうするの問題 • 9/15に登録した従業員の9/1からの情報を登録したい ◦ 9/15以降にしか有効なレコードが存在しない ◦ 最古（9/15以前）のレコードを追加するときの起点が存在しない ID 名前 役職 開始 終了 1 田中 部長 9/15 ∞
20. CONFIDENTIAL 最古のレコード追加時の起点どうするの問題 Timecop.freeze("2021/09/01") do employee = Employee.find(id) employee.update!(name: "高橋") end
21. CONFIDENTIAL 最古のレコード追加時の起点どうするの問題 Timecop.freeze("2021/09/01") do employee = Employee.find(id) employee.update!(name: "高橋") end 9/1の情報を追加したい
22. CONFIDENTIAL 最古のレコード追加時の起点どうするの問題 Timecop.freeze("2021/09/01") do employee = Employee.find(id) employee.update!(name: "高橋") end ActiveRecord::RecordNotFound！！！！！
23. CONFIDENTIAL 考えられる解決策 • まっさらなレコードとして登録する（起点なし） • 最も近い未来のレコードを起点にする
24. CONFIDENTIAL まっさらなレコードとして登録する（起点なし） • Pros ◦ 実装はスッキリしそう • Cons ◦ 未入力の項目は空になる（すべての項目を入力しなければいけない） ID 名前 役職 開始 終了 1 田中 部長 9/15 ∞ 1 高橋 - 9/1 9/15
25. CONFIDENTIAL まっさらなレコードとして登録する（起点なし） ちなみに、未来の日付へのレコード追加（この例だと9/20）は、未入力の項目に対して、直 前のレコードを引き継ぐ（役職は部長になる） ID 名前 役職 開始 終了 1 高橋 部長 9/20 ∞ 1 田中 部長 9/15 9/20 1 高橋 - 9/1 9/15
26. CONFIDENTIAL 最も近い未来のレコードを起点にする • Pros ◦ 変更したい項目だけ入力すれば、他は近いレコードから引き継げる • Cons ◦ 実装は複雑になりがち ID 名前 役職 開始 終了 1 田中 部長 9/15 ∞ 1 高橋 部長 9/1 9/15
27. CONFIDENTIAL 最も近い未来のレコードを起点にする Timecop.freeze("2021/09/01") do employee = Employee.find_by(bitemporal_id: id) unless employee earliest_employee = Employee.ignore_valid_datetime.where(bitemporal_id: id).order(:valid_from).first Timecop.freeze(earliest_employee.valid_from) do employee = Employee.find(id: id) end end employee.update!(name: "Tom") end
28. CONFIDENTIAL 最も近い未来のレコードを起点にする Timecop.freeze("2021/09/01") do employee = Employee.find_by(bitemporal_id: id) unless employee earliest_employee = Employee.ignore_valid_datetime.where(bitemporal_id: id).order(:valid_from).first Timecop.freeze(earliest_employee.valid_from) do employee = Employee.find(id: id) end end employee.update!(name: "Tom") end 9/1の情報を追加したい
29. CONFIDENTIAL 最も近い未来のレコードを起点にする Timecop.freeze("2021/09/01") do employee = Employee.find_by(bitemporal_id: id) unless employee earliest_employee = Employee.ignore_valid_datetime.where(bitemporal_id: id).order(:valid_from).first Timecop.freeze(earliest_employee.valid_from) do employee = Employee.find(id: id) end end employee.update!(name: "Tom") end 9/1の情報を追加したい 最も近い未来のレコードを探す
30. CONFIDENTIAL 最も近い未来のレコードを起点にする Timecop.freeze("2021/09/01") do employee = Employee.find_by(bitemporal_id: id) unless employee earliest_employee = Employee.ignore_valid_datetime.where(bitemporal_id: id).order(:valid_from).first Timecop.freeze(earliest_employee.valid_from) do employee = Employee.find(id: id) end end employee.update!(name: "Tom") end 9/1の情報を追加したい 起点となる日付に固定してイ ンスタンスを取得 最も近い未来のレコードを探す
31. CONFIDENTIAL 最も近い未来のレコードを起点にする Timecop.freeze("2021/09/01") do employee = Employee.find_by(bitemporal_id: id) unless employee earliest_employee = Employee.ignore_valid_datetime.where(bitemporal_id: id).order(:valid_from).first Timecop.freeze(earliest_employee.valid_from) do employee = Employee.find(id: id) end end employee.update!(name: "Tom") end 9/1の情報を追加したい 起点となる日付に固定してイ ンスタンスを取得 9/1に戻って更新 最も近い未来のレコードを探す
32. CONFIDENTIAL 最も近い未来のレコードを起点にする Timecop.freeze("2021/09/01") do employee = Employee.find_by(bitemporal_id: id) unless employee earliest_employee = Employee.ignore_valid_datetime.where(bitemporal_id: id).order(:valid_from).first Timecop.freeze(earliest_employee.valid_from) do employee = Employee.find(id: id) end end employee.update!(name: "Tom") end
33. CONFIDENTIAL 最も近い未来のレコードを起点にする Timecop.freeze("2021/09/01") do employee = Employee.find_by(bitemporal_id: id) unless employee earliest_employee = Employee.ignore_valid_datetime.where(bitemporal_id: id).order(:valid_from).first Timecop.freeze(earliest_employee.valid_from) do employee = Employee.find(id: id) end end employee.update!(name: "Tom") end 9/1として処理される 9/15として処理される 9/1として処理されるが、インスタンスは 9/15の時点のデータを保持する
34. CONFIDENTIAL どうしているのか • 最も近い未来のレコードを起点にしている • 実装はとてもむずかしい • そもそも時間の流れと逆方向にデータを入れるのをやめたいが、段階的に過去の データを移行するような計画で導入するならば必要になる
35. CONFIDENTIAL 一意制約つらい問題
36. CONFIDENTIAL 一意制約つらい問題 • 次のような二人の従業員の履歴を考えてみる ◦ 社員番号は一意 ID 名前 社員番号 開始 終了 1 松田 3 8/15 ∞ 1 松田 1 8/1 8/15 2 山本 4 9/1 ∞ 2 山本 2 8/1 9/1
37. CONFIDENTIAL 一意制約つらい問題 8/15 9/1 社員番号: 1 社員番号: 2 社員番号: 3 社員番号: 4 8/1 松田さん 山本さん
38. CONFIDENTIAL 一意制約つらい問題 8/15 9/1 社員番号: 1 社員番号: 2 社員番号: 3 社員番号: 4 8/1 松田さん 山本さん 8/10に社員番号3の レコードは入れられるか？
39. CONFIDENTIAL 一意制約つらい問題 8/15 9/1 社員番号: 1 社員番号: 2 社員番号: 3 社員番号: 4 8/1 松田さん 山本さん 8/10に社員番号3の レコードは入れられるか？
40. CONFIDENTIAL 一意制約つらい問題 8/15 9/1 社員番号: 1 社員番号: 2 社員番号: 3 社員番号: 4 8/1 松田さん 山本さん 8/10 8/10時点を見れば、社員番号 3 は確かに使われていない
41. CONFIDENTIAL 実際に追加される履歴 8/15 9/1 社員番号: 1 社員番号: 2 社員番号: 3 社員番号: 4 8/1 松田さん 山本さん 8/10 社員番号: 3
42. CONFIDENTIAL 実際に追加される履歴 8/15 9/1 社員番号: 1 社員番号: 2 社員番号: 3 社員番号: 4 8/1 松田さん 山本さん 8/10 社員番号: 3
43. CONFIDENTIAL 実際に追加される履歴 8/15 9/1 社員番号: 1 社員番号: 2 社員番号: 3 社員番号: 4 8/1 松田さん 山本さん 8/10 社員番号: 3 一意制約に違反する期間！！
44. CONFIDENTIAL どうしてこうなった • 開始時点を指定すると、終了時点は暗黙に決定される ◦ 今回の例で言うと、9/1まで有効なレコードが追加される ◦ 8/10を指定したときに、終了時点が暗黙に決定され、かつその期間で一意制約 に違反することを正しくフィードバックするのは困難
45. CONFIDENTIAL 考えられる解決策 • 一意制約に違反する期間は空白の履歴を許容する
46. CONFIDENTIAL 一意制約に違反する期間は空白の履歴を許容する 8/15 9/1 社員番号: 1 社員番号: 2 社員番号: 3 社員番号: 4 8/1 松田さん 山本さん 8/10 社員番号: 3
47. CONFIDENTIAL 一意制約に違反する期間は空白の履歴を許容する 8/15 9/1 社員番号: 1 社員番号: 2 社員番号: 3 社員番号: 4 8/1 松田さん 山本さん 8/10 社員番号: 3 8/15から9/1まで所属してい ないことになってしまう
48. CONFIDENTIAL どうしているのか • 検討中... • 何か良い方法があったら助けて欲しい...
49. CONFIDENTIAL リレーションつらい問題
50. CONFIDENTIAL リレーションつらい問題 Active Recordは他のモデルを関連づけることができる class Employee has_one :job_title end
51. CONFIDENTIAL リレーションつらい問題 Active Recordは他のモデルを関連づけることができる class Employee has_one :job_title end これも BiTemporal Dataだったら？
52. CONFIDENTIAL それぞれが異なる有効期間を持つ 9/1 Employee Job title: 部長 Job title: VP 8/1
53. CONFIDENTIAL それぞれが異なる有効期間を持つ 9/1 Employee Job title: 部長 Job title: VP 8/1 9/1に役職名が英語表記（ VP）に変わった
54. CONFIDENTIAL それぞれが異なる有効期間を持つ 9/1 Employee Job title: 部長 Job title: VP 8/1 Employeeテーブルだけでは履歴を表現できない！
55. CONFIDENTIAL 考えられる解決策 • JOINしてうまく表示できるように頑張る • モデルの更新時にリレーションも一緒に更新する • リレーションをなるべく使わない
56. CONFIDENTIAL JOINしてうまく表示できるように頑張る • Pros ◦ データ構造には手を入れなくて良いので楽 • Cons ◦ リレーションが増えるごとに計算コストが高くなる ◦ 実装が複雑化する
57. CONFIDENTIAL モデルの更新時にリレーションも更新する 9/1 Employee Job title: 部長 Job title: VP 8/1 Employee
58. CONFIDENTIAL モデルの更新時にリレーションも更新する • Pros ◦ 素直にEmployeeの履歴を表示できる • Cons ◦ リレーションが増えるごとに更新のコストが高くなる ◦ 正規化とはなんだったのか
59. CONFIDENTIAL リレーションをなるべく使わない • Pros ◦ 素直にEmployeeの履歴を表示できる • Cons ◦ 普通のRailsアプリとは異なる考え方でDBの再設計が必要になる
60. CONFIDENTIAL どうしているのか • 検討中... • 第一正規形に寄せていくのが正解かもしれない
61. CONFIDENTIAL まとめ
62. CONFIDENTIAL まとめ • 単純に ActiveRecord::Bitemporal を include して対応完了！とはならない • 一意制約やデータベースの正規化など、仕様からDB設計まで幅広い領域で BiTemporal Data Model 前提に再設計する必要がある • 至高の人事DBを求める旅はまだまだ続く...