（途中）読書メモ：SQL アンチパターン
SQLアンチパターン の読書メモです。
ジェイクウォーク（信号無視）
多対多の関係の場合に交差テーブル（中間テーブル）を作らず、参照先を1フィールドに「カンマ区切りのリスト」で格納するのはアンチパターン。
これはやらないなぁ。
交差テーブルを作るメリットとして以下は大きい。
交差テーブルにさらに列を加えることによって、各エントリに属性を追加できます。例えば、製品に連絡先が追加された日付を記録したり、連絡先の優先順位を示す属性をつけたりできます。
私は多対多の関係を表すテーブルのことを「中間テーブル」と呼んでることもあり、名前からは内容が想像できなかったパターン。Wiki では 連想エンティティ - Wikipedia や 連想テーブルだった。英語ではなんて呼ばれてるんだろう。
多対多関連のテーブルの呼び方ってどれが一般的なんだろう。SQLアンチパターンでは「交差テーブル」を使ってるし、他にも「中間テーブル」あたりはよく聞く。 英語版Wikipediaではcross-reference tableがjunction tableにリダイレクトされるが。
— 局所解 (@s_osa_) 2013年12月17日
そして交差を避けるから信号無視なんだって。
ナイーブツリー（素朴な木）
組織図やスレッド形式の掲示板のような階層構造を実装する場合、ノードが parent_id を持つことで階層を表現する隣接リストと呼ばれる設計をするのはアンチパターン。問題点は隣接していない子孫へアクセスして集計などをする操作や、途中のノードを削除してノードを昇格させるなどの操作が難しくなる。
階層構造を実装する設計方法として以下がある。
- 隣接リスト（アンチパターン）
- 隣接リスト ＋ 再帰クエリ
WITH キーワードの後に共通テーブル（階層構造の Path 列がある）式を指定する形式を用いることで、隣接リストに格納された階層構造をサポートする。ただし、全てのデータベースでサポートされているわけではないので注意が必要。
※ MySQL は 8.0、PostgreSQL は 8.4 でサポートしている
参考）再帰CTE（共通テーブル式）で階層構造のパスを作る - 経路列挙
経路パス（ファイルシステムのパスのような文字列）を格納する列を保有する。ただし、ジェイウォークのように長さの制限（階層の深さの制限）があったり、参照整合性を DB で維持できないのが欠点。 - 入れ子集合
ノードのレコードは自分より下の階層にあるすべてのノードが持つ値より「小さな値」と「大きな値」を保有することで階層を表現する。得意なことはサブツリーに対するクエリ実行で、欠点はノードの挿入や移動をすることで関係する全てのノードの再計算が必要になるので複雑になりがちなところ。 - 閉包テーブル
階層パス情報を格納するテーブルを別に作る。
参考）テーブル設計まで細かく書いてくれてる。
階層構造(a.k.a ツリー構造・ディレクトリ構造・フォルダ)をDBでどう設計すべきか - @teitei_tk Blog
子孫へのクエリ実行や参照製合成の意地などを考慮すると、再帰クエリが使える環境であれば再帰クエリを、それ以外であれば閉法テーブルが良いのでは？
自分が階層構造を実装したら…？を考えると隣接リストしか思い付かなかったけど、、読み進めていくうちに以前、階層構造を実装したときは入れ子集合モデルのライブラリ使っていことを思い出した。なんかいい感じに動いてるってだけでライブラリのことをよく知らずに使ってた証拠だね。。反省です。選択肢と選択肢ごとのメリデメを知れたので、今後はライブラリ選定に活かしたいです。
＊Rails の場合
（参考）Railsで木構造を扱うには｜TechRacho by BPS株式会社
- 隣接リスト / awesome_nested_set
- 経路列挙 / ancestry
- 入れ子集合 / awesome_nested_set
- 閉包テーブル / closure_tree
※ 直近の親や子にアクセスするクエリをより容易に書く方法として、ノードテーブルに parent_id を持たせている。
ID リクワイアド（とりあえず ID）
すべてのテーブルに主キーとして「id」列を用いるのはアンチパターン。
まさに私は Rails に依存してることもあり、何も考えずに id 列を生成してる。。
id という名前は明確な意味を持っていなから、bug テーブルなら bug_id にした方が良いという主張について。いやいや SQL で table 名を付けて列を指定（bug.id）すれば良いじゃんと思ったんだけど、以下の主張に確かになぁとなった。
主キーは「あるテーブル上の1つの行」を識別するためのものなので、主キーの列名でもそのテーブルの種類がわかるようにしておくべきです。そうすれば「bug_id = 1」という情報だけで、データベース上の1行を特定することができます。
あと主キーと外部キーの名前を揃えることで結合条件を on 句の代わりに USING でっていうのも良かった。USING って新人研修的なので習った記憶ある。
これを読んだうえでも主キーを自然キーにする場合以外、すなわち擬似キーを作る場合は id 列にするだろうな。
キーレスエントリ（外部キー嫌い）
外部キー制約をしないというアンチパターン。
具体例ではまだ外部キー制約をサポートしていない MySQL の MyISAM ストレージエンジンを使っていたから、参照整合性を満たせなくなって、数字の不整合やデータの重複が発生して。そのためにゴミレコードを探すようなスクリプト書いて対応していたそう。
そうか、外部キー制約がなかったときもあるのかぁ。外部キーがなくても良いと思ったことはないけど、強く必要性を求めたこともなく一度つけ忘れたことがあるので、改めて参照整合性の大事さを感じた。
外部キーで関連付けられたレコードに対してどのように対応するかは Rails で書いてしまう（Active Record の関連付け - :dependent）ので、DB 側でも同様の機能をサポートしてること知らなかった。
参考）MySQLの外部キー制約RESTRICT,CASCADE,SET NULL,NO ACTIONの違いは？ - Qiita
EAV（エンティティ・アトリビュート・バリュー）
EAV はカラムの可変性をサポートするために Key, Value のようにデータを格納するパターンで、これを使うことがアンチパターン。
EVA のデータ構造。
RDB はそもそも EAV に対応してない、外部キーもデータ型も集計でも使いづらいので基本は使うな。EVA したいなら Key Value で格納できる DB を使うこと。
この記事が分かりやすかった）SQLアンチパターン勉強会 第五回：EAV(エンティティ・アトリビュート・バリュー) - Qiita
アンチパターン回避策として
