前置き
以前から読もう読もうとずっと思っていたSQLアンチパターンが想像以上に名著だったので、少しでもこの書籍の素晴らしさをお伝えすることができれば幸いです。
「SQLアンチパターン」とは
「SQLアンチパターン」は、デーベースの論理設計や物理設計などで陥りやすい失敗=アンチパターンを紹介し、失敗を避けるためのより良い方法を紹介している書籍です。
全体として
- データベース論理設計のアンチパターン
- データベース物理設計のアンチパターン
- クエリのアンチパターン
- アプリケーション開発のアンチパターン
以上４つのカテゴリーに分割され、一章ごとに１つのアンチパターンが紹介されていく形式になっています。
紹介されているアンチパターンを見てみると、
「うわっ、このパターンやっちゃってた・・・（OR 見たことある・・・）」
「いや、直近の案件がまさにこのアンチパターンの設計だったんだけど・・・」
という心の声が思わず何度も漏れてきてしまいました。
本記事では特に自分が勉強になった部分をピックアップして感想を書いていきたいと思います。
データベースを単なる「データの容れ物」として見ていないことがアンチパターンでは
「SQLアンチパターン」では以下のようなデータベース論理設計のアンチパターンが紹介されています。
「１章 ジェイウォーク（信号無視）」より。目的としては、１対多あるいは多対多の関連を作ろうとするため、複数の値を持つ属性を１つの列に格納してしまうパターンです。
| bag_id | tag_ids |
|---|---|
| 1 | 3,5,11 |
続いて「７章 マルチカラムアトリビュート（複数列属性）」より。果たしたい目的はジェイウォークと同様です。
| bag_id | tag_1 | tag_2 | tag_3 |
|---|---|---|---|
| 1 | 3 | 5 | 11 |
私はどちらのパターンの設計も見たことはありますが、さらに上記2つを組み合わせたパターンも見たことがあります。
| hoge_id | items_1 | item_2 | item_3 |
|---|---|---|---|
| 1 | 3,5,11 | 2,4,6 | 7,8,9 |
これらのアンチパターンを用いると、更新、検索、更新、妥当性検証すべてが困難になってしていまいます。
ジェイウォークと言えるかわかりませんが、私は以下のような設計も見たことあります。
| year_month | value_of_day |
|---|---|
| 202010 | 000100000000000000000000000100 |
年月(1)に対して月内の1日毎のある設定値(多）の関連をもたせようとした結果だと思うのですが。
value_of_dayが31バイトの数値になっていて、
例えば5日にデータが有る、だったらvalue_of_dayの5バイト目を1にする、といった方式です。
value_of_dayから日付の情報に変換するのはすべてアプリケーション側での役目なので、結構大変です・・・
※これは有りですかね？意見聞きたいです・・・
「ジェイウォーク」「マルチカラムアトリビュート」、いずれも従属テーブルを作成し、BagとTagに関連（リレーション）を持たせる事が解決策です。
RDBやSQLの基本知識があれば、当たり前じゃん！と言える解決策だと思うのですが。
いずれのアンチパターンもSQLやリレーションの機能を全く活かす事ができず、データベースを単なる「データの容れ物」として見ていないような設計に思えてしまいます。
本書の和田省二さんの前書きで以下のような事が書かれています。
RDB理論が集合論を基にしていること、集合を扱うためのSQL言語であることが正確に知られていないのが現状です。また「SQLを知らない（書いて動かしたことがない）RDB設計者」、「SQL言語が集合をデータとした宣言型言語であるのに手続型言語のように利用しようとするプログラマー」も後を絶ちません。これは憂慮すべき状況と言わざるを得ません。
まさにおっしゃられている憂慮すべき状況から、アンチパターンが生み出されてきてしまうのかもしれません。
主キーにはわかりやすい名前をつけよう
「3章 IDリクワイアド（とりあえずID）」より。例えば以下の例。
| id(主キー） | bag_id | tag_id |
|---|---|---|
| 22 | 327 | 1234 |
| 23 | 327 | 1234 |
bag_id と tag_id の複合キーを主キーと出来るのに、別にidという主キーを定義したため、重複
が許されてしまっています。
例えば氏名を登録するテーブルの場合、同姓同名のパターンがあるので氏名は主キーに出来ません。
その場合は「id」のような行を一意にするための疑似キー（pseudo key）や代理キー（surrogate key)が必要になります。
疑似キー（pseudo key）や代理キー（surrogate key)は便利ですが、前述のような複合キーを主キーと出来るようなテーブルには不要です。
不要なテーブルにもよく考えずid（主キー）列を設置しようとするのが「アンチパターン：すべてのテーブルに「id」列を用いる」です。
私がこの章で特に学びになったなと思ったのが
3.5.1 わかりやすい列名にしよう
の箇所でした。例えばBugsテーブルの主キーであれば「id」ではなく「bug_id」と命名する。
ちょうど直近の案件でテーブル設計でメンバーと検討していたとき、
「テーブルの主キーはidで良くて、外部キーとして他のテーブルに設置する場合は〇〇_idにすれば良いのでは」
といった話をしていたのですが、本書籍を読んで主キーの命名は基本「テーブル名+id」で良いと確信が得られました。
関連テーブル内で主キー、外部キーの命名を統一することでJOIN構文内でUSINGを使用し、ON構文を使わないで済むというメリットも得られます。
『本システムのDB設計規約では「外部キー成約」の使用は禁止とします』
皆さんが保守されているシステムの設計規約に、こんな一文書かれていたりしませんでしょうか？
確かに本書籍内でも述べられていますが、複数のテーブルの関連し合う列を更新する際に成約が邪魔になると感じることもあるのでしょう。
ですが、本書籍では「外部キー制約を使用しない」はアンチパターンとされています。
私も過去に保守していた外部キー制約の無いテーブルを利用したシステムで、
バグにより親テーブルが存在しなくなってしまった子テーブルのレコードが、また別のバグの発生原因になったりした経験をしてきました。
（親無しになった子レコードが新しく出来た親レコードに紐付いてしまったり・・・・）
自分としても、データベース内の整合性を第一に考えるのであれば、外部キー制約を絶対に使用すべきだと思います。
「お客様、新しいカスタマイズ項目をどれぐらいの頻度で追加される可能性がありますか」「結構あると思いますよ〜」（年１回ぐらいしかなった）
第5章で紹介されるアンチパターンは「EAV（エンティティ・アトリビュート・バリュー）」です。
本章では以下のような可変属性をサポートするテーブル設計例が紹介されています。
CREATE TABLE Issues (
issue_id SERIAL PRIMARY KEY
);
CREATE TABLE IssueAttributes
issue_id BIGINT UNSIGENED NOT NULL,
attr_name VARCHAR(100) NOT NULL,
attr_value VARCHAR(100),
PRIMARY KEY (issue_id, attr_name),
FOREIGN KEY (issue_id) REFERENCES Isssues(issue_id)
)
| issue_id |
|---|
| 1 |
| issue_id | attr_name | attr_value |
|:-:|:-:|:-:|:-:|:-:|:-:|
| 1 | Product | 1
| 1 | date_reported | 2009-06-01
って、これって前の案件で自分が保守してたシステムの設計ほぼまんまじゃん！と思わず吹き出ししてまいました。
本章で挙げられているのは、バグデータベースでBugとFeatureRequest（機能要望）をIssue（問題）という基底型から関連付けさせ、BugにもFeatureRequestとしてそれぞれ独自の項目を追加させたいケースですが、私が担当したシステムでは1種類のレポートデータにEAVを採用させていました。
本章にもこのパターンのデメリットが幾つか記載されていますが、
・必須属性が設定できない
・参照整合性成約を強制できない
とSQLの恩恵をほとんど受けられなくなってしまうのがこのアンチパターンのおそろしいところだと思います。
解決策としては「サブタイプのモデリングを行う」で、いくつか具体例が紹介されています。
- シングルテーブル継承・・・関連する全てのサブタイプを１つのテーブルに格納する
- 具象テーブル継承・・・・・サブタイプごとにテーブルを作成する
- クラステーブル継承・・・・サブタイプごとにテーブルを作成するのと、それらの基底型テーブルを作成する
- 半構造化データ・・・・・・BLOB列にXMLやJSONで項目と値を保存する
独自項目をカスタマイズできるようにしたい、という要求・要望はよくあるような気がします。
データに新しい項目を追加したくなったら、IssueAttributesにデータを定義するだけですよ！というのも、（その時は）魅力的なメリットに思えてしまいます。
本書ではEAVに期待されるメリットとして以下のような点が挙げられています。
- 両方のテーブルの列数を減らせます。
- 新たな属性をサポートするために、列数を増やす必要がありません。
- 属性が存在しないエンティティの該当列にNULLが入っている、NULLだらけのテーブルになることを防げます。
もちろんユースケースによると思いますが、こと業務システムにおいて新しい項目が追加されるケースってそんなに頻繁に発生することなのでしょうか?
このアンチパターンに陥るのに防ぐ方法はまず第一にユーザーに対して、「独自項目のカスタマイズって本当に必要ですか？」「項目の追加ってどのぐらいの頻度で発生することを想定されていますか？」とか要件定義時に慎重に確認することが大事なのではと思いました。
（そんな思いを本項のタイトルにしました）
ちなみに本章の最後にはこんな事も書かれています
残念ながら、どうしてもEAVを使わざるを得ない状況があります。
プロジェクトを引き継いだ場合や、EAVを使用するサードパーティ製のソフトウェアプラットフォームが会社を採用している場合などです。
ははは。
SQL処理でも例外を握りつぶすな！
「22章 シー・ノー・エビル（臭いものに蓋）」より。本章ではエレガントにSQLの処理コードを書こうとした結果、データベースAPIの戻り値や、コネクションのインスタンス化時の失敗を無視してしまうコードを書いてしまい、「肝心な部分を見逃す」事になってしまうのをアンチパターンとしています。
SQL処理以外でも言えることがですが所謂「例外を握りつぶすな（try-catchして何もしない）」ですね。
登録/保存処理の成功時の戻り値とかも大事ですね。
例えば.NET FrameworkのSqlCommand.ExecuteNonQueryは戻り値が影響を受けた行数なので、例えばUPDATE文の場合は更新したレコード数が1つだったら「1」が返ってきます。
当たり前ですがExecuteNonQueryで例外が起きなければ、戻り値の検証をしなくても登録/保存は成功します。
ExecuteNonQueryの結果が0件でもUIに「データは正常に更新されました」なんてメッセージを返すコードを書いてしまうことが無いようにしたいですね。
DDL/DMLはソースコードのリポジトリに含まれていますか？
「23章 ディプロマティック・イミュニティ（外交特権）」より。紹介されているアンチパターンは「SQLを特別扱いする」です。
中規模〜大規模の案件とかで、開発チームと運用（いわゆるインフラ/DBAチーム）が分かれていたりすると、「DBはDBAチームの管轄なんで・・・スキーマ変更は申請書書いて許可をもらわないと・・・」なんてあったりしませんか。
アプリケーション開発のルールはデータベースに当てはらまない
・・・
- SubversionやGitなどのツールを用いて、ソースコードのバージョン管理を行う。
- ユニットテストや機能テストを自動化し、実行する
- ドキュメント、仕様書、コードコメントを書き、アプリケーションの要件や実相戦略を記録する。
といったベストプラクティスがアプリケーション開発では常識的ですが、データベースコードにはこれらを当てはらまないことをアンチパターンとしています。
確かに、ソースコードはリポジトリで管理されているのにDDLはそもそも管理されていない（そもそもローカル環境にテスト用のDBを作る風習がない現場だった。テストサーバのDBを皆で共有して使う。）現場もありました。
あるいは、DDL/DMLはすべてDBAチーム管理でアプリケーションコードとは別のリポジトリに保管され、アプリケーション開発者はそのリポジトリにはコミットできない、など。
データベース側コードのファイルが、そのデータベースを使うアプリケーションコードと対応づいていることを確認しましょう
と本章では書かれていますが、リポジトリをチェックアウトしたら中のDDLを使ってテーブル定義を構築し、直ぐに実行できるような環境に持っていけるのが望ましい状態ですね。
データベースの構造と振る舞いをアイソレーション（独立）テストする
「23章 ディプロマティック・イミュニティ（外交特権）」から続けてですが、以下のようなデータベースの構造と振る舞いをテストする事が推奨されています。
- テーブル、列、ビューの存在
- 制約
- トリガー
- ストアドプロシージャ
- ブートストラップデータ
- クエリ
- ORMを使用したクラス
DBUnitなどでCRUD関連のユニットテストを行ったことはありますが、「テーブル、列、ビューの存在」テストするのは目からウロコでした。アプリケーションコードよりもかえってテストも書きやすいですね。
リリース時に新バージョン用のDDL流し忘れて、スキーマが無くてエラー出して失敗とかあったなあ・・・
MVCの「M」はモデルだよね。ではモデルって何？
「24章 マジックビーンズ（魔法の豆）」より。
モデル・ビュー・コントローラ（MVC）アーキテクチャは、アプリケーションを３つの関心事に分ける技法です。
※MVCの説明については割愛させていただきます
コントローラとビューの役割は明白ですが、モデルの役割は曖昧です。
例えば Entity Framework のようなオブジェクトリレーショナルマッパー (O/RM) を採用すれば、テーブルの列の定義とモデルオブジェクトの対応付け、さらにテーブル操作に関する基本的なCRUDメソッドまで用意してくれます。
このようなマッピングをサポートするデザインパターンを「アクティブレコード」と呼ばれます。
「アクティブレコード」は非常に優れたパターンですが、モデルをデータベーススキーマに強く依存させてしまいます。
問題はアクティブレコードにより、モデルが単にデータベースとのI/Fの役割しか持たなくなることです。
結果、モデルの外部でビジネスロジックのコーディングが必要になり、複数のコントローラに似たような処理を行うロジックが散りばめられてしまう可能性が高くなってきてしまいます。
（このようにモデルの凝集度が低下する現象をドメインモデル貧血症(Anemic Domain Model)と呼ばれます。
コントローラとアクティブレコードがつるのように絡まってしまっている状態が「マジックビーンズ（魔法の豆）」アンチパターンです。
「マジックビーンズ（魔法の豆）」アンチパターンの具体的な解決策として、「ドメインモデルの使用」が紹介されています。
ドメインモデルはエリック・エヴァンスのドメイン駆動設計で提唱されている技法です。
モデルを単なるテーブルとマッピングしたオブジェクトとして捉えるのではなく、アプリケーションショーンのビジネスルールとそのためのデータ、アプリケーションのビジネスロジックを実装する場所として捉えるようにします。
ドメインモデルの採用により、ドメインモデルがアクティブレコードの操作を行うようにし（本章では「解決策：モデルがアクティブレコードを「持つ」ようにする」と表現しています）、コントローラからはアクティブレコードを直接参照せず、ドメインモデルを介してデータベースの操作を行うような形になります。
名著：「現場で役立つシステム設計の原則」で言うところの三層+ドメインモデルの構成にすることでアンチパターンを防げるということですね。
おわりに
良き始める前は基本的にデータベース設計やクエリ関連中心に述べられているのだと思いこんでいたので、
自分としては「24章 マジックビーンズ（魔法の豆）」にてドメイン駆動設計の話まで登場してきたのは少々驚きました。
本書の冒頭「監訳者まえがき」内で、
データの寿命はアプリケーションの寿命やシステムの寿命よりも長いものです。
とあります。
確かに。そして本書の各アンチパターンな設計を見ていくとよくわかりますが、アンチパターンな設計が施されたデータベースはそれを利用するアプリケーションのコードも複雑になり、ソリューション全体や保守を行う開発者も疲弊させていくと思います。
本書を読んで、改めてデータベース設計の重要さを学ぶことが出来ました。
Appendix1：幻の第26章
実は「SQLアンチパターン」には監訳者の和田卓人さんによる幻の第26章が存在していたようです。
こちらもものすごく興味深い内容ので、ぜひ。
Appendix2：自分が気になるパターン
あなたが保守しているシステムに、とりあえず全部のテーブルに以下の列を追加する規約があったりしませんか？
- created_at（レコードの作成日時）
- updated_at（レコードの最終更新日時）
- created_user（レコードの作成者）※アカウントのユーザーIDか氏名が入る
- updated_user（レコードの最終更新者）
アンチパターンとは言えないとは思いますが、自分の中では「それ絶対本当に必要ですか？」パターンの1つ。
Appendix3：参考リンク
自分は一部のトピックしか取り上げられませんでしたが、以下のブログでは書籍全体を詳しくまとめてくださっています！
「SQLアンチパターン」を避けるためのチェックリスト①（DB論理設計編）
「SQLアンチパターン」を避けるためのチェックリスト②（DB物理設計編）
「SQLアンチパターン」を避けるためのチェックリスト③（SQLクエリ設計編）
「SQLアンチパターン」を避けるためのチェックリスト④（アプリケーション設計編）