blog.toxn

あしあと

SQLアンチパターンその1 ジェイウォーク(信号無視)

ジェイウォーク(信号無視)とは

(例えば)カンマ区切りで複数の値を持つ列を作ること。

例:アカウントのテーブル。このアカウントは複数のグループに所属できる。

(account_id, name, group_id)
---------------------------
(1, "hoge", "1,2,36,103")
(2, "moga", "2,4")

なぜアンチパターンなのか

区切り文字を気にしてアプリを実装する必要がある

区切り文字をカンマにした場合、区切り文字以外にカンマが出てくる可能性はゼロではない。(上の例だとgroup_idは数値だから大丈夫に見えるけど。)じゃあどんな区切り文字だったら大丈夫なのかは分からない。

列のデータ型が文字列になってしまう

group_idのデータ型がIntegerだとしたら130は入っても、"piyo"なんて値は入るはず無い(エラーする)けど、「カンマ区切りで記された数値」を保存する列(varchar)だった場合、"1,2,4"と、"1,2,piyo"はどちらもデータとしては間違っているが、データ型としては正しい。

列の最大サイズを決められない

いったい何個のgroup_idがカンマ区切りで入ってくるのか予想はできない。どれくらいのサイズを取っておけば安全なのか誰もわからない。

データの修正が複雑

文字列解析してgroup_idを追加・変更・削除するのはとても面倒。

どうするべきか

第一正規化して別テーブル(交差テーブル)を作る。

(1, "hoge", "1,2,36,103")
(2, "moga", "2,4")
↓
(1, "hoge")
(2, "moga")

(1, 1)
(1, 2)
(1, 36)
(1, 103)
(2, 2)
(2, 4)

文字列をsplitする必要もないし、データ型がおかしいデータも入らない。サイズに関しても予想がつく範囲内におさまるし、データの修正や削除もSQL文1行で出来る。

アンチパターンに限らない話だと思いますが、「アプリ側できちんと制御してやれば変なデータは入らない。」というのは「安全装置ついてない刃物むき出しの機械なんだけど、注意すれば事故は起こらない」って言ってるのと同じ。しなくてもいい注意は排除して、安全な設計でいきましょう…ということだと思います。