こんにちは。急に記事を書くことになってどうしようかと思いましたが、いい機会なので書いてみることにします。
#はじめに
最近業務でシステム構成の設計をすることが増えてきて、その中で特にDBのテーブル設計に苦慮することが多くなっています。
そこで、いろいろテーブル設計のアンチパターンを調べたので、せっかくなのでこれを記事にしてみようかと思いました。
「SQLアンチパターン」という本が出ており、それをご覧になった方も多いかと思いますし、ネットで調べれば内容をまとめているサイトも多くあります。そのアンチパターンを全部抜き出して紹介するとかなりの長さになってしまうので、もう少し場面を絞り、自分なりの解釈で解説してみます。かなり個人的な意見とかも入っていたり、誤解があったりするかもしれませんが、ご了承下さい。
※ちなみに、SQLServer前提です
#列の追加に柔軟に対応したい
アプリ・サーバー間での通信がある場合に、アプリ側からこの値も入れて欲しいとか、サービスのログを取るときに、この項目を追加して欲しいとか、テーブルの列の追加が後々発生することは良くあると思います。もちろんSQLServerであれば、カラム追加はそこまで難しいことではありませんが、頻繁にカラム追加をしなければならない場合は結構面倒ですし、DBを触るので誤操作によるデータ削除などのリスクも高まります。
ここでは、そのような「列の追加に柔軟に対応しようとした結果のアンチパターン」をいくつか紹介します。
#第1アンチパターン「Jaywalking」
例えば、あるサービスで、ユーザーの名前と性別、好きな果物(複数可)を入力してもらって、それをDBに格納するという場面があったとします。
好きな果物の複数入力可というところに対応するため、この部分は柔軟に作っておく必要があります。
そこで、以下のようなテーブル設計を考えました。
| ユーザーID | 名前 | 性別 | 好きな果物 |
|---|---|---|---|
| 1 | A子 | 女性 | りんご、みかん、イチゴ |
| 2 | B男 | 男性 | ぶどう、バナナ |
これであれば、好きな果物がいくつ入ろうと対応できます。
しかし、これはJaywalking(信号無視)というアンチパターンです。
何がダメかというと、例えば後々「好きな果物にみかんを入力している人の人数を集計したい」みたいな要望があったとき、
この場合では以下のようなSQLを実行することになります。
SELECT COUNT(*) FROM ユーザー WHERE 好きな果物 LIKE '%みかん%'
となります。確かにこれで抽出はできますが、問題はLIKEの中間一致なので、インデックスが使われません。
もしこのデータが大量にある場合、抽出までにかなりの時間と負荷を要してしまいます。
また、果物別に集計したいという場合に、どうしよう...となります。
解決策としては、以下のような交差テーブルと呼ばれるものを作ることです。
| ユーザーID | 好きな果物 |
|---|---|
| 1 | りんご |
| 1 | みかん |
| 1 | イチゴ |
| 2 | ぶどう |
| 2 | バナナ |
これであれば、果物別の集計にも楽に対応できますね。
ただ、このアンチパターンでも、必ずしも悪であるわけではありません。
このサービスの入力画面で、果物をカンマ区切りで書いてください、という風になっている場合は、むしろ自然かもしれませんし、後々DBのカンマ区切りをそのまま使う場面があれば、この選択も悪くありません。あとで集計したいという要望がなければの話ですが...
#第2アンチパターン「Multicolumn Attributes」
上記と同じく、ユーザー情報の入力画面で、名前、性別、果物2つを入れるという場面があるとします。
今回は2つという制約があります。「2つだからこんな設計にしよう」という風に考えて、以下のように設計しました。
| ユーザーID | 名前 | 性別 | 果物1 | 果物2 |
|---|---|---|---|---|
| 1 | A子 | 女性 | りんご | みかん |
| 2 | B男 | 男性 | ぶどう | バナナ |
今はこれでいいかもしれませんが、後々もう1個ということになりかねません。これがMulticolumn Attributesです。
これではもう1つ果物を追加したいという場合に、テーブルに変更が加わりますし、「やっぱり無制限にしよう」とかになったらアウトです。
このパターンでも、第1アンチパターンと同様の解決策が適用できそうですね。
また、本当に果物が2つだけで、将来的に変わらないのであれば、このパターンを適用してもよいですが、変わらないという根拠なんてないのが普通ですね。
#第3アンチパターン「Entity-Attribute-Value」
同じような例で、ユーザーの名前と性別、好きな果物(複数可)の他に、好きな色(複数可)を加えるとします。そして、企画者から今後こういった項目が増える可能性が高いという話をもらいました。
この場合、項目とその内容を柔軟に追加できるようにしたいものです。そう考えて、以下のような設計にしてみました。
ユーザー テーブル
| ユーザーID | 名前 | 性別 |
|---|---|---|
| 1 | A子 | 女性 |
| 2 | B男 | 男性 |
ユーザー 詳細テーブル
| ユーザーID | 属性 | 値 |
|---|---|---|
| 1 | 果物 | りんご |
| 1 | 果物 | バナナ |
| 1 | 色 | 赤 |
| 1 | 色 | 黄色 |
これならいくら項目が増えようが、いくら値の数が増えようが、柔軟に対応できますね。
もうバッチリじゃん！という感じですが、意外とそうでもないようですね。これが「Entity-Attribute-Value」というアンチパターンです。
例えば、果物にりんごを選んだ女性の数を取りたいという場合、以下のようなSQLになります。
SELECT COUNT(ユーザー.ユーザーID) FROM ユーザー
INNER JOIN ユーザー詳細 ON ユーザー.ユーザーID = ユーザー詳細.ユーザーID
WHERE 性別 = 男性 AND 属性 = '果物' AND 値 = 'りんご'
確かにこれで取得ができますが、なんか面倒なSQLですね。
このサンプルが単純なので、そうでもないように見えますが、これがさらに属性が増え、さらに複数項目で集計をしたいとなった場合、かなり複雑になってしまいます。
他にも、値のデータ型が使えない(ほぼvarcharになりそう)、NOT NULLなどの制約がかけられないといったデメリットもあります。
解決策ですが、オブジェクト指向の考え方をテーブルに持ってくる、テーブル継承という考え方があります。以下の3つのようなテーブル構成にします。
①シングルテーブル継承(全部を1テーブルに乗せる)
ユーザー テーブル
| ユーザーID | 名前 | 性別 | タイプ | 果物 | 色 |
|---|---|---|---|---|---|
| 1 | A子 | 女性 | 果物 | りんご | NULL |
| 1 | A子 | 女性 | 色 | NULL | 赤 |
②具象テーブル継承(複数項目別にテーブルを分ける)
ユーザー テーブル
| ユーザーID | 名前 | 性別 |
|---|---|---|
| 1 | A子 | 女性 |
| 2 | B男 | 男性 |
ユーザー果物 テーブル
| ユーザーID | 果物 |
|---|---|
| 1 | りんご |
| 1 | みかん |
| 2 | ぶどう |
| 2 | バナナ |
ユーザー色 テーブル
| ユーザーID | 色 |
|---|---|
| 1 | 赤 |
| 1 | 黄 |
| 2 | 青 |
③クラステーブル継承(複数項目のもののみのテーブルを作る)
ユーザー テーブル
| ユーザーID | 名前 | 性別 |
|---|---|---|
| 1 | A子 | 女性 |
| 2 | B男 | 男性 |
ユーザー詳細 テーブル
| ユーザーID | 果物 | 色 |
|---|---|---|
| 1 | りんご | 赤 |
| 1 | みかん | 黄 |
| 2 | ぶどう | 青 |
| 2 | バナナ | NULL |
今回は②がしっくり来そうですが、データの数や種類・企画からの要求などによっては、他のパターンを使った方がいい場合もあります。結局は場面場面での使い分けということですね。
まとめ
様々なアンチパターンを見てきましたが、絶対にやってはいけないということではありません。採用するリスクを理解し、それでも採用する価値があるという場合にやってもいい、くらいの解釈でいいかと思います。
私自身はSQLアンチパターンの書籍を読んだことはなく、ネットで調べた情報を元に解釈をしてみましたが、こんな設計の考え方もあるんだ、と結構参考になりました。余裕があれば書籍も読んでみたいと思います。
初めてこういう記事を書いたということもあり、かなり拙い文章になってしまったかと思いますが、こんな記事が誰かの役に立てたのならば幸いです。
ここまで読んで頂き、ありがとうございました。