「SQLアンチパターン」の気づき（DB論理設計2）

はじめに

こんにちは。小川です。
以前から気になっていた「SQLアンチパターン」という本を読みました。
とても良い本で今まで気づかずにやっていたことがSQLアンチパターンなんだと知ることができました。
勉強になったので、学んだことを言語化して量が多いのでいくつかの記事に分けて紹介していこうと思います。
今回は第Ⅰ部のDB論理設計のアンチパターンについて記載します。
この記事は「SQLアンチパターン」の気づき（DB論理設計1）の続きになります。

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

アンチパターン

EAVとは可変属性をサポートするために、柔軟な設計を目指したことによるDB設計のアンチパターンのことを言います。

例)属性名をレコードに持たせた以下のようなテーブル
IssueAttributes

issue_id	attr_name	attr_value
1234	product	1
1234	date_reported	2009-06-01
1234	status	NEW

デメリット

1. データ型を指定できない
   EAVではいろんなデータが入ってくることがあるので文字列型にすることが普通です。
   そうすると日付等を登録する際に、型が文字列なのでいろんな形で登録できてしまう。

2. SQLが冗長化してしまう
   例のテーブルのデータをissue_idが1234のデータを取得する際に以下のようなSQLになる

SELECT
    i1.attr_name AS product,
    i2.attr_name AS date_reported,
    i3.attr_name AS status
FROM
    Issues i
LEFT JOIN
    (SELECT * FROM IssueAttributes WHERE attr_name = 'product') i1
ON i.issue_id = i1.issue_id
LEFT JOIN
    (SELECT * FROM IssueAttributes WHERE attr_name = 'date_reported') i2
ON i.issue_id = i2.issue_id
LEFT JOIN
    (SELECT * FROM IssueAttributes WHERE attr_name = 'status') i3
ON i.issue_id = i3.issue_id
WHERE i.issue_id = '1234'

解決策：サブタイプのモデリングを使う

EAVを使わずに、EAVが扱うようなデータを格納する方法はいくつかあります。
その方法を紹介します。

1. シングルテーブルの継承
   全項目を列に持った1つのテーブルで管理する方法
2. 具象テーブル継承
   サブタイプごとにテーブルを作成し、共通項目はすべてのテーブルに重複して持たせる管理する方法
3. クラステーブル継承
   共通情報テーブルとサブタイプごとにテーブルを作成して管理する方法
4. 半構造化データ
   固有情報をXMLやJSONの形式で一つの列に保存する方法

それぞれの方法に良い点悪い点があるのでその時に合った方法で設計する必要がある

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

ポリモーフィック関連に関しては以下のURLで詳しく説明されています。

アンチパターン

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

例) BugsテーブルまたはFeatureRequestsテーブルと1対1の関係にあるCommentsテーブル

CREATE TABLE Comments (
    comment_id SERIAL PRIMARY KEY,
    issue_type    VARCHAR(20), -- 'Bugs'または'FeatureRequests'
    issue_id        BIGINT UNSIGNED NOT NULL,
    comment      TEXT,
);

デメリット

例のテーブルではissue_idのための外部キー宣言がありません。
外部キーでは、複数のテーブルを指定することはできないため、ポリモーフィック関連を使用しているときは関連付けを宣言できません。
その結果、Comments.issue_idの値が親テーブルと値と一致することを保証するための参照整合性を定義することができないのです。

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

1. 参照を逆にする
   CommentsテーブルとBugsテーブル、CommentsテーブルとFeatureRequestsテーブルの間に中間テーブルを作成する

CREATE TABLE BugsComments (
    issue_id           BIGINT UNSIGNED NOT NULL,
    comment_id    BIGINT UNSIGNED NOT NULL,
    UNIQUE KEY (comment_id),
    PRIMARY KEY (issue_id, comment_id),
    FOREIGN KEY (issue_id) REFERENCES Bugs(issue_id),
    FOREIGN KEY (comment_id) REFERENCES Comments(comment_id),
);

CREATE TABLE FeaturesComments (
    issue_id           BIGINT UNSIGNED NOT NULL,
    comment_id    BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (issue_id, comment_id),
    FOREIGN KEY (issue_id) REFERENCES FeatureRequests(issue_id),
    FOREIGN KEY (comment_id) REFERENCES Comments(comment_id),
);

2.　共通の親テーブルを作成する

まとめ

今回は第Ⅰ部のDB論理設計のアンチパターンについて一部まとめてみました。
この記事はこの本を読んで、私がこういう理解をしましたということを言語化してみました。
とても勉強になる本なので皆様もぜひ読んでみてください。

以上、小川でした。