Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
SQLアンチパターンから学ぶ『中間テーブル』を使用するメリット
Search
Kami Makoto
July 26, 2020
Technology
2
3.9k
SQLアンチパターンから学ぶ『中間テーブル』を使用するメリット
社内の勉強会の発表資料
Kami Makoto
July 26, 2020
Tweet
Share
More Decks by Kami Makoto
See All by Kami Makoto
DataHubを活用した集計基盤の構築過程と工夫したこと
kaminotsukai
0
660
AtCoderを1ヶ月全力でやって『体感』したこと
kaminotsukai
0
210
Other Decks in Technology
See All in Technology
エンジニアの生存戦略 〜クラウド潮流の経験から紐解く技術トレンドのメカニズムと乗りこなし方〜
shimy
9
1.9k
LLMアプリケーションの評価の実践と課題 ~PharmaXにおける今後の展望~
pharma_x_tech
2
160
AWSサービスメニュー開発をしていてAWSを好きだ!と感じた瞬間
toru_kubota
0
130
[I/O Extended Android 2024] What`s new in Android 2024
kyeongwan
0
220
Flutter研修【MIXI 24新卒技術研修】
mixi_engineers
PRO
0
160
AOAI Dev Day LLMシステム開発 Tips集
hirosatogamo
15
3.7k
Azure AI ことはじめ
tsubakimoto_s
0
130
AWSで”最小権限の原則”を実現するための考え方 /20240722-ssmjp-aws-least-privilege
opelab
10
4.3k
スレットハンティングについて知っておきたいこと
hacket
0
130
ゆめみのアクセシビリティの現在地と今後
ryokatsuse
3
290
20240725 LLMによるDXのビジョンと、今何からやるべきか @Azure OpenAI Service Dev Day
nrryuya
3
1.2k
プレイドにおけるDatadog APMの活用方法
plaidtech
PRO
2
120
Featured
See All Featured
The Mythical Team-Month
searls
217
43k
Easily Structure & Communicate Ideas using Wireframe
afnizarnur
189
16k
10 Git Anti Patterns You Should be Aware of
lemiorhan
652
58k
What the flash - Photography Introduction
edds
65
11k
How to name files
jennybc
67
96k
Clear Off the Table
cherdarchuk
89
320k
Happy Clients
brianwarren
94
6.5k
Become a Pro
speakerdeck
PRO
15
4.8k
[RailsConf 2023 Opening Keynote] The Magic of Rails
eileencodes
17
8.7k
JavaScript: Past, Present, and Future - NDC Porto 2020
reverentgeek
44
4.7k
Learning to Love Humans: Emotional Interface Design
aarron
269
39k
Stop Working from a Prison Cell
hatefulcrawdad
266
20k
Transcript
SQLアンチパターンから学ぶ! 「多対多には中間テーブルつけておけ」 の理由を理解する
ジェイウォーク - 信号無視
ジェイウォーク - 信号無視 id 1 1, 2 xxxxxxx-xxxxxx xxxxxxx-yyyyyy 1
2 user_id account ⼝座テーブル こんなテーブル⾒たことありませんか? ⼀つのカラムに『カンマ区切り』で複数の値が⼊っている
ジェイウォーク - 信号無視 id 1 1, 2 xxxxxxx-xxxxxx xxxxxxx-yyyyyy 1
2 user_id account ⼝座テーブル こんなテーブル⾒たことありませんか? これ、アンチパターンです!
ジェイウォーク - 信号無視 この現象はどうやって起きるのか? ⼝座 ユーザー ⼀⼈で複数の⼝座を持てる。 ⼝座は複数⼈で扱うことはできない ⼀⼈で複数の⼝座を持てる。 1つの⼝座(会社の⼝座)を複数⼈で扱える
⽇が過ぎて仕様変更が⼊る・・・ +
ジェイウォーク - 信号無視 この現象はどうやって起きるのか? 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 ← この⼝座は複数⼈で管理したい 少しでも変更を少なくしたいなあ エンジニアの気持ち
ジェイウォーク - 信号無視 この現象はどうやって起きるのか? 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を挿⼊ 変更点
ジェイウォーク - 信号無視 これの何が問題なの? 1 ユーザーIDカラムの型がvarcharになったことで数値以外も⼊ってしまう 2 クエリの作成が難しくなる(例)ユーザーの共有解除 3 登録できるユーザーがuser_idの桁数によって変化してしまう
ジェイウォーク - 信号無視 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 数値じゃない! ⽂字列が⼊るので問題なし! 変更前 変更後
ジェイウォーク - 信号無視 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 数値じゃない! ⽂字列が⼊るので問題なし! 変更前 変更後 なんでも⼊⼒できるので データが意味不明になる
ジェイウォーク - 信号無視 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
ジェイウォーク - 信号無視 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を 削除したものを⽂字列で取得
ジェイウォーク - 信号無視 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両⽅ゴリゴリ書かないといけない
ジェイウォーク - 信号無視 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⼈』のユーザーしか登録できない
ジェイウォーク - 信号無視 じゃあ、どうすればいいの? 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) 多 多
ジェイウォーク - 信号無視 じゃあ、どうすればいいの? 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制限をかけること!
ジェイウォーク - 信号無視 1 ユーザーIDカラムの型がvarcharになったことで数値以外も⼊ってしまう user_id: int 1 3 1
2 account_id: int ⼝座とユーザーの中間テーブル 2 3 数値しか⼊らなくなったので、 想定外の⽂字が⼊⼒されていないかを ⼼配する必要がなくなった!
ジェイウォーク - 信号無視 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 このレコードを削除すると関連がなくなるので ユーザーを解除した扱いになる!
ジェイウォーク - 信号無視 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オンリーで楽になった!
ジェイウォーク - 信号無視 3 登録できるユーザーがuser_idの桁数によって変化してしまう これに関しては、制限をかけないければ無限に登録ができるようになった! 逆に制限をかけたい場合は以下の⼿順で簡単に⾏える! (1)中間テーブルから特定の⼝座と紐づいているユーザー数を取得(COUNTなどで取れるよ!) (2)取得したユーザー数が制限数を上回っていないか確認 (3)追加で登録しても⼤丈夫なら中間テーブルに追加