$30 off During Our Annual Pro Sale. View Details »

SQLアンチパターンを読んでリファクタしてみた / sql-anti-pattern-refactored-2022

Genki Sano
September 28, 2022

SQLアンチパターンを読んでリファクタしてみた / sql-anti-pattern-refactored-2022

Genki Sano

September 28, 2022
Tweet

More Decks by Genki Sano

Other Decks in Programming

Transcript

  1. 佐野 元気
 SQLアンチパターンを読んで
 リファクタしてみた
 @kaonaviTechTalk #9
 2022/09/28


  2. 自己紹介
 @sanogemaru 
 2 株式会社カオナビ
 バックエンドエンジニア
 エンジニアリングマネージャー
 佐野 元気 Genki

    Sano

  3. 3 SQLアンチパターン
 呼んだことありますか?
 


  4. 4 SQLアンチパターン
 呼んだことありますか?
 (ありますよね?)


  5. 5 Bill Karwin(著). 和田 卓人, 和田 省二(監訳). 児島 修(訳) 『SQLアンチパターン』.

    オライリー・ジャパン. 2013年, 352ページ 

  6. 6 自分で設計したものが
 アンチパターンに該当したので
 リファクタリングしてみた話


  7. アジェンダ
 1. 今回の開発内容
 2. 修正前のテーブル構成
 3. アンチパターンになった箇所
 4. 何が問題なのか
 5.

    修正後のテーブル構成
 6. 発生した問題点
 7. まとめ
 7
  8. 1.今回の開発内容
 8

  9. 1.今回の開発内容
 9 • 開発する機能
 ◦ グラフの作成機能
 • 概要
 ◦ とあるタレントマネジメントシステムにおける機能追加


    ◦ 横軸と系列に対して、値を指定することで登録されているデータを
 マッピングする
 

  10. 1.今回の開発内容
 10

  11. 1.今回の開発内容
 11 横軸項目
 系列項目


  12. 2.修正前のテーブル構成
 12

  13. 2.修正前のテーブル構成
 13

  14. 2.修正前のテーブル構成
 14

  15. 2.修正前のテーブル構成
 15 column type comment id int(10) series_item_id int(10) 系列項目ID

    series_item_type tinyint(3) 系列項目タイプ(1:所属、2:マスター) x_axis_item_id int(10) 横軸項目ID x_axis_item_type tinyint(3) 横軸項目タイプ(1:所属、2:マスター) ※一部のカラムのみ抜粋

  16. 2.修正前のテーブル構成
 16 column type comment id int(10) series_item_id int(10) 系列項目ID

    series_item_type tinyint(3) 系列項目タイプ(1:所属、2:シート項目) x_axis_item_id int(10) 横軸項目ID x_axis_item_type tinyint(3) 横軸項目タイプ(1:所属、2:マスター) ※一部のカラムのみ抜粋
 所属の集計は 所属グループID マスターの集計は シート項目ID 所属の集計は 所属グループID マスターの集計は シート項目ID
  17. 3.アンチパターンになった箇所
 17

  18. 3.アンチパターンになった箇所
 どこが該当しそうなのか?
 • 5章:EAV(エンティティ・アトリビュート・バリュー)
 • 6章:ポリモーフィック関連
 18

  19. 3.アンチパターンになった箇所
 5章:EAV(エンティティ・アトリビュート・バリュー)
 可変属性を表現するために attr_name と attr_value を持つテーブルを作成する
 19 issue_id attr_name

    attr_value 1234 status FAILED 1234 description 保存処理に失敗する 1235 status SUCCESS 1235 description 保存処理に成功する
  20. 3.アンチパターンになった箇所
 6章:ポリモーフィック関連
 1つのモデルに対して複数のモデルを関連させるために、typeとidを持つ
 20

  21. 3.アンチパターンになった箇所
 もう一度、今回の該当テーブルを確認する
 21

  22. 3.アンチパターンになった箇所
 22 column type comment id int(10) series_item_id int(10) 系列項目ID

    series_item_type tinyint(3) 系列項目タイプ(1:所属、2:シート項目) x_axis_item_id int(10) 横軸項目ID x_axis_item_type tinyint(3) 横軸項目タイプ(1:所属、2:マスター) ※一部のカラムのみ抜粋
 所属の集計は 所属グループID マスターの集計は シート項目ID 所属の集計は 所属グループID マスターの集計は シート項目ID
  23. 3.アンチパターンになった箇所
 一つのモデルに対して複数のモデルを関連させようとしており
 6章:ポリモーフィック関連
 が該当している様子
 23

  24. 4.何が駄目なのか
 24

  25. 4.何が問題なのか
 25 結論から言うと、外部キー制約を定義できない こと
 


  26. 4.何が問題なのか
 26 結論から言うと、外部キー制約を定義できない こと
 → 4章:キーレスエントリ(外部キー嫌い)に該当


  27. 4.何が問題なのか
 27 結論から言うと、外部キー制約を定義できない こと
 → 4章:キーレスエントリ(外部キー嫌い)に該当
 • 参照整合性をアプリケーションで再実装する必要がある
 • 壊れた参照をどうするかという問題が生じる


    • アドホックなSQLによって壊れる

  28. 5.修正後のテーブル構成
 28

  29. 5.修正後のテーブル構成
 29

  30. 5.修正後のテーブル構成
 30 交差テーブル


  31. 5.修正後のテーブル構成
 31 column type comment chart_id int(10) グラフID sheet_item_id int(10)

    シート項目ID column type comment chart_id int(10) グラフID department_group_id int(10) 所属グループID グラフ系列シート項目/グラフ横軸シート項目
 グラフ系列所属グループ/グラフ系列所属グループ

  32. 6.発生した問題点
 32

  33. 6.発生した問題点
 33 問題となったのは以下の1点
 • 排他の制御が出来ない
 


  34. 6.発生した問題点
 34 排他の制御が出来ない
 「どちらか一方にしかレコードが入ってほしくない」のを制御できない
 


  35. 6.発生した問題点
 35 排他の制御が出来ない
 「どちらか一方にしかレコードが入ってほしくない」のを制御できない
 


  36. 6.発生した問題点
 36 排他の制御が出来ない
 「どちらか一方にしかレコードが入ってほしくない」のを制御できない
 
 どちらか一方にだけ、レコードを保持したい 


  37. 6.発生した問題点
 37 排他の制御が出来ない
 「どちらか一方にしかレコードが入ってほしくない」のを制御できない
 
 どちらか一方にしかレコードが入らないようにし たい場合、アプリでの制御が必要


  38. 7.まとめ
 38

  39. 7.まとめ
 39 Q:結局はどういう風な構成で実装していくのか?


  40. 7.まとめ
 40 Q:結局はどういう風な構成で実装していくのか?
 A:修正前のままの構成で実装予定


  41. 41 えっ!?


  42. 7.まとめ
 42 Q:どうしてそのままにしたのか?


  43. 7.まとめ
 43 Q:どうしてそのままにしたのか?
 A:以下の2点が理由
 • Eloquent Model でポリモーフィック関連をサポートしている
 • アプリケーションでの制御がしんどい


  44. 7.まとめ
 44 Eloquent Model でポリモーフィック関連をサポートしている
 > Eloquentはそうしたリレーションを簡単に管理し操作できるようにする
 > とともに、さまざまなタイプのリレーションをサポートしています。
 Laravel

    6.x Eloquent:リレーション
 

  45. 7.まとめ
 45 アプリケーションでの制御がしんどい
 結局、排他の制御などはアプリ側で実装する必要がある
 データベース側で参照整合性がそこまで担保できないので、実装が楽な方で
 いいのでは?と考えた
 


  46. 7.まとめ
 46 ご相談・ご指摘・その他
 下記のTwitterアカウントまでお願いします!
 
 @sanogemaru