SQLアンチパターンの内容をまとめます その1

どんな本？

SQLアンチパターン（以下本書）は、データベース設計やSQL記述時に避けるべき25のアンチパターンを紹介する書籍です。

データベースに関して、システム開発においてよくある失敗（アンチパターン）を回避するための具体的な手法を学ぶことができます。

有名なエンジニアである和田 卓人氏が監訳として携わっています。

内容まとめ

1章 ジェイウォーク（信号無視）

複数の値を入れたい時に、カンマ区切りで入れてしまう、というアンチパターンです。

アンチパターン

例として、以下のような状況を考えます。

1つのproductに、複数のaccount_idを保持したい

アンチパターンでは、下記のようになります。

• 「account_id」カラムの値として「 1, 2, 3, 4 」を格納する

こうなると、SQLでカラムの値を判定するのが困難になります。

（いちいち値をカンマで分割して・・・という処理が必要）

解決策

交差テーブルを作りましょう！

交差テーブルとは、2つのテーブルを参照する外部キーを持つテーブルです。

下記のようなイメージです。

• productテーブル
  • product_id
• product_accountテーブル
  • product_id
  • account_id
• accountテーブル
  • account_id

2章 ナイーブツリー（素朴な木）

ツリー構造のデータを扱う際のアンチパターンです。

例えば、記事へのコメントなど、スレッド状になっているデータが挙げられます。

アンチパターン

常に親のみに依存する。

自分自身の親IDのみ保持する、というイメージです。

解決策

代替ツリーモデルを使用しましょう！

本書では、以下の3種類が挙げられています。

• 経路列挙(Path Enumeration)
  • パスを文字列として保持する
• 入れ子構造(Nested Set)
  • left, rightカラムを保持するやり方です
  • 私はCakePHPのTreeヘルパーで使ったことがあります！
• 閉包テーブル(Closure Table)
  • 新たにツリー構造を保持するためのテーブルを作成するやり方です
  • 一番きれいな解決策のように見えます
  • 機会があれば使ってみたい！

3章 IDリクワイアド（とりあえずID）

アンチパターン

思考停止でIDを作成してしまった結果、テーブル構造が複雑化してしまうというアンチパターンです。

解決策

きちんとテーブル設計をしましょう。

下記のような点について考える必要があります。

• 主キーが必要か
• 必要な場合、どのカラムを主キーにするか
  • 新たにIDを追加するのか、格納したいデータが主キーになりうるのか

Laravelなどのフレームワークを使用していると、自然とIDカラムを作ってしまいがちですが、設計段階で本当にIDが必要なのか考える必要がありそうです。

4章 キーレスエントリ（外部キー嫌い）

外部キーを使わなかったことにより、データ構造がわかりにくくなってしまうというアンチパターンです。

解決策

外部キーについて理解し、適宜取り入れましょう！

外部キーを取り入れることで、テーブル間の参照を正しく表現でき、バグを減らす効果が期待できます。

DBのカスケード更新という便利な機能も使えるようになります。

5章 EAV（エンティティ・アトリビュート・バリュー）

データを状態管理したい時に起こりがちなアンチパターンです。

例えばstatusテーブルを作成し、「NEW」「失敗」「HIGH」などといった文字列を格納して状態を管理しようとしてしまうと、アンチパターンとなります。

EAVでは下記のような欠点があります。

• 必須属性を付与できない
• SELECT文が複雑になる
  • statusテーブルとJOINする必要がある
• データ型を使えない
  • 文字列のみになってしまう

解決策

• シングルテーブル継承
  • Issueテーブルを作る
    • issue_typeカラムでデータの構造を明示する
    • bug, featureなど
• 具象テーブル継承
  • Bugs, Featuresといったテーブルを作る
• クラステーブル継承
  • OOのクラスのように、テーブルを作成する
  • Issues, Bugs, Features

個人的には3番目のクラステーブル継承が一番わかりやすくてしっくりきました。

6章 ポリモーフィック関連

複数の親テーブルを持ってしまう、というアンチパターンです。

例えば、以下のようなケースです。

• bugs, featuresという2つのテーブルがある
  • どちらもcommentテーブルと関連している

こうなると、commentからデータを取得する際に困ってしまいます。

解決策

データモデルを再考しましょう！

今回の例でいうと、以下のように構築し直すことができます。

• commentテーブルがある
• bugs_comments, features_commentsという2つのテーブルがある
• bugs, featuresという2つのテーブルがある

交差テーブルを使用することで、すっきりとまとめることができます。

または、以下のような構造にもできます。

• issuesテーブルがある
  • 下記3テーブルが紐づいている
  • commentテーブル
  • bugsテーブル
  • featuresテーブル

7章 マルチカラムアトリビュート（複数列属性）

属性を管理するために、tag_1, tag_2, tag_3,……というようなカラムを作ってしまうアンチパターンです。

以下のような問題があります。

• 列がどんどん増えていく可能性がある
• 複数列に対するUPDATEをしたい場合、SQLが複雑になる

解決策

新たに属性を格納するテーブルを作りましょう！

上記の例でいうと、tagsテーブルを作成して属性を管理しましょう。

8章 メタデータトリブル（メタデータ大増殖）

どんどんとデータが増えていってしまうアンチパターンです。

例えば以下のようなテーブルを想像してみてください。

• bugs_2018
• bugs_2019
• bugs_2020
• bugs_2021

（年の部分がメタデータにあたります）

• もしバグの日付変更によって、年が変わったら？
• 複数テーブルをまたいだクエリ作成を依頼されたら？
• テーブル構造を変えることになったら？

このテーブル達の保守運用は、しんどそうですね……。

解決策

パーティショニングを使用しましょう！

主要なデータベース管理ツールであれば、パーティショニング機能が存在するはずなので、調べてみましょう。

MySQLの場合

おわりに

今回は1〜8章までについてまとめました。

次回もお楽しみに！