どうも、シローです。
ちょっとDB設計をすることもあるんで、SQLのアンチパターンなど勉強中です。
今回は、カンマ区切りのカラムを設定しちゃったらダメという内容です。
SQLにおけるアンチパターンというのは「ある問題を解決しても、別の問題を引き起こす」ということらしくて、
どうしてダメなのか、代わりにどうしたら良いのかをみていきましょう。
カンマ区切りのカラムが出来上がるまで
例えば、製品テーブル:Productsとタグテーブル:Tagsがあって、
一つの製品は一つのタグを持つとすればテーブル間のERはこのようになるんですが
仕様変更とやらが起きて、一つの製品が複数のタグを持つようになった場合に、
tag_idを数値型から、tagsのidをカンマ区切りで連結して格納するために文字列型にしました。
その場の問題回避としてこのような解決策を選ぶと後々めんどくさい問題を引き起こします。
何がダメなのか
要素の更新や削除がめんどくさくなる
例えば、
id | 1 |
tag_id | 11,14,45 |
product_name | SQLアンチパターン |
というレコードのtag_idの14を12に変更したい場合、
- まずこのレコードを
SELECT
文を発行して取得して - tag_idを分割して配列にして
- 14を12に差し替えて
- 再びカンマ区切りの文字列に変換して
UPDATE
文を発行してレコードを更新
となるでしょう。
うーん、たかが一つのタグを更新するだけなのに処理が多い
そして、その処理のソースコードで何かしらのバグが起きたらどうするのか・・
とりあえず、イケテナイ。
文字列型の最大文字列数によって格納できる要素数にばらつきが生じる
tag_idの最大文字列長が15だとすると
1,2,3,4,5,6,7,8
では8個の要素を格納できるけど
11111,22222,111
では3個の要素までしか格納できないです。(しかも3つ目の要素は3ケタ以内という制限もある)
例えばtag_idの長さが1000ぐらいあるとすれば最初の方は気にしなくても良いと思いますが、
数年経ってある日突然、タグの追加ができなくなった・・ということが起こりえます。
tag_idで検索するときに文字列検索になってしまう
tag_idが45を含むレコードを取得したい場合は、数値型ならSELECT * FROM products WHERE tag_id = 45
というクエリでいいのですが。
文字列型にしてしまうと、これが通用しなくなってしまいます。
正規表現を用いれば可能らしいのですが、他の人がその処理を見た時に正しく把握できるでしょうか・・
そのため、検索として用いる際の利便性が著しく低下してしまいます。
数字以外を間違って入力される可能性もある
tag_idが数値をカンマ区切りで入力されるという仕様があったとして、
- その仕様をどこで管理しているか(ちゃんと更新しているか
- 数値以外がもし要素として含まれた場合にどうやってそれを検知するか、検知した場合の処理をどうするか
などなど、考えなけらばならないことがありますし
そもそも「仕様として満たしているから現状はこれで良い」的な考えは個人的には嫌いだったりします。
後からプロジェクトに参画するエンジニアにとっては時限爆弾みたいなことになりかねないです。
解決策:交差テーブルを作成する
以下のようなProductsのidとTagsのidのペアを管理するProducts_Tagsと言うテーブルを考えてみます。
このように、二つのテーブル(ProductsとTags)の参照する外部キーを持つテーブルを交差テーブルといいます。
ある製品のタグを追加、更新する
例えばProductsのidが1に対してTagsのidの3を追加したい場合は
INSERT products_tags(product_id, tag_id) VALUES (1, 3)
とすれば良く、
Tagのidの3を5に更新したい場合は
UPDATE products_tags SET tag_id = 5 WHERE product_id = 1 AND tag_id = 3
とすれば良いです。
手続き型言語でカンマ区切りの文字列をチマチマいじらなくても良いです。
レコードの検索
例えばTagsのidが3を含む製品リストを取得したい場合は、
SELECT * FROM products_tags LEFT_JOIN products ON products.id = products_tags.product_id WHERE products_tags.tag_id = 3
とすれば良いです。
正規表現とか必要ないです。
まとめ
- 一つのレコードに複数の要素をカンマ区切りで格納するのはアカン
- 解決策として2つのテーブル間への外部キーを持つ交差テーブルを作成する
本書はDB設計やSQL記述の際に避けるべき事柄を1章で1つ、25個紹介する書籍です。
リレーショナルデータベースを中心に据えたシステム開発には、様々な場面で陥りやすい失敗(アンチパターン)があります。
本書はデータベース論理設計、データベース物理設計、クエリの記述、アプリケーション開発という4つのカテゴリに分かれて、それぞれの分野におけるアンチパターンを紹介し、失敗を避けるためのより良い方法を紹介します。
複数の値を持つ属性や再帰的なツリー構造の格納から、小数値の丸めやNULLの扱いに起因する問題、全文検索やSQLインジェクション、MVCアーキテクチャなど、実践的かつ幅広いトピックを網羅します。
データベースに関わるすべてのエンジニア必携の一冊です。