リレーショナルデータモデリング

エンティティ・リレーションシップモデルは、テーブルを選択する前にエンティティ型、その属性、およびリレーションシップを特定します。その後、リレーショナルスキーマは各事実を定義された粒度で一度だけ表現します。たとえば、order_line は1つの注文に含まれる1つの商品を表し、order は注文ヘッダーを表します。

エンティティ、キー、およびリレーションシップ #

すべてのテーブルには、行を一意に識別する候補キーが必要です。主キーには、安定していて、NULLにならず、結合に適したものを選択します。代理キーを使用しても、アカウントの外部識別子など、自然な業務上の識別子に対する一意性制約は不要になりません。

リレーションシップのカーディナリティは、次のようにマッピングします。

1. 1対1のリレーションシップでは、任意側に UNIQUE 外部キーを配置するか、ライフサイクルとアクセス制御が一致する場合はテーブルを統合します。
2. 1対多では、多側のテーブルに外部キーを配置します。
3. 多対多では、両方のエンティティへの外部キーと、リレーションシップに属する属性を含む関連テーブルを作成します。

NOT NULL は必須参加を表します。NULL許容の外部キーは、その行にとってリレーションシップが任意であることを意味します。外部キー制約は孤立した参照を防ぎますが、ON DELETE の動作は業務ルールを反映する必要があります。つまり、制限、カスケード、NULL設定、または明示的なワークフローでの削除処理のいずれかです。

正規化 #

正規化は関数従属性から始まります。X の値が Y の1つの値を決定する場合、X -> Y と記述します。第一正規形では、各列に宣言されたドメインからの1つの値だけを持たせ、繰り返しグループをなくします。第二正規形では、複合候補キーの一部だけに依存する非キー属性を取り除きます。第三正規形では、非キー属性が別の非キー属性を決定する推移的従属性を取り除きます。これらの規則は、各事実に信頼できる唯一の格納場所を持たせるため、挿入、更新、および削除の異常を防ぎます。

読み取りコストを測定し、重複した値を正しく維持する方法を定義した後にのみ、非正規化を行います。トランザクション、トリガー、生成列、変更データキャプチャのコンシューマー、または再構築ジョブのどれが複製を維持するかを記録し、その後、陳腐化と突合を監視します。インデックス、サマリーテーブル、またはクエリの書き換えによって、業務データの制御不能なコピーを作成せずに読み取り経路を改善できる場合があります。

CREATE TABLE order_line (
  order_id BIGINT NOT NULL,
  line_number SMALLINT UNSIGNED NOT NULL,
  product_id BIGINT NOT NULL,
  quantity INT UNSIGNED NOT NULL,
  unit_price DECIMAL(12, 2) NOT NULL,
  PRIMARY KEY (order_id, line_number),
  CONSTRAINT fk_order_line_order
    FOREIGN KEY (order_id) REFERENCES orders (id),
  CONSTRAINT fk_order_line_product
    FOREIGN KEY (product_id) REFERENCES product (id)
) ENGINE = InnoDB;

複合主キーによって、テーブルの粒度が明示されます。つまり、1つの注文内には1つの行番号が一度だけ存在します。InnoDBでは、各外部キーの先頭列をカバーするインデックスが必要であり、自動的に作成される場合があります。冗長なインデックスを追加する前に、SHOW CREATE TABLE とクエリプランを確認してください。

参考資料 #

• InnoDB外部キー制約
• MySQLの制約