Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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

Kazuma Watanabe

September 15, 2021
Tweet

More Decks by Kazuma Watanabe

Other Decks in Programming

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を求める旅はまだまだ続く...