SQLアンチパターンを読んでリファクタしてみた

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. １．今回の開発内容
 8
9. １．今回の開発内容
 9 • 開発する機能
 ◦ グラフの作成機能
 • 概要
 ◦ とあるタレントマネジメントシステムにおける機能追加
 ◦ 横軸と系列に対して、値を指定することで登録されているデータを
 マッピングする
 

10. １．今回の開発内容
 10
11. １．今回の開発内容
 11 横軸項目
 系列項目

12. ２．修正前のテーブル構成
 12
13. ２．修正前のテーブル構成
 13
14. ２．修正前のテーブル構成
 14
15. ２．修正前のテーブル構成
 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. ２．修正前のテーブル構成
 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. ３．アンチパターンになった箇所
 17
18. ３．アンチパターンになった箇所
 どこが該当しそうなのか？
 • 5章：EAV（エンティティ・アトリビュート・バリュー）
 • 6章：ポリモーフィック関連
 18
19. ３．アンチパターンになった箇所
 5章：EAV（エンティティ・アトリビュート・バリュー）
 可変属性を表現するために attr_name と attr_value を持つテーブルを作成する
 19 issue_id attr_name attr_value 1234 status FAILED 1234 description 保存処理に失敗する 1235 status SUCCESS 1235 description 保存処理に成功する
20. ３．アンチパターンになった箇所
 6章：ポリモーフィック関連
 １つのモデルに対して複数のモデルを関連させるために、typeとidを持つ
 20
21. ３．アンチパターンになった箇所
 もう一度、今回の該当テーブルを確認する
 21
22. ３．アンチパターンになった箇所
 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. ３．アンチパターンになった箇所
 一つのモデルに対して複数のモデルを関連させようとしており
 6章：ポリモーフィック関連
 が該当している様子
 23
24. 4．何が駄目なのか
 24
25. 4．何が問題なのか
 25 結論から言うと、外部キー制約を定義できない こと
 

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

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

28. ５．修正後のテーブル構成
 28
29. ５．修正後のテーブル構成
 29
30. ５．修正後のテーブル構成
 30 交差テーブル

31. ５．修正後のテーブル構成
 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. ６．発生した問題点
 32
33. ６．発生した問題点
 33 問題となったのは以下の１点
 • 排他の制御が出来ない
 

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

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

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

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

38. ７．まとめ
 38
39. ７．まとめ
 39 Q：結局はどういう風な構成で実装していくのか？

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

41. 41 えっ！？

42. ７．まとめ
 42 Q：どうしてそのままにしたのか？

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

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

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

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