Tweet
Tweet

Slide 1

Slide 1 text

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


Slide 2

Slide 2 text

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


Slide 3

Slide 3 text

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


Slide 4

Slide 4 text

4 SQLアンチパターン
 呼んだことありますか？
 （ありますよね？）


Slide 5

Slide 5 text

5 Bill Karwin(著). 和田 卓人, 和田 省二(監訳). 児島 修(訳) 『SQLアンチパターン』. オライリー・ジャパン. 2013年, 352ページ 


Slide 6

Slide 6 text

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


Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

１．今回の開発内容
 8

Slide 9

Slide 9 text

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


Slide 10

Slide 10 text

１．今回の開発内容
 10

Slide 11

Slide 11 text

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


Slide 12

Slide 12 text

２．修正前のテーブル構成
 12

Slide 13

Slide 13 text

２．修正前のテーブル構成
 13

Slide 14

Slide 14 text

２．修正前のテーブル構成
 14

Slide 15

Slide 15 text

２．修正前のテーブル構成
 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:マスター） ※一部のカラムのみ抜粋


Slide 16

Slide 16 text

２．修正前のテーブル構成
 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

Slide 17

Slide 17 text

３．アンチパターンになった箇所
 17

Slide 18

Slide 18 text

３．アンチパターンになった箇所
 どこが該当しそうなのか？
 ● 5章：EAV（エンティティ・アトリビュート・バリュー）
 ● 6章：ポリモーフィック関連
 18

Slide 19

Slide 19 text

３．アンチパターンになった箇所
 5章：EAV（エンティティ・アトリビュート・バリュー）
 可変属性を表現するために attr_name と attr_value を持つテーブルを作成する
 19 issue_id attr_name attr_value 1234 status FAILED 1234 description 保存処理に失敗する 1235 status SUCCESS 1235 description 保存処理に成功する

Slide 20

Slide 20 text

３．アンチパターンになった箇所
 6章：ポリモーフィック関連
 １つのモデルに対して複数のモデルを関連させるために、typeとidを持つ
 20

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

３．アンチパターンになった箇所
 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

Slide 23

Slide 23 text

３．アンチパターンになった箇所
 一つのモデルに対して複数のモデルを関連させようとしており
 6章：ポリモーフィック関連
 が該当している様子
 23

Slide 24

Slide 24 text

4．何が駄目なのか
 24

Slide 25

Slide 25 text

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


Slide 26

Slide 26 text

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


Slide 27

Slide 27 text

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


Slide 28

Slide 28 text

５．修正後のテーブル構成
 28

Slide 29

Slide 29 text

５．修正後のテーブル構成
 29

Slide 30

Slide 30 text

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


Slide 31

Slide 31 text

５．修正後のテーブル構成
 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 グラフ系列シート項目／グラフ横軸シート項目
 グラフ系列所属グループ／グラフ系列所属グループ


Slide 32

Slide 32 text

６．発生した問題点
 32

Slide 33

Slide 33 text

６．発生した問題点
 33 問題となったのは以下の１点
 ● 排他の制御が出来ない
 


Slide 34

Slide 34 text

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


Slide 35

Slide 35 text

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


Slide 36

Slide 36 text

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


Slide 37

Slide 37 text

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


Slide 38

Slide 38 text

７．まとめ
 38

Slide 39

Slide 39 text

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


Slide 40

Slide 40 text

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


Slide 41

Slide 41 text

41 えっ！？


Slide 42

Slide 42 text

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


Slide 43

Slide 43 text

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


Slide 44

Slide 44 text

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


Slide 45

Slide 45 text

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


Slide 46

Slide 46 text

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