SQLアンチパターンを読んだのだが、論理設計編のまとめがようやく終わる。
サボっていたらだいぶ日が空きました、DB論理設計編のまとめです。
結構散文化としてしまっていますが、よろしければ引き続きご覧くださいませ🐶
ようやく完結、DB論理設計編。
1. ジェイウォーク（信号無視）
2. ナイーブツリー（素朴な木）
3. IDリクワイアド
4. キーレスエントリ
5. EAV（エンティティアトリビュートバリュー）
6. ポリモーフィック関連
EAV（エンティティアトリビュートバリュー
select date_reported, COUNT(*)
from Bugs
group by date_reported;
これはバグテーブルからdate_reported毎に集計した数を出力しています。
ただし、日付のフォーマットが異なる為にこれを整備できないとしたらどのような方法を取るのがベターなのでしょうか？
可変属性をサポートしたい場合
オブジェクト指向設計になぞらえるならば、基底のモデルを拡張してサブクラスを立てることができます。
インスタンスは同じ基底クラスを持ち、サブタイプのインスタンスでもありますが、
あるサブタイプの持つ属性は基底型や他のサブタイプに存在するとは限りません。
このような例を1行のレコードに格納したい場合、自ずとカラムの定義が揺らぐことになります。
アンチパターンの実現例
属性テーブルの用意
1つのモデルに対し、属性(カラム)を内包したテーブルを作成します。
これが実現するパターンは、カラムの存在意味と名前がロジックに基づき固定されている場合であり、
無闇に列を増やしていけば管理がさらに複雑になることが想定されます。
投げるクエリが複雑になる
単純にデータのidと報告日が見たい！
select issue_id, date_reported from issues;
EAV設計の場合
select issue_id, attr_value as date_reported
from IssueAttributes
where attr_name = 'date_reported';
データ整合性の保ち方
date_reportedが設定されていない場合の運用が決定されていない
報告書がまとめられない…
date_reportedが設定されていないデータのためにクエリを投げて検査結果をメールで寄越す？適当な日付を入れてしまう？
SQLのデータ型扱いづらい問題
このattr_nameはStringで定義されているため、日付型でないデータも許容します。
計算結果を挿入するデータをBIGINTなどで定義してから、うっかりDECIMALやUNSINGEDなデータが投げられてしまったら、
このテーブルはデータ更新を拒否するかもしれません。
参照整合性を強制できない
薄々感づくところもあるかもしれませんが、attr_nameが外部キーであった場合に、
他のテーブルでも同じ型でこのデータを参照する必要があり、かつこのデータに一致することが強制されます。
attr_dateが文字列型であるのに、外のテーブルからTIMESTAMP型としてこれを参照することは出来ません。
(データを参照するためにjoinの手間がかかる…)
ソリューション
サブタイプのモデリングを行う
シングルテーブル継承(Single Table Inheritance)
基底クラスと関連データを一つのテーブルにまとめ、タイプでサブクラス識別できるように設計する。
タイプ毎に使用するカラムを定める。Ruby on RailsのActiveRecordがこの設計を採用している。
具象テーブル継承(Concrete Table Inheritance)
サブタイプ毎にテーブルを設計する。サブタイプを判別するカラムを入れずに設計できる。
基底型に設定されているデータかの判別がしづらく、共通部に新たなカラムを加えたい場合に共通テーブルの変更が必要になる。
サブタイプに関わらずデータを取得したい場合、UNION句の使用によりビューを作成することで実現できる。
クラステーブル継承(Class Table Inheritance)
オブジェクト指向に則り、一つのテーブルを一つのクラスとみなして設計する。
サブクラスは基底クラスのidを参照し、外部キーで関連性が保たれる。
基底クラスからサブクラスへは1対1のリレーションが強制される。
ポリモーフィック関連
サブクラスそれぞれに紐つくCommentsテーブルが欲しいけれども、どう実装しよう？
同じ基底クラスを持つBugs, FeatureRequestsですが、新たなCommentsテーブルの実装にはひとひねりが必要そうです。
同じ外部キーを通して関連性を持つことはできない為、ポリモーフィック関連(Polymorphic Associations)というテクニックを使います。
プロミスキャス・アソシエーションという別名は、複数のテーブルを参照できる為に無差別な関連という意味で使用されます。
ポリモーフィック関連の定義
Commentsテーブルには、サブクラスを判別するissue_typeが定義されます。
その他の外部キーはaccounts_id一種のみです。
ポリモーフィック関連の特徴は、参照整合性制約を定義できない部分にあり、
言い換えればissue_typeに定義されている種類も、親のissue_idも、本当に親テーブルで定義されているものかはわからないのです。
-- データを抜くときの注意
issue_idはBugs, FeatureRequestsの両テーブルに同じ値の主キーを持つ可能性を孕んでいます。
正確に紐つくデータを抜き取るには、issue_typeも同時に使用することが重要です。
両方のテーブルを参照したい場合は外部結合を使います。
