第5章 EAV

可変属性があるときどのように設計するか
EAVパターンではまず設計しない

シングルテーブル継承

クラステーブル継承
- まず思いつくやつ
Active RecordはRails用語じゃなかった

半構造化データ
- LOB = large Object

yoshino

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

アンチパターン：多対多が成り立つ2つのエンティティにおいて、片方のテーブルの1つのカラムに、もう片方のテーブルのIDをカンマ区切りで入れる（＝正規化されていない）
解決策：中間テーブル（交差テーブル）を使う、ひとつひとつの値は個別の行と列に格納しましょう

yoshino

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

階層構造を持つデータの設計
- 組織図、スレッド形式のコメント欄
アンチパターン：隣接リストモデル＝ナイーブツリー構造

CREATE TABLE Comments (
    comment_id SERIAL PRIMARY KEY,
    parent_id      BIGINT UNSIGNED,
    comment      TEXT NOT NULL,
    FOREIGN KEY (parent_id) REFERENCES Comments(comment_id),
);

階層の深さに関わらず子孫を取得したい場合、再帰的なクエリを実行しなければならない
- MySQL8.0、PostgreSQLでは再帰クエリをサポートしている
ノードの削除が大変（これも再帰的な操作が必要になる）
- ON DELETE CASCADEをつければ自動化できる
  - CASCADE = 連鎖的に

アンチパターンを用いてもいい場合：必要な操作が「ノードの直近の親子を取得」「ノードの挿入」のみである場合
解決策：経路列挙モデル、入れ子集合モデル、閉包テーブルモデル

経路列挙モデル：pathに1/2/3/などを入れる。UNIXのパス/usr/local/libと同じ。
- 子孫、先祖の取得、ノードの追加が容易
- データベースはパスが正確かどうか保証できない

CREATE TABLE Comments (
    comment_id SERIAL PRIMAL KEY,
    path              VARCHAR(1000),
    comment      TEXT NOT NULL,
);

入れ子集合：子孫の集合に関する情報をnsleft``nsrightで管理する。
- サブツリーに対するクエリ実行が重要な場合に適している
- ノードの挿入や移動はnsleft``nsrightの再計算が必要になるため大変
閉包テーブル：Commentsテーブルに加えてTreePathsテーブルを新たに定義する。TreePathsテーブルに、先祖／子孫関係の組み合わせを格納する。ツリー上の離れた位置にあるノードも対象、また自分自身を参照する行も追加する。
- 子孫、先祖の取得、ノードの追加、削除、すべて簡単にできる
- ツリーが大きくなると、TreePathsのレコード数が増える

yoshino

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

テーブルに、一意であり非NULLなカラムがあるのであれば、それを主キーにして、擬似キー（連番、uuidなど）を使わなくてもよい
Railsは、テーブル作成時、デフォルトではidカラムに対してインデックスを作る。それ以外のカラムに対してはインデックスを作らない。

複合主キーのデメリットは、複合主キーを参照する外部キーもまた列の組み合わせでなくてはならないところ。

yoshino

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

外部キー制約は使用すべき
外部キーが避けられる理由：複数のテーブルの関連し合う列を更新する際に、外部キー制約が邪魔だと感じるから
カスケード更新／削除：親テーブルのレコードを削除または更新したときに、子テーブルの関連するレコードを自動的に更新／削除してくれる機能
- メリット：アプリケーション側で複雑な更新／削除の処理を実装しなくてよくなる。
- デメリット：外部キーの利点である誤処理防止がなくなって、オペミス・実装ミスが起きる可能性がある。どのレコードが更新／削除されたかわからなくなる。

yoshino

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

ポリモーフィック関連の例：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の値が親テーブルの値と一致することを保証するための参照整合性制約を定義できない。issue_typeも同様。
ポリモーフィック関連を用いてもよい場合
- ORMフレームワークを使うとき

解決策１：参照を逆にする
- CommentsテーブルとBugsテーブルまたはFeatureRequestsテーブルの間に中間テーブルを作成する
- Commentsテーブルのあるレコードが、BugsテーブルとFeatureRequestsテーブルの両方に1回ずつ関連づけられる可能性はあるので、アプリケーション側で対応しなければならない

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),
);

SQLクエリが複雑になるので、データベースビューを活用するとよい

解決策2：共通の親テーブルの作成
- 外部キーによるデータ整合性制約に依存できる

CREATE TABLE Issues (
    issue_id          SERIAL PRIMARY KEY
);

CREATE TABLE Bugs (
    issue_id          BIGINT UNSIGNED PRIMARY KEY,
    FOREIGN KEY (issue_id) REFERENCES Issues(issue_id),
    ...
);

CREATE TABLE FeatureRequests (
    issue_id          BIGINT UNSIGNED PRIMARY KEY,
    FOREIGN KEY (issue_id) REFERENCES Issues(issue_id),
    ...
);

CREATE TABLE Comments (
    comment_id    SERIAL PRIMARY KEY,
    issue_id           BIGINT UNSIGNED NOT NULL,
    comment         TEXT,
    FOREIGN KEY (issue_id) REFERENCES Issues(issue_id)
);

yoshino

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

例：バグデータベースにタグ機能を追加する。1つのタグに複数のタグをつけられるようにする。

CREATE TABLE Bugs (
    bug_id        SERIAL PRIMARY KEY,
    description VARCHAR(1000),
    tag1             VARCHAR(20),
    tag2            VARCHAR(20),
    tag3            VARCHAR(20)
);

解決策：従属テーブルを作成する

CREATE TABLE Tags (
    bug_id        BIGINT UNSIGNED NOT NULL,
    tag              VARCHAR(20),
    PRIMARY KEY (bug_id, tag),
    FOREIGN KEY (bug_id) REFERNCES Bugs(bug_id)
);

yoshino

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

トリブルとは？

メタデータトリブルの例：Customersテーブルに、年ごとの営業収益のデータを格納する列を新たに追加する
- メタデータ（カラム名）にデータ（2002年など）が混入している

CREATE TABLE Customers (
    customer_id      NUMBER(9) PRIMARY KEY,
    contact_info      VARCHAR(255),
    business_type   VARCHAR(200),
    revenue              NUMBER(9, 2)
);
ALTER TABLE Customers ADD (revenue2002 NUMBER(9, 2));
ALTER TABLE Customers ADD (revenue2003 NUMBER(9, 2));
ALTER TABLE Customers ADD (revenue2004 NUMBER(9, 2));

クエリの実行速度を劣化させずに、データが増加し続けるテーブルに対応できるようデータベースの構造を設計する方法
アンチパターン
- 行数の多いテーブルを複数のテーブルに分割する
- 列を複数列に分割する
アンチパターンを用いてもよい場合
- 過去データを最新データから分離するようなアーカイブが目的のとき
解決策1：水平パーティショニング（シャーディング）
- テーブルを行で分割する
- 行を分割するルールを定めて論理テーブルを定義する。テーブルは物理的には分割されているが、あたかも1つのテーブルを扱うようにSQLを実行できる。
- パーティショニングはSQL標準ではないが、ほぼすべてのDBでパーティショニングがサポートされている。

MYSQL5.1のパーティション機能
CREATE TABLE Bugs (
    bug_id              SERIAL PRIMARY KEY,
    ...
    date_reported DATE
) PARTITION BY HASH ( YEAR(date_reported) )
    PARTITIONS 4;

解決策2：垂直パーティショニング
- テーブルを列で分割する
- BLOB列とTEXT列は可変。多くのDBでは、自動的にこれらのデータ型を持つ列を他の列と分けて格納している。このため、テーブルのBLOB列やTEXT列を参照しないクエリを実行する場合は他の列に効率的にアクセスできる。

CREATE TABLE ProductInstallers (
    product_id         BIGINT UNSIGNED PRIMARY KEY,
    installer_image  BLOB, -- 巨大なバイナリが格納される列
    FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

解決策3：従属テーブルの導入

CREATE TABLE ProjectHistory (
    project_id  BIGINT,
    year            SMALLINT,
    bugs_fixed INT,
    PRIMARY KEY (project_id, year),
    FOREIGN KEY (project_id) REFERENCES Projects(project_id)
);

yoshino

9章ラウンディングエラー（丸め誤差）

SQLのFLOATデータ型は他のプログラミング言語のfloat型と同じようにIEEE754標準に従って実数を2進数形式でエンコードする。

浮動小数点数の規格を読むためにはinstitutional membershipが必要

FLOAT型の列にあるすべての値は丸められている可能性を想定しなければならない。
- 10進数の59.95は、2進数形式で格納できる近似値にすると59.950000752939になる。
浮動小数点数の誤差の影響は和ではなく積を計算する場合にさらに大きくなる。
FLOAT型は科学技術計算を行うアプリケーションには適している。
正確な10進数値が必要な場合（＝金額の計算など）にはNUMERIC型またはDECIMAL型を用いる。
- 有理数を丸めることなく格納できる
- NUMERIC型とDECIMAL型は同じ
- NUMERIC(9,2)
  - 精度9、スケール2
  - 格納できる値：123456789、1234567.89
  - 格納できない値：1234567890、123456.789

yoshino

10章サーティワンフレーバー（31のフレーバー）

列に入るデータをCHECK制約で特定の値に限定する
- MySQLではENUMをサポートしている

CREATE TABLE Bugs (
    ...
    status VARCHAR(20)
        CHECK (status IN ('NEW', 'IN PROGRESS', 'FIXED')),
);

status列に現在許可されている列挙値を取得したい場合、メタデータ定義を取得する必要がある
status列に新しい有効値を追加したい場合、新たな値セットで列を再定義するしか方法はない

ALTER TABLE Bugs MODIFY COLUMN status
    ENUM('NEW', 'IN PROGRESS', 'FIXED', 'DUPLICATE');

ETL（抽出Extract、変換Transform、ロードLoad）
値の変更の可能性があるならENUM型やCHECK制約を使うべきではない
解決策：限定する値をデータで指定する

CREATE TABLE BugStatus (
    status   VARCHAR(20) PRIMARY KEY
);

INSERT INTO BugStatus (status) VALUES ('NEW'), ('IN PROGRESS'), ('FIXED');

CREATE TABLE Bugs (
    ...
    status    VARCHAR(20),
    FOREIGN KEY (status) REFERENCES BugStatus(status) ON UPDATE CASCADE
);

yoshino

11章ファントムファイル（幻のファイル）

画像などの大容量メディアファイルを格納する方法
- ファイルは外部のファイルシステムに格納し、データベースにはファイルパスを格納する。
  - データベースであるファイルパスのある行を削除しても、そのパスの指定先のファイルは自動的に削除されない
  - ロールバック時に問題が起こる。アプリケーション側で「行をデータベースから削除し、対応するファイルを削除する」という操作を行い、ロールバックを行ったとき、データベースは元に戻るが、ファイルは元に戻らない。
  - SQLではファイルパスの検証ができない。
必要に応じて、データベースにファイルのバイナリデータをBLOB型で格納しよう。

第5章 EAV

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

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

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

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

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

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

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

9章 ラウンディングエラー（丸め誤差）

10章 サーティワンフレーバー（31のフレーバー）

11章 ファントムファイル（幻のファイル）

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

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

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

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

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

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

9章ラウンディングエラー（丸め誤差）

10章サーティワンフレーバー（31のフレーバー）

11章ファントムファイル（幻のファイル）