概要
名著SQLアンチパターンを読み終えたので、それの復習のために悍ましいデータベースを作ろうと思った。
まず前半では、SQLアンチパターンを意図的に盛り込み、目も当てられない酷い設計をします。
そのあとリファクタリングを行なったER図に書き直していきます。
なお、真面目に書くと参考書の丸写しになってしまうので、この記事は アンチパターンもりもりのER図を見て嫌悪感を学習し、設計に役立てようという趣向のもと、詳しい説明は省きます。
とても良い本なので読んでください。
想定するシステムの概要と状況
目的において適切かはわかりませんが、とりあえず考えることの多い”お金”を扱うシステムを想定してみます。
私はブラックジョークが好きなので、今回は「ちょっと怖い金融屋さんが使う債務者管理システム」のER図を設計してみようと思います。
###ざっくりした要件
- 債務者を登録でき、プロフィールを入力できる。
- 債務者の家族構成を登録でき、そのプロフィールを入力できる。
- 連帯保証人を登録でき、そのプロフィールを入力できる。
- 取り立て人が債務者に借金の回収に行って、それを記録できる
- 債務者の借金を管理できる。
- 債務者の返済状況を管理できる。
- 債務者、家族、連帯保証人をブラックリストで管理できる。
並べてみると頭の痛いシステムとなりますね。
ER図（まともじゃない）
とりあえず作ってみました。こちらがアンチパターンを沢山盛り込んだER図となります。
以下からアンチパターンを一つ一つ見ていきましょう。
###ジェイウォーク（信号無視）
要件No2の 債務者の家族構成を登録でき、そのプロフィールを入力できる。を実現するために使われたアンチパターンです。
debtorsとfamiliesには1:多のリレーションがあります。debtors.familiesには下記のようなカンマ区切りのデータが入っているのです。
1,3,14,55
こんなことをしてしまうと家族を追加する度にdebtorsのレコードを更新する必要がありますし、JOINもままなりません。
familiesテーブルのカラムに、debtor_idの外部キーを設けた方が良いでしょう。
###EAV(Entity Attribute Value)
要件No1の 債務者を登録でき、プロフィールを入力できるを実現するために使われたアンチパターンです。
柔軟なプロフィール登録を可能にしようとした結果生まれてしまったアンチパターンとなります。
profilesテーブルをみてください。
様々な型の項目に対応するため、keyとvalueのフィールドを持っています。
このように実装してしまうと、NOT NULLのような必須属性を設定できませんし、VARCHARで定義してしまっているためSQLのデータ型を使うことができません。
MySQL5.7以上だとJSON型というjsonデータを挿入できる型が用意されています。こちらを使った方が良いでしょう。
（後述のまともなER図ではjson型を使わないアプローチで改良したいと思います）
###ナイーブツリー（素朴な木）
要件No3 連帯保証人を登録でき、そのプロフィールを入力できるを実現するために使われたアンチパターンです。
debtorsテーブルをみてください。自テーブルにむけた1:1のリレーションがあります。joint_guatantor_idにはdebtors.idの値が入ります。
債務者が何らかの理由で飛んだら、連帯保証人は債務者にランクアップします。これを表現するためにdebtorsテーブルで連帯保証人を管理しようとして、debtorsテーブルを階層構造にしたわけです。
このような設計は隣接リストと呼ばれます。
これは主にCOUNTのような集約関数の扱いに問題を産みます。
ざっくりいうと、どこまでの深さまで集計するかはJOINを用いて集計できますが、JOINの数で集計できる深さが決まってしまいます。
共通テーブル式を用いて再帰クエリを使えばこの集計は簡単にできるようになりますが、MySQLにおいては8.0よりこの機能が実装されている比較的新しい機能となります。
共通テーブル式が使えないRDBMSではアンチパターン以外の何ものでもないでしょう。
###idリクワイアド
要件No7 債務者、家族、連帯保証人をブラックリストで管理できるを実現するために使われたアンチパターンです。
blacklistテーブルをみてください。
レコードを一意に識別するためには主キーが必要であるという固定観念から、blacklistにはblacklist_idが付与されています。
しかし、これがアンチパターンです。
実際にこのテーブルの使い方を考えてみましょう。
システム利用者は融資審査の際、その人がブラックリストに無いかを確認します。その時に発行されるクエリは、このデータベースにおいてはdebtor_idです。
つまり主キーであるblacklist.idではこのテーブルをSELECTしないのです。
識別するために主キーを使わないのであれば、必要はないでしょう。
しかも、主キーのidは単なるidと命名されています。これは他のテーブルの主キーにも当てはまります。
クエリの結果が帰ってきた時、どっちのIDなのかをどうやって識別するのでしょうか？
何となくidと命名しがちですが、せめてblacklist_idのようなテーブル名単数形_idと命名した方が良いでしょう。
###ポリモーフィック関連
要件No7 債務者、家族、連帯保証人をブラックリストで管理できるを実現するために使われた、もう一つのアンチパターンです。
blacklistテーブルをみてください。
debtor_family_idというカラムがあります。ここにはブラックリストに債務者・連帯保証人・家族を登録するために、二つのテーブルのいずれかの主キーが挿入されます。
これをやってしまうと参照先のテーブルが二つ以上あることとなり、参照整合性制約を定義できません。
外部キーなんて要らない、と思っているのなら構いませんが。
ER図（まとも）
アンチパターンを排除して、目的に沿ったER図をみてみます。
###ジェイウォーク対策
familiesを見てください。こちらはとてもシンプルに、debtorsへの外部キーを設定しました。
###EAV対策
まともじゃないER図にはあったprofilesが消えています。
前述の通り、MySQLであれば5.7以上でjson型が使えるので、どうしても頻繁に、いろんな値を設定したい場合はjson型を選択しても良いでしょう。
しかし、ここでは採用しませんでした。なぜか？
まず参考書ではjson型を使う形式の他に、クラステーブル継承という解決法が示されていました。
こちらではつまりオブジェクト指向プログラミングにのように、共通の属性を持つクラスを定義し、必要に応じて追加の属性を定義したサブクラスを使うというものです。
つまり、パターンを洗い出した上でそう自由には値を設定しません。
どのデータが必要で不要なのか、しっかり要件を詰めた上で、テーブルのカラムをfixせよということなのです。
###ナイーブツリー対策
こちらは素直にjoint_guarantorsテーブルを分けて定義しました。
テーブルは一つ増えてしまいましたが、隣接リスト構造は失われ、そして要件も満たしています。
###idリクワイアド対策
問題のblacklistテーブルからidは削除されました。
合わせて各テーブルの主キーもidではなく、テーブル名単数形_idという命名に変更されました。
###ポリモーフィック関連対策
こちらはpersonsテーブルを追加することにより参照方法を変更しています。
これは参考書における共通の親テーブルの作成からヒントを得た解決方法です。
原則、debtors, joint_guarantors, familiesのレコードが生成される時、personsが生成されるようにします。これはつまりテーブル名の通り、３つのテーブルの抽象テーブルです。
blacklistテーブルはこちらのテーブルの主キーを参照することで、ブラックリスト登録ができるようになります。
##おまけ よくあるレビュー指摘事項
こちらはアンチパターンとしては挙げられていませんが、実際の現場で頻発したレビュー指摘事項について紹介し、対策を紹介してみたいと思います。
※ほんとしょうもないやつですので読みとばした方がいいかも
###テーブル名が複数形
テーブル名は原則、複数形が望ましいです。なぜならばテーブルは複数のレコードが存在する集合体であるからです。
英語を母国語としない、しかも複数形の概念がない日本人は特にこのミスが多いように思われます。
blacklistのようにそのものが既に複数形である場合を除いて、複数形を使っていきましょう。
これと同じような問題としてスペルミスがあります。
最悪これは治さずとも動くかもしれませんが、データベースが存続する限り設計者の恥を知らしめ続けるものとなります。
恥ずかしいので絶対に避けたいですね。
###抽象的すぎるカラム名
type, flag, text, classなどなど、カラムには様々な値が入ることでしょう。
しかしそれは具体的にはなんなのですか？ typeってなんのタイプなのですか？
プログラミングにおいても変数の命名はとても重要であるように、カラム名を可能な限り具体的な命名にすべきです。
例えば、無課金ユーザーと課金ユーザーを区別するならuser_classとか、なんらかの本文であるならばtextではなくbodyとするとか、具体的に命名することができます。
コメントを見ずともカラムの意味がわかることが理想的でしょう。
あと、flagなのにboolean以外の値を使うのはやめましょう。
###物理削除？ 論理削除？
レコードを削除する時、論理削除にするか物理削除にするかを検討することがあります。
DELETEは遅いので安易に物理削除を採用すると、レコードが増えて削除対象が増えた場合死にます。
##最後に
まともな方のER図に対するツッコミ、DB設計の失敗談等歓迎です。
もっと良い設計はあるはずですしね。
なお、例として挙げられているER図についてはフィクションです。関西で有名な例の帝王とは一切関係ありません。
##参考文献
この記事を書くにあたりお世話になった書籍たち
- SQLアンチパターン Bil Karwin (著)
- [楽々ERDレッスン] (https://www.amazon.co.jp/%E6%A5%BD%E3%80%85ERD%E3%83%AC%E3%83%83%E3%82%B9%E3%83%B3-CodeZine-BOOKS-%E3%82%B9%E3%82%BF%E3%83%BC%E3%83%AD%E3%82%B8%E3%83%83%E3%82%AF-%E7%BE%BD%E7%94%9F/dp/4798110663) 羽生 章洋 (著)
##2019/06/24追記
たくさんのいいね、コメントありがとうございます。
まともじゃない方のER図はいうまでもなく最悪ですが、まともな方のER図も課題は残っています。
それらの指摘はコメントしていただいた方々の力をお借りする形になるので、是非ともコメントを読んでみてください。