(途中)読書メモ:SQL アンチパターン

SQLアンチパターン の読書メモです。

ジェイクウォーク(信号無視)

多対多の関係の場合に交差テーブル(中間テーブル)を作らず、参照先を1フィールドに「カンマ区切りのリスト」で格納するのはアンチパターン

これはやらないなぁ。
交差テーブルを作るメリットとして以下は大きい。

交差テーブルにさらに列を加えることによって、各エントリに属性を追加できます。例えば、製品に連絡先が追加された日付を記録したり、連絡先の優先順位を示す属性をつけたりできます。

私は多対多の関係を表すテーブルのことを「中間テーブル」と呼んでることもあり、名前からは内容が想像できなかったパターン。Wiki では 連想エンティティ - Wikipedia や 連想テーブルだった。英語ではなんて呼ばれてるんだろう。

そして交差を避けるから信号無視なんだって。

ナイーブツリー(素朴な木)

組織図やスレッド形式の掲示板のような階層構造を実装する場合、ノードが parent_id を持つことで階層を表現する隣接リストと呼ばれる設計をするのはアンチパターン。問題点は隣接していない子孫へアクセスして集計などをする操作や、途中のノードを削除してノードを昇格させるなどの操作が難しくなる。

階層構造を実装する設計方法として以下がある。

  • 隣接リスト(アンチパターン
  • 隣接リスト + 再帰クエリ
    WITH キーワードの後に共通テーブル(階層構造の Path 列がある)式を指定する形式を用いることで、隣接リストに格納された階層構造をサポートする。ただし、全てのデータベースでサポートされているわけではないので注意が必要。
    MySQL は 8.0、PostgreSQL は 8.4 でサポートしている
    参考)再帰CTE(共通テーブル式)で階層構造のパスを作る
  • 経路列挙
    経路パス(ファイルシステムのパスのような文字列)を格納する列を保有する。ただし、ジェイウォークのように長さの制限(階層の深さの制限)があったり、参照整合性を DB で維持できないのが欠点。
  • 入れ子集合
    ノードのレコードは自分より下の階層にあるすべてのノードが持つ値より「小さな値」と「大きな値」を保有することで階層を表現する。得意なことはサブツリーに対するクエリ実行で、欠点はノードの挿入や移動をすることで関係する全てのノードの再計算が必要になるので複雑になりがちなところ。
  • 閉包テーブル
    階層パス情報を格納するテーブルを別に作る。

参考)テーブル設計まで細かく書いてくれてる。
階層構造(a.k.a ツリー構造・ディレクトリ構造・フォルダ)をDBでどう設計すべきか - @teitei_tk Blog

子孫へのクエリ実行や参照製合成の意地などを考慮すると、再帰クエリが使える環境であれば再帰クエリを、それ以外であれば閉法テーブルが良いのでは?

自分が階層構造を実装したら…?を考えると隣接リストしか思い付かなかったけど、、読み進めていくうちに以前、階層構造を実装したときは入れ子集合モデルのライブラリ使っていことを思い出した。なんかいい感じに動いてるってだけでライブラリのことをよく知らずに使ってた証拠だね。。反省です。選択肢と選択肢ごとのメリデメを知れたので、今後はライブラリ選定に活かしたいです。

Rails の場合
(参考)Railsで木構造を扱うには|TechRacho(テックラッチョ)〜エンジニアの「?」を「!」に〜|BPS株式会社

ID リクワイアド(とりあえず ID)

すべてのテーブルに主キーとして「id」列を用いるのはアンチパターン

まさに私は Rails に依存してることもあり、何も考えずに id 列を生成してる。。

id という名前は明確な意味を持っていなから、bug テーブルなら bug_id にした方が良いという主張について。いやいや SQL で table 名を付けて列を指定(bug.id)すれば良いじゃんと思ったんだけど、以下の主張に確かになぁとなった。

主キーは「あるテーブル上の1つの行」を識別するためのものなので、主キーの列名でもそのテーブルの種類がわかるようにしておくべきです。そうすれば「bug_id = 1」という情報だけで、データベース上の1行を特定することができます。

あと主キーと外部キーの名前を揃えることで結合条件を on 句の代わりに USING でっていうのも良かった。USING って新人研修的なので習った記憶ある。

これを読んだうえでも主キーを自然キーにする場合以外、すなわち擬似キーを作る場合は id 列にするだろうな。

キーレスエントリ(外部キー嫌い)

外部キー制約をしないというアンチパターン

具体例ではまだ外部キー制約をサポートしていない MySQLMyISAM ストレージエンジンを使っていたから、参照整合性を満たせなくなって、数字の不整合やデータの重複が発生して。そのためにゴミレコードを探すようなスクリプト書いて対応していたそう。

そうか、外部キー制約がなかったときもあるのかぁ。外部キーがなくても良いと思ったことはないけど、強く必要性を求めたこともなく一度つけ忘れたことがあるので、改めて参照整合性の大事さを感じた。

外部キーで関連付けられたレコードに対してどのように対応するかは Rails で書いてしまう(Active Record の関連付け - :dependent)ので、DB 側でも同様の機能をサポートしてること知らなかった。

参考)MySQLの外部キー制約RESTRICT,CASCADE,SET NULL,NO ACTIONの違いは? - Qiita