サーティワンフレーバーパターン

アンニュイな日曜なので読書しましょうという気持ちになったので、Kindleで買ったSQLアンチパターンを読み進めていた。サーティワンフレーバーパターンはその中に書かれているSQLアンチパターンのひとつ。

サーティワンアイスクリームは31種類という不変のアイスクリームフレーバーのリストを組み替えることができるような、可変のセットに拡張したから、お前たちの設計も組み換え可能なようにした方が良いというのが名前の由来みたい。アンチパターンの名前というよりも、こういうことあるよね?というパターンに対して付けた名前にも見える。

テーブルに"Mr."や"Mrs."のような敬称を入れるカラム salutation があるとする。name が"ホワイト"なら、組み合わせて"Ms. ホワイト"と作れる。このカラムには決まった種類の値しか入って欲しくない。salutation に"キュア"みたいな敬称でもない変な値を入れて、"キュアホワイト"とか作りたくない。なので、どの値が入るべきという制限を設ける必要が出てくる。

制限の方法はいくつかあって、アンチパターンとして紹介されていたのがCREATE TABLE時にCHECK制約を入れたりENUM型(SQL非標準)を使うこと。どんな時にこれが問題になるのかというと、SELECT DISTINCT salutation FROM PersonalContacts とかで salutation に入っても良いものの一覧を作成することができない。テーブルに "Ms. ホワイト" 1人だけの情報しかないのなら、敬称の一覧として "Ms." しか取れなくなる。

また、新しい敬称が必要になった場合にはCHECK制約やENUMの値の変更といった、カラム定義の変更が必要になる。このようなメタデータの変更は適切なテストが必要だったりコストのかかるもの。個人的にはカジュアルに変更できたらいいなと思うけど、本の中でも「あるべき論」として、メタデータの変更は頻繁に行うものではないと書かれている。

PersonalContacts テーブルから敬称の一覧がとれないのならば Salutations というテーブルを作って、取り得る敬称の一覧を定義しておきましょうという話になる。Salutations テーブルを作ることになるのが予見できるのならば、PersonalContacts.salutation には Salutations.salutation への外部キーを指定しておく。そうすれば、新しい敬称を追加する必要が出てきても、INSERTだけで済む。外部キー制約にON UPDATE CASCADEも指定しておけば、Salutations.salutation を変更した場合は PersonalContacts.salutation の値も追随して変更されるので嬉しい。

サーティワンフレーバーパターンは「データベース物理設計のアンチパターン」の章に書いてあるので、アプリケーションについてはあんまり触れられてないのだけど、INSERTしようとする値が正しいものであるかアプリケーション側でも制限することはできる。テーブルの設計書!に書かれているコメントに基いてちゃんと制限を書く人もいるけど、コメントを見逃しちゃって、制限を書かないで"キュア"が入ってしまうこともある。ちょっとしたテーブル設計で制限できるのなら、アプリケーション側よりもDB側の1つしかない入り口でやった方が、変なデータが入らなくて安心だと思う。

キュアホワイト言いたかった。