[読書メモ] SQL アンチパターン: I部データベース論理設計のアンチパターン/ふみのて

sql

[読書メモ] SQL アンチパターン: I部データベース論理設計のアンチパターン

2019-10-06 Sun updated: 2019-10-06 Sun

Contents

書籍
1章ジェイウォーク (信号無視)
2章ナイーブツリー (素朴な木)
3章 ID リクワイアド (とりあえずID)
4章キーレスエントリー (外部キー嫌い)
5章 EAV (エンティティ・アトリビュート・バリュー)
6章ポリモーフィック関連
7章マルチカラムアトリビュート (複数列属性)

書籍

SQL アンチパターン SQL Antipatterns (2013年, オライリー・ジャパン)

Bill Karwin 著
和田卓人, 和田省二監訳
小島修訳

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

１つの製品に複数ユーザーを連絡先として登録できるようにしたい

目的: 複数の値を持つ属性を格納する
アンチパターン: カンマ区切りフォーマットのリストを格納する
解決策: 交差テーブルを作成する
まとめ: ひとつひとつの値は個別の行と列に格納しましょう
わたしの感想: 多対多は交差テーブルだな

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

スレッド形式のコメント欄

目的

階層構造を格納し、クエリを実行する

アンチパターン

常に親のみに依存する

解決策

代替ツリーモデルを使用する (各設計の比較はP.30を見よ)

経路列挙 (Path Enumeration)
入れ子集合 (Nested Set)
閉包テーブル (Closure Table)
再帰クエリ ※

まとめ

階層構造はエントリと関連 (リレーションシップ) から成り立ちます。これを念頭に、行う作業に合わせて最適な設計手法を選択しましょう。

わたしの感想

再帰クエリめちゃくちゃ便利だな

-- ※ 再帰クエリの例
WITH RECURSIVE CommentTree
  (comment_id, bug_id, parent_id, author, comment, depth)
AS (
    SELECT *, 0 AS depth FROM Comments
    WHERE parent_id IS NULL
  UNION ALL
    SELECT c.*, ct.depth+1 AS depth FROM CommentTree ct
    INNER JOIN Comments c ON ct.comment_id = c.parent_id
)
SELECT * FROM CommentTree WHERE bug_id = 1234;

MySQL 8.0 ~ , PostgreSQL 8.4 ~ ... はサポートしている

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

「行の重複を避けるにはどうすればよいか」と質問された

目的

主キーの規約を確立する

アンチパターン

すべてのテーブルに「id」列を用いる

解決策

状況に応じて適切に調整する

わかりやすい列名にしよう
規約に縛られない
自然キーと複合キーの活用

まとめ

規約は、役立つと思える場合のみ従いましょう。

わたしの感想

ずっと気になっていたので腹落ち

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

研究所の設備がダブルブッキングされた

目的

データベースのアーキテクチャを単純化する

アンチパターン

外部キー制約を使用しない

解決策

外部キー制約を宣言する

複数テーブルの変更をサポートする (カスケード更新)
オーバーヘッド、……にはなりません

まとめ

データベースでのミスの発生を未然に防ぐために、外部キー制約を用いましょう。

わたしの感想

やっぱりつけたほうが良かったんだな!

5章 EAV (エンティティ・アトリビュート・バリュー)

Issue と Bug と FeatureRequest (機能要望) のはなし

目的

可変属性をサポートする

アンチパターン

汎用的な属性テーブルを使用する

解決策

サブタイプのモデリングを行う

シングルテーブル継承 (Single Table Inheritance)
- Bug と FeatureRequest を同じテーブル (Issues) に入れる
- issue_type 列を持つ
- Bug のみが使う列/FeatureRequest のみが使う列、がある
具象テーブル継承 (Concrete Table Inheritance)
- Bugs テーブルと FeatureRequests テーブルを作る (サブタイプごとにテーブル作成)
- issue_type 列不要
- サブタイプに存在しない属性列を格納せずにすむ
クラステーブル継承 (Class Table Inheritance)
- サブタイプに共通する属性を含む基底型のテーブル (Issues) を１つ作る
- サブタイプごとに追加のテーブルを作る (Bugs/FeatureRequests)
  - Issues.id を外部キーに持つ
  - それぞれに固有の属性のみを持つ
半構造化データ (シリアライズLOB: Serialized LOB)
- issue_type 列を持つ
- サブタイプに固有の属性は LOB列に持つ
- サブタイプの数が多い、頻繁に新しい属性を追加しなければならない、場合など

まとめ

メタデータは、メタデータのために用いましょう。

わたしの感想

ありそう。ある。

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

ユーザーがバグについてのコメントを書き込める => Bugs と Comments が１対多
ユーザーが機能要望についてのコメントを書き込める => FeatureRequests と Comments が１対多
バグについてのコメントも、機能要望についてのコメントも、同じ Comments テーブルに格納したい

目的

複数の親テーブルを参照する

アンチパターン

二重目的の外部キーを使用する

解決策

関連 (リレーションシップ) を単純化する

参照を逆にする
交差テーブルの作成 (Bugs/FeatureRequests が具象テーブル継承)
共通の親テーブルの作成 (Bugs/FeatureRequests がクラステーブル継承)

まとめ

テーブル間の関連 (リレーションシップ) には、参照元テーブルと参照先テーブルが常にそれぞれ１つしかないことを忘れないようにしましょう。

わたしの感想

5章と関連してる。

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

バグデータベースに、バグを分類するための「タグ付機能」を追加したい

目的: 複数の値を持つ属性を格納する
アンチパターン: 複数の列を定義する
解決策: 従属テーブル (Tags テーブル) を作成する
まとめ: 同じ意味を持つ値は、１つの列に格納するようにしましょう。
わたしの感想: すんなり。