SQLアンチパターンを読んだので、論理設計編を個人的なアンチパターンと共にまとめてみる。
論理設計の章ではデータの構成、関連付けなどをまとめています。
具体的にはDBのテーブル、列、関連性の設計があります。
物理設計の章は格納データタイプの決定後、テーブルやインデックスの定義、データ型の決定となります。具体的なDBの中身の決定ですね。
今回は論理設計の章についてまとめていきます。
個人的な所感が多く含まれますので、リアクションいただけると嬉しいです。
1. ジェイウォーク（信号無視）
2. ナイーブツリー（素朴な木）
3. IDリクワイアド
4. キーレスエントリ
5. EAV（エンティティアトリビュートバリュー）
6. ポリモーフィック関連
1. ジェイウォーク（信号無視）
これ、まさしくこのアンチパターンを頭の中で考えたことがありました。
（適当な絵面が浮かばなかった…）
文字面で説明しておくと、Product(製品)とUser(ユーザー)が1: nの関係です。製品は複数のユーザーが使用しているので、連絡先を参照できるようにしたいという要件が投げられたとします。
今回のアンチパターンはカンマ区切りでaccount_idを列挙するカラムをProduct側に定義するというものです。
この場合だとカラムを1個定義するだけで終了なのですが、
如何せん運用中にどんなSQLを発行すればいいか想像つかないですよね…
後該当データがダブりそう。
事例ではユーザーから製品を参照したいときに正規表現が必要だと言っています。
account_id = 3のユーザーのidが登録されている製品と、その製品詳細が欲しい… となるとテーブルを結合してViewを作りたい気持ちなのですが、
ユーザーidで製品抜いて！該当の製品idでデータ抜いて！その作業だけでもややこしいSQLが発行されるのが想像に容易くないですよね…？（さらに最近アクティブな製品だけ抜きたい…Productのupdated_atでソートをかけたいなど、要件追加されたらちょっと面倒臭いなあ…）
それが100行近くに肥えた暗黒クエリとして勉強会のスライドショーに晒されるのも遠くない未来でしょう。
* ソリューション
中間テーブルを作って相互参照が容易なものにする。
外部キーをカラムに持つテーブルを作るんですね。
これだったらNULLの心配もないですし、更新・削除がお手軽になりそうです。
中間テーブルはRailsを現場で使い始めてから初めて知りました。
何でもかんでも適用していいという訳ではないと思いますが、命名が重要ですよね。
連絡先だけじゃなくて、関連性だけに注視するならProductUserMappingって名前でもいいのかなあと思います。
2. ナイーブツリー（素朴な木）
これもJavaのアプリケーション作成時に苦しみました。
記事に結びつくコメントのテーブル設計ですが、投稿された後にどんな運用が考えられるかというパターンを出しきれなかったのです。
id = 1のコメントに対する返信のコメントを書いて、返信文だけ消したいのに
親記事まで消してしまったり…
発行するクエリが悪い場合もあるんですけどね…💧
今回のお題では親記事に連なるコメントを取得したい場合。
親のidが動的に変わるので、親記事内でparentとして扱われているコメントを取得しなければいけないので、左外部結合をひたすら行わなければいけないようです。
<<アンチパターン>>
LEFT OUTER JOIN …左軸に検索元のテーブル1の外部キーを提示し、抜きたい関連テーブルを右側に置くことでNULLを混入せずにデータが取れる。
* ソリューション1 - 経路列挙
該当データまでのパスをcomment_idで表現し、格納します。
上の図でいう『いってみたい』のコメントを抽出したい場合に、親記事のcomment_id = 1, 該当のcomment_id = 2となる為、1/2/というパスをしまっておきます。
隣にもう一列できた場合も1/5/のように表現できるので、元記事に連なるデータは、ワイルドカードを使って1/%のように取得できます。
* ソリューション2 - 入れ子集合
親記事を起点にして、今回は便宜上その距離をrightとleftで表します。
こんな深さの記事があったとして
親記事は1番上の高さにいるのでleftには1
データの起点から数えて12番目の距離にいるので、rightは12を代入します。
親記事から1階層下であれば左側からleftの値が1ずつ増えるのです。
この起点となるデータ（親記事）からのパスを辿ることで、目的のデータが抜けるという手続きを想定しています。
画像の『こんな姉妹店が…』以下のデータを抜きたい場合は、該当階層のleftの最小値とrightの最大値を検索して抜く事ができます。
元記事に連なるデータはleftの最小値が2、rightの最大値は11までのデータを指定する事で取得が可能です。
* ソリューション3 - 閉包テーブル
各データまでのパスを格納するテーブルを作ります。
親記事は子孫のデータ分、自身を含めたパスデータを持ちます。
各子孫も、自分よりも下にデータを持っている場合にそのパスを格納します。
パスは記事の持っているcomment_idで表現します。
コメント元記事に連なるデータは、記事とパスのテーブルを結合し、先祖と子孫の最大距離を指定してクエリで抜く事ができます。
少し長くなったので、3~6は次記事以降にまとめます🐕
