勉強日記

チラ裏

SQL Antipatterns ch8 Multicolumn Attriubtes

SQL 勉強メモ

pragprog.com

Multicolumn Attriubtes

Store each value with the same meaning in a single column.

  • 例えば電話番号
    • 家
    • 職場
    • FAX
    • 携帯
  • まだあるぜ
    • アシスタントの番号
    • 携帯2つめ
    • 現場事務所
  • まだありそう
  • レアケースのためにカラムを作るのは良くない
    • いくつあれば十分？

Objective: Store Multivalue Attributes

  • ch.2 Jaywalking(信号無視)と同様
  • 属性に複数の値をもたせたい
  • 例: バグにタグ付けする
    • 複数
    • mutually exclusiveじゃない

Antipattern: Create Multiple Columns

  • tag1, tag2, tag3カラムとか作っちゃう
    • 【補】第一正規形ですらない
      • リレーションの属性は直交していなければならない
  • 普段やる操作が途端に複雑になる

Searchinig for Values

  • タグ一致検索
  • WHERE句でOR条件を書かないといけない
  • あるいは普段書かないようなIN述語 
SELECT FROM Bugs
 WHERE 'performance' IN (tag1, tag2, tag3)
   AND 'printing'    IN (tag1, tag2, tag3);

Adding and Removing Values

  • タグの追加
    • どのtagNカラムまで埋まってるのか場合によりけりなのでケアする必要あり

Ensuring Uniqueness

INSERT INTO Bugs (description, tag1, tag2, tag3)
  VALUES ('printing is slow', 'printing', 'performance', 'performance');
  • テーブル定義上、列間で重複を許してしまう
  • これを禁止するための直接的な制約がない
    • 【補】トリガーとか使わないとむり

Handling Growing Sets of Values

  • tagNはいくつ必要ですか
  • ほどほどの個数にしておいて後から増やす？
  • 変更コスト高い
    • データがすでに入っているテーブルのテーブル定義を変更すると可用性損ねる
      • テーブル全体のロックを生じる
    • DB製品によってはETLが必要
      1. データ退避する
      2. 新しいテーブル作る
      3. 新しいテーブルにデータ読み込む
    • あらゆるSQLをしらみ潰しに調べてORやINまわりを修正する必要がある
      • 修正が漏れると、見つけづらいバグを埋め込んでしまう

How to Recognize the Antipattern

  • UIやドキュメント上で、「複数の値が設定可能だが上限がある」というようなものがあれば怪しい
    • あえて上限を設けることもあるが、普通はない
    • 上限に正当な理由がなさそうならば、その上限はMulticolumn Attributesアンチパターンによるものである可能性あり
  • こんなのが聞こえてきたら注意:
    • 「最大いくつのタグまでサポートする必要がある？」
      • Multicolumn Attributesアンチパターンを踏んでいるため、tagNをいくつ定義するか決める必要がある
    • 「SQLで同時に複数カラム検索するにはどうすればいい？」
      • そのデータは複数カラムに分けずに、単一のカラムに入っているべきということ

Legitimate Uses of the Antipattern

  • ドメインは同じだが論理的に異なるデータである場合
    • 例
      • バグの報告者
      • 修正担当者
      • QA担当者
    • この場合、単一のaccount_idカラムにまとめて、roleカラムで役割を表現することもできる
    • が、Entity-Attribute-Valueアンチパターンと同じ問題が生じるので良くない
  • 【補】「タグ機能」の場合、パフォーマンスのためにあえて正規化したキャッシュテーブルを作ることはあると思う
    • 膨大なレコード数を、検索するタグの数だけJOINするのをさけるため

Solution: Create Dependent Table

  • Tagsテーブル作る

コラム: Patterns Among Antipatterns

  • JaywalkingとMulticolumn Attributesは目的を同じくしたアンチパターン
    • 「複数の値をもつ属性を格納する」
  • 両パターン、一対多/多対多両方で適用されうることに留意する