SQLアンチパターンから学ぶテーブル設計

Transcript

1. @akeno_0810 2022.05.28 SQLアンチパターン から学ぶテーブル設計 ココカラ勉強会 No.9
2. 自己紹介 About me akeno (@akeno_0810) Webエンジニア歴2年くらい Rust, API/コード設計, DevOps/開発の効率化 触っている技術 最近興味のある分野
3. SQLアンチパターン
4. SQLアンチパターン リレーショナルデータベースを使う際のアンチパターンP B 論理設# B 物理設# B クエ B アプリケーション の4種類、25パターン 実際に出会ったパターンをピックアップして紹介 （スマホでスキャンしたので画像が雑です）
5. 論理設計 Entity Attribute Value ポリモーフィック
6. Entity Attribute Value 可変属性をサポートする方法 KVSのようにRDSでも柔軟なデータ構造を持ちたい… † カラム名を用いた検索が出来な“ † 入っている値はアプリケーションが保 証する必要があx † 構造をプログラマが管理しなければな らない
7. Entity Attribute Value 基本的には向いてないので、ある程度の種類に絞れる場合を考える シングルテーブル継’ e ひたすらカラムを増やb e NULLが入るカラムが多くなる 具象テーブル継’ e テーブルを完全に分けV e 共通データとして扱いづらい クラステーブル継’ e 共通部と個別部のテーブルを分けV e 共通部の切り出しが難しい・参照が面倒
8. ポリモーフィック シングルテーブル バリエーションのあるテーブルに関連を持たせたい場合 クラステーブル 具象テーブル(Bad) 具象テーブル(Good) Commentsテーブルに どちらのテーブルを参 照するか（メタデー タ）を持たせるという パターンが辛い
9. Entity Attribute Value / ポリモーフィック 経験談と感想 KVSの形はマスターデータを保存する場合はありだと思う EAVはなんだかんだ見ることが多いが、コメントなしでは誰も読めないことも多い →できる限り避けていきたい気持ち 商品と特定の属性が追加された商品のパターンは見たことがある メタデータがデータに混入する問題・柔軟性は怖い ORMを使ったら実装していいって書いてるけど、それでも辛かった 結局用途に寄るのだが、シングルテーブルorクラステーブルを使いたくなる
10. 物理設計 31のフレーバー
11. 31のフレーバー 限られた値のみを格納するカラムを作成する方法 ステータスや性別等、限られた値を保持する際にENUMを使ってしまうとV P 既存の値リストがわからなE P 追加がしづらE P 移植しづらい といった問題が発生する。 解決策としてはマスターテーブルからデータ参照して外部キーで縛ること。
12. 31のフレーバー 経験談と感想 出来るだけマスターテーブルを用いていきたい。 入る値が一覧できるのと、値を縛れるのが有用。 この辺をアプリケーション側で担保するのは面倒。 tinyint型で管理する手もあるが、コメントが無くて分からなくなるパターンがある。 柔軟性を求めるならありだが、柔軟性とアンチパターンは紙一重。 けどマスターテーブル管理が面倒で数値や文字列に逃げることはまあまあある… ドキュメントやコメントでしっかり補完しておきましょう。
13. クエリ フィア・オブ・ジ・アンノウン
14. フィア・オブ・ジ・アンノウン NULLを使わない方法 unknownな値が怖い NULLとの比較はかなり特殊。 あまり使いたくないし、使う際は NULLと比較される可能性を考慮 したクエリを組む必要がある。 UNIQUE制約に縛られない点から も値としては見てない。
15. フィア・オブ・ジ・アンノウン 経験談と感想 出来るだけデフォルト値を用いていきたい。 NULLは何もないという特別な状態を示すために使う。 カラムを増やす際にNULL許可デフォルト値なしのマイグレーションがあった。 アプリケーションは動かなくなった。 column_nane != ‘検索文字列’ というクエリをnull許可のカラムに掛けていた。 期待より少なめの検索結果となった。
16. アプリケーション開発 リーダブルパスワード
17. リーダブルパスワード パスワードを安全に保存する方法 パスワードを平文で保存していたので閲覧が簡単に出来てしまった… 平文はなかなか無いと思うが、それ以外でW r SQLに平文のパスワードを入れてSQLでHash化していI r Saltがな„ r Hash化回数が不十C r 復号の必要がないが、復合可能 あたりは踏みやすい。 DBとは関係ない部分の注意点が多い。
18. リーダブルパスワード 経験談と感想 復合可能な情報を持つのは怖い。 Hash化回数が少ない場合、Saltが使われていない場合も怖い。 復合可能にするという要件があったので、実装したことはある。 keyとiv(初期化ベクトル)の扱い、大事。 パスワードのHash化は大抵の言語に専用のライブラリがあると思うので使おう。 Goだと`bcrypt.GenerateFromPassword()`がお手軽。 普通のHash関数を使って自前で実装するメリットは薄いと思う。
19. まとめ
20. Thank you! 参考資料 SQLアンチパターン https://www.oreilly.co.jp/books/9784873115894/ 読みやすい本だったので是非どうぞ(半日くらいで読めた) 25パターン中21の問題を経験していたので、早めに読んでおくとかなり役立ちそう （特に前半の論理設計/物理設計） 概要をまとめた記事をよく見るのでざっと眺めてみるのもあり （ネットの記事でほぼ全ての情報が出ている気がする…） RDB、正しく使えばアプリケーション側の負担が減ります