SQLアンチパターン

※この記事はこちらのブログ記事をQiitaに移植したものです。

はじめに

やろうと思っていた個人開発がどうにも滞ったまま何ヶ月も経ってしまい、でも久々に記事だけでも更新しなきゃなということで今回は技術書の紹介。
今回紹介するのはSQLアンチパターンという本。業務でDB設計をやる機会が増えて、設計に関して気をつけなきゃいけないことを把握しようと思って買ってみた。
本全体の感想としては、DB設計に関わる人なら一度は読んでおくべき本だなという印象。テーブル定義や設計などで避けるべきことが網羅されている感じ。
紹介されているパターンが25もあるので、今回は特にやりがちなパターンを3つほど紹介してみる。

3章 : IDリクワイアド（とりあえずID）

新しいテーブルを作る時、何でもかんでもとりあえずIDカラムを入れるというのをやりがちな人は結構多いのではないかと（自分含む）。
例えば、学生ごとに受講する科目を定義するstudent_subjectというテーブルを考えてみる（studentテーブルとsubjectテーブルは別途定義されているとする）。

id	student_id	subject_id
1	1	1
2	1	2
3	2	3
4	3	2

また、テーブルを生成する際にUNIQUE KEY (student_id, subject_id)でstudent_idとsubject_idが一意になるようにする。
そうなると、”id”カラムって何のために使うんだという話になってくるので、必要でないカラムをあえて入れておく必要はないのではということになる。
とはいえ、このテーブルに成績を表すgradeというカラムを追加して、さらに別のテーブルと連結させる必要が出てきたりした場合はidカラムに相当するものが必要になるので、ケースバイケースではあるが（もっとも、この場合はテーブル名をもっと別の適切な名前にすべきだが）。
要約すると、「このidカラム、本当に必要ですか？」ということか。

10章: サーティーワンフレーバー（31のフレーバー）

あるカラムに入る文字列の値をいくつかに限定したい場合、MySQLではENUM型で定義することがある。他のRDBMSはあまり使ったこと長いのでわからないが、CHECK制約を入れて入る値を限定するやり方があるらしい。
例えば、従業員が所属する支社が”TOKYO”、”OSAKA”、”NAGOYA”、”SAPPORO”の4つのいずれかという場合、officeカラムをENUM(‘TOKYO’,’OSAKA’,’NAGOYA’,’SAPPORO’)という風に定義していたとする。
employeeテーブル

employee_id	name	office
1	Suzuki	TOKYO
2	Sato	OSAKA
3	Tanaka	NAGOYA
4	Yamamoto	TOKYO
5	Yoshida	SAPPORO

ところがある日、那覇支社が新たに追加された場合ENUM(‘TOKYO’, ‘OSAKA’,’NAGOYA’,’SAPPORO’,’NAHA’)に定義を変更するのは結構面倒臭い。

このような状況を避けるには、以下のようにすればいい。
officeテーブルを新たに定義し、employeeテーブルにoffice_idを持たせる。
employeeテーブル(修正版)

employee_id	name	office_id
1	Suzuki	1
2	Sato	2
3	Tanaka	3
4	Yamamoto	1
5	Yoshida	4

officeテーブル（追加）

office_id	office_name
1	TOKYO
2	OSAKA
3	NAGOYA
4	SAPPORO
5	NAHA

ただし、入る値が”LEFT”、”RIGHT”など絶対に追加されることが無いと断言できる場合はENUM型を使ってもいいと思う。なので、ENUM型での定義を考える場合は新たな値が追加される可能性をまず検討すべきかと。

18章 : インプリシットカラム（暗黙の列）

プログラム内でデータベースから検索する時、

SELECT * from （テーブル名） where （検索条件）

に相当するような検索クエリを呼び出すような形で結果を取得するようなことがあると思う。
あるいは、

INSERT INTO （テーブル名） （カラム） VALUES （値）

でレコードを追加するときにデフォルト値が設定されているカラムを省略したりすることもあるはず。
けど、実はこれらには落とし穴があったりする。例えば、カラム数が10あったとして、取得した値をカラムごとに配列に入れていた場合、誰かが不要なカラムを1つ削除したとすると、配列の要素数が9しかないのに10番目の要素にアクセスしたらエラーになってしまう！
それ以外にも不要なデータまでも持ってきていることでクエリが必要以上に重くなってしまうこともあり得る。
なので、多少面倒でも必要なカラムのみを明示的に指定した方が後々問題が発生するのを避けることができる。

終わりに

25のアンチパターンの中で、自分も気をつけていないとついやりがちなパターンを3つほど上げてみたけど、逆に「これは普通やらないだろ」ってのもちらほらあったなあ。
例えば、1章の「ジェイウォーク（信号無視）」は1つのカラムに無理やり複数の値をカンマ区切りで入れるのは少なくとも自分は気持ち悪く感じるし、20章の「SQLインジェクション」は最近のフレームワークだとほぼ対策がされているのではないかと（それでも、ユーザー向けのシステムの場合はテストの時にフォームにSQL文を入れて変なことにならないかの確認はやるけど）。
それでも、こういう「べからず集」は意識して開発していけば後々のトラブルの元が減らせることになるので、設計していて違和感を感じたら本を見返して問題点と対策を把握しておくのがいいだろうな。

半年近く滞っていた個人開発の方も、どこかのタイミングで再開したいなあ。