Jaywalking
Store each value in its own column and row.
-
「信号無視」
- intersection(交差点)を無視することから
- 多対多の関連でintersection tableを作らずにカンマ区切りリストを使ってしまうこと
Objective: Store Multivalue Attributes
- 最初は1対多だったのが、プロジェクトの成長にともない多対多になった場合など
Antipattern: Format Comma-Separated Lists
BIGINT UNSIGNED
だったaccount_id
をVARCHAR(100)
にしてしまう- テーブルや列の追加なく要件を満たすことができ、うまくやったように思える
- が、問題がある
-
特定のアイテムを取得するためにREGEXPが必要
- false matchの危険性
- インデックス使えない
- DB製品による方言
- JOINも同様
-
集約関数使えない
-
COUNT(col)
の代わりにLENGTH(col) - LENGTH(REPLACE(col, ',', '')) + 1
とか必要- cleverだがclearじゃない
- 開発時間がかさみデバッグが大変
- 集約関数によっては実現できない
-
-
要素を削除するためだけに多くのコードが必要
- SELECTステートメント
- アプリケーションコードでカンマ区切りリスト修正
- UPDATEステートメント
-
VARCHARにしてしまったので何でも入ってしまう
- 【補】FK制約も効かない
-
デリミタの選択で頭を悩ます
- 文字列のリストの場合
- 文字列エントリにデリミタが含まれないことを保証できますか?
-
リスト長に制限がある
- 個々のエントリの長さにより格納可能な要素数が変わる
- リスト長はどれだけあれば十分ですか?
- リスト長の上限について上司や顧客に説明できますか?
How to Recognize the Antipattern
-
こんなのが聞こえてきたら注意:
-
「このリストがサポートする最大エントリ数はいくつですか?」
- VARCHARカラムの長さを決めようとして生じた質問かも
-
「SQLで単語境界にマッチする方法は?」
- 別々に格納すべき説
-
「リストのエントリに現れない文字は?」
- どんな文字もいつかは現れると想定すべき
-
Legitimate Uses of the Antipattern
-
パフォーマンスのための非正規化
- アプリケーション側が個々の要素を必要としないとき
- アプリケーションからカンマ区切りのデータを受け取り、格納し、受け取ったそのまま返せば良いとき
-
非正規化するときは慎重に。あくまで基本は正規化
- 柔軟
- データ整合性
Solution: Create an Intersection Table
- 交差テーブル追加しろ
Other Advantages of the Intersection Table
-
インデックス効く
- カンマ区切りリストの正規表現マッチングよりもパフォーマンスよい
- 多くの製品で、FK宣言すると暗黙にインデックス張られる
- 交差テーブルに属性追加できる