テーブル設計のよくある罠と それに対する処方箋

【DMM×バイセル】若手エンジニアによる学生向けTech Study Summit
（https://dmm-recruit.connpass.com/event/316914/）
登壇資料

Transcript

1. テーブル設計のよくある罠と それに対する処方箋 2024/05/15 株式会社BuySell Technogies 岩下拳勝
2. 2 自己紹介 名前 岩下 拳勝 各種SNSアカウント x: @iwashi623 GitHub: @iwashi623 入社時期 2023年7月 所属・職域 店舗買取アプリケーション Storeチーム・BE/インフラ担当 趣味 プロ野球観戦・散歩・旅行
3. 3 アジェンダ 1. 自己紹介 2. 導入 3. サンプルアプリ紹介 4. 罠1. 乱用されるフラグ 5. 罠2. 悲しい非正規化 6. 罠3. 消えた履歴 7. まとめ 8. 参考文献
4. 4 アジェンダ 1. 自己紹介 2. 導入 3. サンプルアプリ紹介 4. 罠1. 乱用されるフラグ 5. 罠2. 悲しい非正規化 6. 罠3. 消えた履歴 7. まとめ 8. 参考文献
5. 5 導入 皆さん、RDBMS使ってますか？？
6. 6 導入 Web開発では、アプリケーションのデータを永続化したいときに、 RDBMS（Relational Database Management System）をよく使います。 有名なRDBMSとして、 • MySQL • PostgreSQL • SQL Server • Oracle Dabatase などがあります。
7. 7 導入 Web開発では、アプリケーションのデータを永続化したいときに、 RDBMS（Relational Database Management System）をよく使います。 有名なRDBMSとして、 • MySQL • PostgreSQL • SQL Server • Oracle Dabatase などがあります。 ← StoreではPostgreSQLを採用しています。
8. 8 導入 今日は、そんなRDBMSを使うにあたって必要な テーブル設計の話をします
9. 9 アジェンダ 1. 自己紹介 2. 導入 3. サンプルアプリ紹介 4. 罠1. 乱用されるフラグ 5. 罠2. 悲しい非正規化 6. 罠3. 消えた履歴 7. まとめ 8. 参考文献
10. 10 サンプルアプリケーション紹介 よくある記事投稿アプリケーションを作るシチュエーションを想定して、ケーススタディ的に発表を進め ます。 アプリケーションの最初の要件は以下です。 要件 • ユーザーが登録できること • 記事を投稿できること ◦ 記事は下書き（Draft）、公開済み（Published）、非公開（Unpublished）に状態変化できること ◦ 記事一覧画面などで使用する、サムネイル画像を添付できること • あらかじめ用意された記事種別を選択できること ◦ 記事種別・・・記事の内容ではなく形式（ニュース？ブログ？論文？ポエム？etc..）を選択する • あらかじめ用意された記事カテゴリを選択できること ◦ 記事カテゴリ・・・記事の中身でどういった内容（スポーツ？芸術？事件？etc…）を選択する
11. 11 サンプルアプリケーション紹介 パッと思いつくテーブル設計 • users(著者) ◦ ユーザー（記事作成者）を保 存する • post_categories（記事カテゴリ） • post_types（記事種別） • posts（記事） ◦ posts.statusは記事のステー タスを保存 ◦ posts.image_urlで画像を保 存する
12. 12 アジェンダ 1. 自己紹介 2. 導入 3. サンプルアプリ紹介 4. 罠1. 乱用されるフラグ 5. 罠2. 悲しい非正規化 6. 罠3. 消えた履歴 7. まとめ 8. 参考文献
13. 13 罠1. 乱用されるフラグ 追加される要件 • 記事を削除できるようにしたい。 ◦ ただし、一度公開済みで削除された記事に関しても、いつかデータとしては使うかもし れない。 ▪ つまりDB上から物理削除はしたくない。 • ユーザー、記事種別、記事カテゴリについても、削除ができるようにしたい。 ◦ ユーザー、記事種別、記事カテゴリが削除されている場合は、それに紐づく記事は非 公開としたい。 ▪ もちろんこちらも物理削除はしたくない。
14. 14 罠1. 乱用されるフラグ 追加される要件 • 記事を削除できるようにしたい。 ◦ ただし、一度公開済みで削除された記事に関しても、いつかデータとしては使うかもし れない。 ▪ つまりDB上から物理削除はしたくない。 • ユーザー、記事種別、記事カテゴリについても、削除ができるようにしたい。 ◦ ユーザー、記事種別、記事カテゴリが削除されている場合は、それに紐づく記事 は非 公開としたい。 ▪ もちろんこちらも物理削除はしたくない。 本当によくある
15. 15 「おっしゃ、削除フラグだ！」
16. 16 罠1. 乱用されるフラグ テーブルにつき、一つのカラムの追加で対応できるか ら超簡単！！！ 要件をミニマムの工数で満たせるし、論理削除に対応 しているORMもたくさんあるし最高ですね！
17. 17 ほんとに？
18. 18 罠1. 乱用されるフラグ 問題点 1. 記事の取得クエリが複雑になる 2. UNIQUE制約が使えない
19. 19 罠1. 乱用されるフラグ 記事の取得クエリが複雑になる このアプリケーションで一番使われるであろう記事の取得クエリ
20. 20 罠1. 乱用されるフラグ 記事の取得クエリが複雑になる このアプリケーションで一番使われるであろう記事の取得クエリ
21. 21 罠1. 乱用されるフラグ 記事の取得クエリが複雑になる このアプリケーションで一番使われるであろう記事の取得クエリ 関連するテーブルが増えるごとに複雑度が上がっていく
22. 22 罠1. 乱用されるフラグ 記事の取得クエリが複雑になる 「公開できる記事を取得するためには、あのテーブルとあのテーブルを JOINして、WHERE検索の 条件はこうで…」のような状態になる。
23. 23 罠1. 乱用されるフラグ 記事の取得クエリが複雑になる 「公開できる記事を取得するためには、あのテーブルとあのテーブルを JOINして、WHERE検索の 条件はこうで…」のような状態になる。 仕様が変更されたときにバグを生む可能性が高い 仕様理解が難しくなる
24. 24 罠1. 乱用されるフラグ 問題点 1. 記事の取得クエリが複雑になる 2. UNIQUE制約が使えない
25. 25 罠1. 乱用されるフラグ UNIQUE制約が使えない 例えば、users.emailでUNIQUE制約を貼りたいケースがあると思います。
26. 26 罠1. 乱用されるフラグ UNIQUE制約が使えない 例えば、users.emailでUNIQUE制約を貼りたいケースがあると思います。 何も考えずに制約をかけると … 一度退会したユーザーが再 登録しようとすると… 制約に引っかかる😇
27. 27 罠1. 乱用されるフラグ とはいえ、Databaseの責務はデータを守ること → データを守るための制約は外したくない
28. 28 罠1. 乱用されるフラグ 処方箋
29. 29 乱用されるフラグ 状態を持ったテーブルを作らない
30. 30 困難は、分割せよ！
31. 31 罠1. 乱用されるフラグ 状態を持ったテーブルを作らないためには、テーブルを分割する。 triggerとか使うと データの移動が楽
32. 32 罠1. 乱用されるフラグ 削除フラグを使わなくなった場合のテーブル設計
33. 33 罠1. 乱用されるフラグ テーブルを分割するメリット • 表示できるpostを取得する際にSELECT条件に、 削除フラグの条件（WHERE句）が必要なくなる • users.emailカラムに、気軽にUNIQUE制約を貼れる
34. 34 乱用されるフラグ ところで
35. 35 罠1. 乱用されるフラグ 「posts.statusカラムって状態を持つ カラムじゃない？ テーブルで状態を持つなっていう主 張と矛盾してない？」 と思ったそこのアナタ。
36. 36 乱用されるフラグ 出典 鋼の錬金術師 荒川弘
37. 37 罠1. 乱用されるフラグ posts.statusの問題点は3つ目の問題「消えた履歴」にて解消・解説します
38. 38 アジェンダ 1. 自己紹介 2. 導入 3. サンプルアプリ紹介 4. 罠1. 乱用されるフラグ 5. 罠2. 悲しい非正規化 6. 罠3. 消えた履歴 7. まとめ 8. 参考文献
39. 39 罠2. 悲しい非正規化 追加される要件 • 画像を1枚だけじゃなく、3枚登録できるようにしてほしい。 • そのうち、1枚をサムネイル画像として登録したい。
40. 40 悲しい非正規化 よし、カラムの追加だ！！！
41. 41 罠2. 悲しい非正規化 ドンッ！！！ カラムの追加だけですごく簡単に実装できるぞ！ image_urlをサムネイル画像という扱いにして、 image_2とimage_3にその他の画像を入れればいいんだ 😤
42. 42 悲しい非正規化 ほんとに？
43. 43 罠2. 悲しい非正規化 この設計の問題点 • 画像が3枚以上増えることが考慮されていない。 • image_urlが削除されたとき、image2_urlやimage3_urlなどが登録されていてもサムネイルとして表示され ない。 • 「image2_urlが空なのに、image3_urlにはデータが登録されている」のようなことが起こり得る ◦ 画像を表示するときも、 if post.image_url != nil などの処理を書くことになって辛い。 • 単純に画像を送信するフォームを作るのと、フォームから送られたきた情報をカラムにマッピングする処理を 書くのがめんどくさすぎる。 ◦ めんどくさいということはバグを生みやすいということ。
44. 44 悲しい非正規化 処方箋
45. 45 悲しい非正規化 困難は、分割せよ！
46. 46 罠2. 悲しい非正規化 処方箋①　画像に関するデータを別テーブルにする 画像データを子テーブルとすることで、 画像の枚数の仕様が変わったとしても耐 えられる（≒仕様変更に強い）設計になっ た。
47. 47 罠2. 悲しい非正規化 処方箋② ”サムネイル画像”というデータを”画像”というデータとは別の場所に持つ ここでは、posts.thumbnail_urlというカラ ムを追加して対応している。 “画像データ”と”サムネイルデータ”という データの責務分割をする。 → サムネイルが登録されていないとい う事態を防ぎやすくなった。 注）サムネイルは通常1記事につき1枚という認識の上、カラ ム追加で対応していますが、サムネイル複数登録したかっ たらテーブル追加での対応にしてもいいかもです。
48. 48 アジェンダ 1. 自己紹介 2. 導入 3. サンプルアプリ紹介 4. 罠1. 乱用されるフラグ 5. 罠2. 悲しい非正規化 6. 罠3. 消えた履歴 7. まとめ 8. 参考文献
49. 49 罠3. 消えた履歴 追加される要件 • ユーザーに1記事あたり500円の課金をしたい。 ◦ 記事公開日時を元に請求データを作りたい。 ▪ 例えば、5月16日にpublishされた記事なら、5月分の サービス利用料としてユーザーに請求したい。
50. 50 罠3. 消えた履歴 「アプリケーションで記事が公開になるとき に、posts.published_atを更新すればい いじゃん！ すごく簡単だ！」
51. 51 罠3. 消えた履歴 数日後・・・ CS（カスタマーサポート）の人 「3月に記事を作成して料金もお支払いしたユーザーの方から、『今 月は新規の記事公開をしていないのに料金の請求書が来た！どういうことだ！』といったお問い 合わせがありました。ご確認いただけますか？」 ぼく「はい、確認します」 〜〜〜調査〜〜〜 ぼく「あ、あれ、どうなってるんだ …。 あ、posts.published_atが5月になっている…。まさか、3月に公開後に、一度非公開になってから 再公開された？ どうしよう、3月に公開された時点のデータが無くなっちゃった …」
52. 52 罠3. 消えた履歴 料金が発生した時点の 日時データが無くなることは、 会社の監査・コンプライアンスの観点からしても大 問題です。
53. 53 罠3. 消えた履歴 現在の設計の問題点 • ステータスの更新履歴のデータを保持していなかったこと。 • 公開済みの記事を取得するクエリに、 WHERE posts.status = ‘Published’という条件を毎回 つける必要があること。 ◦ つけ忘れて非公開の記事が公開されてしまったら大問題になる。
54. 54 消えた履歴 処方箋
55. 55 罠3. 消えた履歴 処方箋① 記事の更新履歴テーブルを作成する statusに更新がかかるたびに、 post_status_logsにデータをINSERTし ていく。 → こうすることで、古い公開データが 消えることなくステータスを更新でき る。 サービス使用料の請求は、最初にス テータスが公開になった日時のデータ を使って行えば良い。
56. 56 罠3. 消えた履歴 処方箋② ステータスごとにテーブルを分ける posts.statusを気にした、WHEREの複雑さから逃 げることができる。 公開済みの記事一覧がほしいときは、 published_postsからSELECTをすればいいだけ になった。非公開にするときは、 posts(親テーブ ル)でなく、published_postsのレコードを消せば良 いので、依存関係などを比較的考えなくて良くな り、気が楽になる。
57. 57 アジェンダ 1. 自己紹介 2. 導入 3. サンプルアプリ紹介 4. 罠1. 乱用されるフラグ 5. 罠2. 悲しい非正規化 6. 罠3. 消えた履歴 7. まとめ 8. 参考文献
58. 58 まとめ 基本的な性質として、 • データベースの寿命はアプリケーションより長い • データベースはアプリケーションより仕様変更に弱い ので、データベースはテーブル設計がとても重要です。 テーブルの責務が複数あるような場合やテーブルが状態を持っているような場合は、テーブル設 計の段階で分割するのがおすすめです。 また、今日紹介したのは設計の単なる一案で、 ”良い設計”はアプリケーションごとに異なります。 先輩・チームメイト・上司と会話して、用法用量を守って使用してください。
59. 59 まとめ 基本的な性質として、 • データベースの寿命はアプリケーションより長い • データベースはアプリケーションより仕様変更に弱い ので、データベースはテーブル設計がとても重要です。 テーブルの責務が複数あるような場合やテーブルが状態を持っているような場合は、テーブル設 計の段階で分割するのがおすすめです。 また、今日紹介したのは設計の単なる一案で、 ”良い設計”はアプリケーションごとに異なります。 先輩・チームメイト・上司と会話して、用法用量を守って使用してください。 困難は、分割せよ！
60. 60 参考文献 • @n_yamadamadamada(かのかの) （2023）『会員状態を例に状態管理 のデータベース設計を考える 』 • 曽根 壮大 （2015）『PostgreSQLアンチパターン』 • 曽根 壮大 （2019）『失敗から学ぶRDBの正しい歩き方』 • 和田 卓人（2015）『SQLアンチパターン 幻の第26章「とりあえず削除フラ グ」』
61. THANK YOU 61