最近「sql anti-pattern」という本を読みました。
案の定、そういうanti-patternを見事に用いたことがあります。読書の振り返りと世の中に同じミスを犯すところ一つでも減るように、sql anti-patternについて幾つか記事を書こうと思います。
ちなみに、言っておきますが。日本語は母国語ではないので、わかりづらい日本語を書く（書いた）かもしれません、それは私のせいではなく、わかりづらい日本語のせいです。
polymorphic associations （多重関係？）
このパターンのお陰で、本当に辛い思いをしてました（してます）。
何かというと、1つのテーブルに複数以上の関連テーブルがあります。普通じゃありませんか？正確にいうとテーブルの1つの列が2つのテーブルを参照する可能性があります。
わかりづらいです！例で説明しましょう。
教室の設備を管理するテーブルを設計するとします。
- 設備の中に椅子と机という2種類がある。
- 設備がどの教室にあるかと教室のどの位置にあるかを表現したい。
-
同じ教室の位置に椅子か机かのどちらかしか置けない(変ですが、問題を説明するためです)
ではまず椅子、机を管理するテーブルが要るでしょう。
tables (もしかしたら、予約語かもしれません)
| table_id | size | weight |
|---|
chairs
| chair_id | size | weight |
|---|
教室の設備（椅子、机）を管理するテーブルも必要です。
しかし、設備は椅子と机という2つの物がありますね。簡単だよ、以下のようにすれば
class_equipments
| class_equipement_id | class_id | position | equipment_id | equipment_type |
|---|
equipment_typeでどの設備かを判別します。
- equipment_typeが"CHAIR"の場合、equipment_idは椅子のIDを指します。
- equipment_typeが”TABLE"の場合、equipment_idは机のIDを指します。
また、同じ位置に1つの設備しかないという要件は、class_idとpositionの複合ユニーク制約を貼ればいいです。
完璧ですね〜 中国語で表現すれば「輕松愉快！」
問題
外部キーを貼りたいんですけど
class_equipments.equipment_idに対して、外部キー制約をかけようとしたら…無理ですね。
すると、テーブルのリレーションを表現できす、データの整合性はdb上担保できません。例えば、tablesに存在しない机を普通にclass_equipmentsに入れられますよね。
クエリーを書いてみます
select * from class_equipments ce
left join tables t
on (ce.class_equipement_id = t.table_id and ce.equipment_type = 'TABLE')
left join chairs c
on (ce.class_equipement_id = c.chair_id and ce.equipment_type = 'CHAIR')
多分結果の項目はこうなるでしょう
ce.class_equipement_id, c.class_id, c.equipment_id, c.equipment_type, c.position, t.table_id, t.size, t.weight, c.chair_id, c.size, c.weight
机のレコードの場合、 c.chair_id, c.size, c.weight全部NULLでしょう。
ORマッパーを使う時、それうまく対応できるんですか。
主流ORMのhibernate
すみません。主流ORMの経験が少なくて、hibernateは難しいんじゃないかなぁと思います。（間違ってたら、ごめんなさい。）
多分イメージは次のようですね。
public class ClassEquipment{
private Long classEquipmentId;
private Table/Chair equipment;
private String position:
}
なんか無理そうな気がします。
もちろん、次のような形だと、できそうですね。
public class ClassEquipment{
private Long classEquipmentId;
private String equipmentType;
private Long equipmentId;
private String position;
}
ただ、それでTable， Chairが取りたい場合、もう一回クエリーしないといけないんですね。
非主流ORM
自分の知っている非主流ORMの中で、上記の設計にある程度対応できるORMがあります。それはdbfluteというframeworkです！こんな事ができます。
classEquipment.getEquipmentAsTable();
classEquipment.getEquipmentAsChair();
設備を机か椅子にするのはコード上指定できます。興味がある方、調べてください。もっというとdbfluteがなければ、その設計もしなかったかもしれないという気も時々してます。
どう解決すればいいんですか
中間テーブルを作ります
class_equipmentsを諦めて、以下の2テーブルを作ります。
class_tables
| class_id | position | table_id |
|---|
class_chairs
| class_id | position | chair_id |
|---|
そうすると、外部キーが貼れます。
ただ、そうすると、同じ位置に1つの設備しかおけないという条件はどう満たしますか。
今の設計だと、dbで制御することは難しいですね。物足りないです。
super-tableを作ります。
オブジェクト指向の中の親クラスみたいに、親テーブルを作ります。
equipment
| equipment_id |
|---|
tables
| equipment_id | size | weight |
|---|
chairs
| equipment_id | size | weight |
|---|
tables.equipment_id, chairs.equipment_idに外部キーを貼ります。
最後
class_equipments
| class_equipement_id | class_id | position | equipment_id |
|---|
class_equipments.class_id, class_equipments.positionに複合ユニーク制約を貼ります。
結局、それによって、ORMがうまく対応できるようになるんですか。
hibernateの場合、そうでもないですね。勉強不足もあり、多分以下のイメージになりますが、使いやすいかどうかは別の話ですね。
public class ClassEquipment{
private Long classEquipmentId;
private String position;
private Equipment equipment;
}
bottom line
テーブル設計の妥当性は、ORMの都合と分けて考えてもいいですね。polymorphic associationsはORMなんかを考慮しなくても、設計上よくないパターンですね。（ORMの話を取り上げるのはいますごく後悔しています…）
参考資料
SQL Antipatterns: Avoiding the Pitfalls of Database Programming (Pragmatic Programmers)