SQLアンチパターンから学ぶ『中間テーブル』を使用するメリット

社内の勉強会の発表資料

Transcript

1. SQLアンチパターンから学ぶ！ 「多対多には中間テーブルつけておけ」 の理由を理解する
2. ジェイウォーク - 信号無視
3. ジェイウォーク - 信号無視 id 1 1, 2 xxxxxxx-xxxxxx xxxxxxx-yyyyyy 1 2 user_id account ⼝座テーブル こんなテーブル⾒たことありませんか？ ⼀つのカラムに『カンマ区切り』で複数の値が⼊っている
4. ジェイウォーク - 信号無視 id 1 1, 2 xxxxxxx-xxxxxx xxxxxxx-yyyyyy 1 2 user_id account ⼝座テーブル こんなテーブル⾒たことありませんか？ これ、アンチパターンです！
5. ジェイウォーク - 信号無視 この現象はどうやって起きるのか？ ⼝座 ユーザー ⼀⼈で複数の⼝座を持てる。 ⼝座は複数⼈で扱うことはできない ⼀⼈で複数の⼝座を持てる。 １つの⼝座（会社の⼝座）を複数⼈で扱える ⽇が過ぎて仕様変更が⼊る・・・ +
6. ジェイウォーク - 信号無視 この現象はどうやって起きるのか？ id 1 ⼭本ジェイ ⽥中ウォーク 2 name ユーザーテーブル (users) id: int 1 1 xxxxxxx-xxxxxx xxxxxxx-yyyyyy 1 2 user_id: int account: varchar(100) ⼝座テーブル (accounts) 3 2 zzzzzzz-zzzzzzz ← この⼝座は複数⼈で管理したい 少しでも変更を少なくしたいなあ エンジニアの気持ち
7. ジェイウォーク - 信号無視 この現象はどうやって起きるのか？ id 1 ⼭本ジェイ ⽥中ウォーク 2 name ユーザーテーブル (users) id: int 1 1 xxxxxxx-xxxxxx xxxxxxx-yyyyyy 1 2 user_id: varchar(30) account: varchar(100) ⼝座テーブル (accounts) 3 1, 2 zzzzzzz-zzzzzzz 1 user_idのカラムの型をintからvarcharに変更 2 user_idカラムにカンマ区切りでuser_idを挿⼊ 変更点
8. ジェイウォーク - 信号無視 これの何が問題なの？ 1 ユーザーIDカラムの型がvarcharになったことで数値以外も⼊ってしまう 2 クエリの作成が難しくなる（例）ユーザーの共有解除 3 登録できるユーザーがuser_idの桁数によって変化してしまう
9. ジェイウォーク - 信号無視 1 ユーザーIDカラムの型がvarcharになったことで数値以外も⼊ってしまう id: int 1 1 xxxxxxx-xxxxxx xxxxxxx-yyyyyy 1 2 user_id: int account: varchar(100) ⼝座テーブル (accounts) 3 2 zzzzzzz-zzzzzzz id: int 1 1 xxxxxxx-xxxxxx xxxxxxx-yyyyyy 1 2 user_id: int account: varchar(100) ⼝座テーブル (accounts) 3 Sample zzzzzzz-zzzzzzz id: int 1 1 xxxxxxx-xxxxxx xxxxxxx-yyyyyy 1 2 user_id: varchar(30) account: varchar(100) ⼝座テーブル (accounts) 3 2, Sample zzzzzzz-zzzzzzz 数値じゃない！ ⽂字列が⼊るので問題なし！ 変更前 変更後
10. ジェイウォーク - 信号無視 1 ユーザーIDカラムの型がvarcharになったことで数値以外も⼊るようになった id: int 1 1 xxxxxxx-xxxxxx xxxxxxx-yyyyyy 1 2 user_id: int account: varchar(100) ⼝座テーブル (accounts) 3 2 zzzzzzz-zzzzzzz id: int 1 1 xxxxxxx-xxxxxx xxxxxxx-yyyyyy 1 2 user_id: int account: varchar(100) ⼝座テーブル (accounts) 3 Sample zzzzzzz-zzzzzzz id: int 1 1 xxxxxxx-xxxxxx xxxxxxx-yyyyyy 1 2 user_id: varchar(30) account: varchar(100) ⼝座テーブル (accounts) 3 2, Sample zzzzzzz-zzzzzzz 数値じゃない！ ⽂字列が⼊るので問題なし！ 変更前 変更後 なんでも⼊⼒できるので データが意味不明になる
11. ジェイウォーク - 信号無視 2 クエリの作成が難しくなる（例）ユーザーの共有解除 <?php $stmt = $pdo->query( “SELECT user_id FROM accounts WHERE id = 3” ); $row = $stme->fetch(); $contact_list = $row[ʻuser_idʼ]; $value_to_remove = 2; // 削除したいユーザーのID $contact_list = explode(ʻ,ʼ, $contact_list); $key_to_remove = array_search($value_to_remove, $contact_list); unset($contact_list[$key_to_remove]); $contact_list = join(ʻ,ʼ, $contact_list); $stmt = $pdo->prepare( “UPDATE accounts SET user_id = ? WHERE id = 1” ); $stmt->excute(array($contact_list)); id: int 1 1 xxxxxxx-xxxxxx xxxxxxx-yyyyyy 1 2 user_id: varchar(30) account: varchar(100) ⼝座テーブル (accounts) 3 1, 2 zzzzzzz-zzzzzzz Q. この⼝座をユーザー2が扱えないように設定しなおしたい A
12. ジェイウォーク - 信号無視 2 クエリの作成が難しくなる（例）ユーザーの共有解除 <?php $stmt = $pdo->query( “SELECT user_id FROM accounts WHERE id = 3” ); $row = $stme->fetch(); $contact_list = $row[ʻuser_idʼ]; $value_to_remove = 2; // 削除したいユーザーのID $contact_list = explode(ʻ,ʼ, $contact_list); $key_to_remove = array_search($value_to_remove, $contact_list); unset($contact_list[$key_to_remove]); $contact_list = join(ʻ,ʼ, $contact_list); $stmt = $pdo->prepare( “UPDATE accounts SET user_id = ? WHERE id = 1” ); $stmt->excute(array($contact_list)); $contact_list = ʻ1, 2ʼ; $contact_list = [1, 2]; $contact_list = 1; id: int 1 1 xxxxxxx-xxxxxx xxxxxxx-yyyyyy 1 2 user_id: varchar(30) account: varchar(100) ⼝座テーブル (accounts) 3 1, 2 zzzzzzz-zzzzzzz Q. この⼝座をユーザー2が扱えないように設定しなおしたい A 1. user_idのカラムのデータ を⽂字列で取得 2. explodeでカンマで分割した ⽂字列の配列を取得 3. 2で取得した配列から2を 削除したものを⽂字列で取得
13. ジェイウォーク - 信号無視 2 クエリの作成が難しくなる（例）ユーザーの共有解除 <?php $stmt = $pdo->query( “SELECT user_id FROM accounts WHERE id = 3” ); $row = $stme->fetch(); $contact_list = $row[ʻuser_idʼ]; $value_to_remove = 2; // 削除したいユーザーのID $contact_list = explode(ʻ,ʼ, $contact_list); $key_to_remove = array_search($value_to_remove, $contact_list); unset($contact_list[$key_to_remove]); $contact_list = join(ʻ,ʼ, $contact_list); $stmt = $pdo->prepare( “UPDATE accounts SET user_id = ? WHERE id = 1” ); $stmt->excute(array($contact_list)); id: int 1 1 xxxxxxx-xxxxxx xxxxxxx-yyyyyy 1 2 user_id: varchar(30) account: varchar(100) ⼝座テーブル (accounts) 3 1, 2 zzzzzzz-zzzzzzz Q. この⼝座をユーザー2が扱えないように設定しなおしたい A SQL + PHP両⽅ゴリゴリ書かないといけない
14. ジェイウォーク - 信号無視 3 登録できるユーザーがuser_idの桁数によって変化してしまう id: int 1 ??? xxxxxxx-xxxxxx xxxxxxx-yyyyyy 1 2 user_id: varchar(30) account: varchar(100) ⼝座テーブル (accounts) 3 2 zzzzzzz-zzzzzzz ʼ10,11,12,13,14,20,21,30,35,38ʼ 『10⼈』のユーザーが登録できる ʼ1020,1100,1210,1321,1433,2019,2100,3099…ʼ 『6⼈』のユーザーしか登録できない
15. ジェイウォーク - 信号無視 じゃあ、どうすればいいの？ id: int 1 1 xxxxxxx-xxxxxx xxxxxxx-yyyyyy 1 2 user_id: int account: varchar(100) ⼝座テーブル (accounts) 3 2 zzzzzzz-zzzzzzz id: int 1 ⼭本ジェイ ⽥中ウォーク 2 name: varchar(30) ユーザーテーブル (users) 多 多
16. ジェイウォーク - 信号無視 じゃあ、どうすればいいの？ id: int 1 xxxxxxx-xxxxxx xxxxxxx-yyyyyy 2 account: varchar(100) ⼝座テーブル (accounts) 3 zzzzzzz-zzzzzzz id: int 1 ⼭本ジェイ ⽥中ウォーク 2 name: varchar(30) ユーザーテーブル (users) user_id 1 3 1 2 account_id ⼝座とユーザーの中間テーブル (user_account_management) 2 3 1 1 多 多 中間テーブルを作る ※ 同じペアが複数存在する状況はおかしいので、 user_idとaccount_idのペアにUNIQUE制限をかけること！
17. ジェイウォーク - 信号無視 1 ユーザーIDカラムの型がvarcharになったことで数値以外も⼊ってしまう user_id: int 1 3 1 2 account_id: int ⼝座とユーザーの中間テーブル 2 3 数値しか⼊らなくなったので、 想定外の⽂字が⼊⼒されていないかを ⼼配する必要がなくなった！
18. ジェイウォーク - 信号無視 2 クエリの作成が難しくなる（例）ユーザーの共有解除 <?php $stmt = $pdo->query( “DELETE FROM user_account_management WHERE account_id = 3 AND user_id = 2” ); $stmt->excute(array($contact_list)); user_id 1 3 1 2 account_id ⼝座とユーザーの中間テーブル (user_account_management) 2 3 このレコードを削除すると関連がなくなるので ユーザーを解除した扱いになる！
19. ジェイウォーク - 信号無視 2 クエリの作成が難しくなる（例）ユーザーの共有解除 <?php $stmt = $pdo->query( “DELETE FROM user_account_management WHERE account_id = 3 AND user_id = 2” ); $stmt->excute(array($contact_list)); user_id 1 3 1 2 account_id ⼝座とユーザーの中間テーブル (user_account_management) 2 3 このレコードを削除すると関連がなくなるので ユーザーを解除した扱いになる！ SQLオンリーで楽になった！
20. ジェイウォーク - 信号無視 3 登録できるユーザーがuser_idの桁数によって変化してしまう これに関しては、制限をかけないければ無限に登録ができるようになった！ 逆に制限をかけたい場合は以下の⼿順で簡単に⾏える！ （１）中間テーブルから特定の⼝座と紐づいているユーザー数を取得（COUNTなどで取れるよ！） （２）取得したユーザー数が制限数を上回っていないか確認 （３）追加で登録しても⼤丈夫なら中間テーブルに追加