はじめに
SQLアンチパターンを読んだので簡単にまとめてみました。
内容としては、論理設計に関するアンチパターンのみを挙げております。
- カンマ区切りで値を格納する
- 同じテーブルにある別行を参照する階層構造を持つ
- 反射的に id のような代替キーを使用する
- 外部キー制約を使用しない
- 1つのカラムに複数テーブルの参照を持たせる
- 同じ意味を持つ値を格納するカラムを複数定義する
- データを含む列名やテーブル名を定義する
カンマ区切りで値を格納する
| 部署名 | 社員番号 |
|---|---|
| 経理部 | 1,2,3 |
| 開発部 | 4 |
| 営業部 | 5,6 |
アンチパターンである理由
- とにかくクエリの生成がしづらい。
- 定義する文字列の長さに根拠を示せない。
→ 将来においても対応し得る長さであるか？
解決策
多：多の関連を持つ中間テーブルを作成し、値を1行ずつ格納する。
同じテーブルにある別行を参照する階層構造を持つ
| 組織名 | 組織ID | 親組織ID |
|---|---|---|
| 親組織 | 1 | NULL |
| 子組織 | 2 | 1 |
| 孫組織 | 3 | 2 |
アンチパターンである理由
- 深い階層であればあるほど取得するクエリにおいて JOIN の数を増やす必要がある。
- 親を削除する場合に子孫から順番に削除する必要がある。
解決策
長所と短所を見極めながら、以下のような代替ツリーモデルを使用する。
- 経路列挙モデル
階層をパスとしてテーブルに格納しておく。 - 入れ子集合モデル
階層関係を子孫の集合関係として捉えて、nsleft および nsright と呼ばれる数値で関係を表す。 - 閉包テーブルモデル
階層のパスを示すテーブルを作成し、直接の親子関係だけでなく全体のパスを格納する。
反射的に id のような代替キーを使用する
アンチパターンである理由
- 制約の指定漏れにより重複行が生じる恐れがある。
- カラム名を「id」とした場合、キーの意味が分からなくなる。
解決策
本当に必要な場合のみ用いる。
必要な例）ORMフレームワークを用いて開発を行っている場合
外部キー制約を使用しない
アンチパターンである理由
- プログラムのコードに不具合があった場合に不正なデータが登録可能となる。
- 長期間運用していると予期せぬデータの不整合が発生する恐れがある。
解決策
外部キー制約を用いる。
Key・Valueカラムを持つテーブルを作成する
| key | value |
|---|---|
| TYPE | error |
| DATE | 2022/07/16 |
| MESSAGE | NULLは登録できません。 |
| TYPE | success |
アンチパターンである理由
- データ型や必須属性を活用できない。
- key に一貫性がない場合はデータの取得が困難となる。
解決策
状況に応じて適切なテーブル設計を用いる
例）シングルテーブル継承
格納し得るすべてのタイプの属性を個別の列に格納する
| type | date | message |
|---|---|---|
| error | 2022/07/16 | NULLは登録できません。 |
| success |
1つのカラムに複数テーブルの参照を持たせる
| 顧客名 | 住所タイプ | 住所ID |
|---|---|---|
| 山田太郎 | 配送先 | 1 |
| 鈴木花子 | 本人宅 | 2 |
| 山田太郎 | 本人宅 | 3 |
| 配送先ID | 配送先 |
|---|---|
| 1 | 東京都 |
| 本人宅ID | 本人宅 |
|---|---|
| 2 | 大阪府 |
| 3 | 東京都 |
アンチパターンである理由
- 外部キー制約ではテーブルを1つだけ指定しなければならないため、外部キー制約を定義できない。
- 複数テーブルへの参照を持つ場合、動的に JOIN を行うことができない。
解決策
中間テーブル、または共通の親テーブルを作成する。
同じ意味を持つ値を格納するカラムを複数定義する
| 商品名 | 分類1 | 分類2 | 分類3 |
|---|---|---|---|
| パソコン | 電子機器 | IT | |
| 炊飯器 | 家電 | ||
| iPhone | IT | スマホ | 電子機器 |
アンチパターンである理由
- 値の検索や追加、削除が困難となる。
- 同様の値が存在しないこと（一意性）の保証ができなくなる。
解決策
従属テーブルを作成し、値を複数の列ではなく1行ずつ持たせるようにする。
データを含む列名やテーブル名を定義する
- Customers_2021 テーブル
- Customers_2022 テーブル
- Customers_2023 テーブル
アンチパターンである理由
- データごとに列やテーブルを作成する必要があるため、どんどん数が増えていく。
- データの更新によって不正な行が生じる恐れがなる。
例）2021年のテーブルに誤って2022年のデータを登録したので、該当のデータを正しい値に更新した。
→ 更新するのではなく、2022年のテーブルに移動（削除および新規登録）する必要がある。
解決策
パーティショニングとテーブルの正規化を行う。