@akeno_0810 2022.05.28 SQLアンチパターン から学ぶテーブル設計 ココカラ勉強会 No.9

自己紹介 About me akeno (@akeno_0810) Webエンジニア歴2年くらい Rust, API/コード設計, DevOps/開発の効率化 触っている技術 最近興味のある分野

SQLアンチパターン

SQLアンチパターン リレーショナルデータベースを使う際のアンチパターンP B 論理設# B 物理設# B クエ B アプリケーション の4種類、25パターン 実際に出会ったパターンをピックアップして紹介 （スマホでスキャンしたので画像が雑です）

論理設計 Entity Attribute Value ポリモーフィック

Entity Attribute Value 可変属性をサポートする方法 KVSのようにRDSでも柔軟なデータ構造を持ちたい… † カラム名を用いた検索が出来な“ † 入っている値はアプリケーションが保 証する必要があx † 構造をプログラマが管理しなければな らない

Entity Attribute Value 基本的には向いてないので、ある程度の種類に絞れる場合を考える シングルテーブル継’ e ひたすらカラムを増やb e NULLが入るカラムが多くなる 具象テーブル継’ e テーブルを完全に分けV e 共通データとして扱いづらい クラステーブル継’ e 共通部と個別部のテーブルを分けV e 共通部の切り出しが難しい・参照が面倒

ポリモーフィック シングルテーブル バリエーションのあるテーブルに関連を持たせたい場合 クラステーブル 具象テーブル(Bad) 具象テーブル(Good) Commentsテーブルに どちらのテーブルを参 照するか（メタデー タ）を持たせるという パターンが辛い

Entity Attribute Value / ポリモーフィック 経験談と感想 KVSの形はマスターデータを保存する場合はありだと思う EAVはなんだかんだ見ることが多いが、コメントなしでは誰も読めないことも多い →できる限り避けていきたい気持ち 商品と特定の属性が追加された商品のパターンは見たことがある メタデータがデータに混入する問題・柔軟性は怖い ORMを使ったら実装していいって書いてるけど、それでも辛かった 結局用途に寄るのだが、シングルテーブルorクラステーブルを使いたくなる

物理設計 31のフレーバー

31のフレーバー 限られた値のみを格納するカラムを作成する方法 ステータスや性別等、限られた値を保持する際にENUMを使ってしまうとV P 既存の値リストがわからなE P 追加がしづらE P 移植しづらい といった問題が発生する。 解決策としてはマスターテーブルからデータ参照して外部キーで縛ること。

31のフレーバー 経験談と感想 出来るだけマスターテーブルを用いていきたい。 入る値が一覧できるのと、値を縛れるのが有用。 この辺をアプリケーション側で担保するのは面倒。 tinyint型で管理する手もあるが、コメントが無くて分からなくなるパターンがある。 柔軟性を求めるならありだが、柔軟性とアンチパターンは紙一重。 けどマスターテーブル管理が面倒で数値や文字列に逃げることはまあまあある… ドキュメントやコメントでしっかり補完しておきましょう。

クエリ フィア・オブ・ジ・アンノウン

フィア・オブ・ジ・アンノウン NULLを使わない方法 unknownな値が怖い NULLとの比較はかなり特殊。 あまり使いたくないし、使う際は NULLと比較される可能性を考慮 したクエリを組む必要がある。 UNIQUE制約に縛られない点から も値としては見てない。

フィア・オブ・ジ・アンノウン 経験談と感想 出来るだけデフォルト値を用いていきたい。 NULLは何もないという特別な状態を示すために使う。 カラムを増やす際にNULL許可デフォルト値なしのマイグレーションがあった。 アプリケーションは動かなくなった。 column_nane != ‘検索文字列’ というクエリをnull許可のカラムに掛けていた。 期待より少なめの検索結果となった。

アプリケーション開発 リーダブルパスワード

リーダブルパスワード パスワードを安全に保存する方法 パスワードを平文で保存していたので閲覧が簡単に出来てしまった… 平文はなかなか無いと思うが、それ以外でW r SQLに平文のパスワードを入れてSQLでHash化していI r Saltがな„ r Hash化回数が不十C r 復号の必要がないが、復合可能 あたりは踏みやすい。 DBとは関係ない部分の注意点が多い。

リーダブルパスワード 経験談と感想 復合可能な情報を持つのは怖い。 Hash化回数が少ない場合、Saltが使われていない場合も怖い。 復合可能にするという要件があったので、実装したことはある。 keyとiv(初期化ベクトル)の扱い、大事。 パスワードのHash化は大抵の言語に専用のライブラリがあると思うので使おう。 Goだと`bcrypt.GenerateFromPassword()`がお手軽。 普通のHash関数を使って自前で実装するメリットは薄いと思う。

まとめ

Thank you! 参考資料 SQLアンチパターン https://www.oreilly.co.jp/books/9784873115894/ 読みやすい本だったので是非どうぞ(半日くらいで読めた) 25パターン中21の問題を経験していたので、早めに読んでおくとかなり役立ちそう （特に前半の論理設計/物理設計） 概要をまとめた記事をよく見るのでざっと眺めてみるのもあり （ネットの記事でほぼ全ての情報が出ている気がする…） RDB、正しく使えばアプリケーション側の負担が減ります