DB 設計の進め方

テーブルをいきなり CREATE するのではなく、業務を理解し、概念・論理・物理の3段階で設計を進めるのが基本です。 このページでは、受注・顧客・商品ドメインを題材に、設計の全工程を実践的に解説します。

設計フロー全体像

DB 設計は、大きく3段階に分かれます。各段階で扱う対象と成果物が異なります。

各段階で問うべき質問

• 概念設計: 「業務で管理すべき"もの"は何か？ それらはどう関係しているか？」
• 論理設計: 「主キーは何か？ 正規化されているか？ データ型は適切か？」
• 物理設計: 「どの列にインデックスが要るか？ 想定データ量で性能は足りるか？」

エンティティ抽出の実践

概念設計の最初のステップは、業務要件からエンティティ（管理対象）を見つけ出すことです。 基本的な手法は「業務記述の名詞を拾う」ことです。

業務要件の例文

名詞の抽出と分類

エンティティの種類

関連と多重度（カーディナリティ）

エンティティ同士がどのように結びつくかを「関連」、その数量関係を「多重度（カーディナリティ）」と呼びます。 多重度には大きく3つのパターンがあります。

1:1（1対1）

一方のエンティティ1件に対して、もう一方も必ず1件だけ対応するケースです。

1:N（1対多）

最も頻出するパターンです。「親」側の1件に対して、「子」側が複数件対応します。

実装では、子テーブルに親の主キーを外部キー (FK) として持たせます。

-- 1:N の実装例
CREATE TABLE customers (
    customer_id  INT          PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    order_id     INT  PRIMARY KEY,
    order_date   DATE NOT NULL,
    customer_id  INT  NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

M:N（多対多）

両方のエンティティが、互いに複数件と対応するケースです。 RDB では M:N を直接表現できないため、中間テーブル（交差テーブル）を挟んで 1:N + N:1 に分解します。

M:N を中間テーブルで解決する ER 図

-- M:N を中間テーブルで解決する DDL
CREATE TABLE products (
    product_id   INT          PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    unit_price   NUMERIC(10,2) NOT NULL
);

CREATE TABLE orders (
    order_id    INT  PRIMARY KEY,
    order_date  DATE NOT NULL,
    customer_id INT  NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_lines (
    order_id    INT NOT NULL,
    line_number INT NOT NULL,
    product_id  INT NOT NULL,
    quantity    INT NOT NULL CHECK (quantity > 0),
    PRIMARY KEY (order_id, line_number),
    FOREIGN KEY (order_id)   REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

属性の定義とデータ型選択

論理設計の段階で、各属性に適切なデータ型を選びます。 型の選択は、格納効率・検索性能・データ整合性に直結します。

VARCHAR vs TEXT

INT vs BIGINT

NUMERIC vs FLOAT

DATE vs TIMESTAMP

命名規約

テーブル名・列名の命名は、チーム全体の可読性と保守性に影響します。 プロジェクト初期に規約を定め、レビューで一貫性を保ちましょう。

よく使われる SQL 予約語（テーブル名・列名に使わないこと）

論理削除と物理削除

レコードを「削除」する方法は2つあります。それぞれ利点と欠点があり、業務要件に応じて使い分けます。

比較表

判断基準表

-- 論理削除カラムの追加例
ALTER TABLE products
    ADD COLUMN is_deleted  BOOLEAN   NOT NULL DEFAULT FALSE,
    ADD COLUMN deleted_at  TIMESTAMP NULL;

-- 有効データだけを返すビュー
CREATE VIEW active_products AS
SELECT * FROM products WHERE is_deleted = FALSE;

履歴管理の設計パターン

「過去のある時点でこの値はどうだったか」を知りたい場合、履歴管理の設計が必要です。 代表的な2つのパターンを紹介します。

パターン1: 有効開始日/終了日パターン

同じテーブル内に有効期間を持たせる方式です。SCD Type 2（Slowly Changing Dimension Type 2）とも呼ばれます。

CREATE TABLE product_prices (
    product_id      INT           NOT NULL,
    valid_from      DATE          NOT NULL,
    valid_to        DATE,  -- NULL = 現在有効
    unit_price      NUMERIC(10,2) NOT NULL,
    PRIMARY KEY (product_id, valid_from),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- 現在有効な単価を取得
SELECT product_id, unit_price
FROM   product_prices
WHERE  valid_to IS NULL;

-- 2025年6月1日時点の単価を取得
SELECT product_id, unit_price
FROM   product_prices
WHERE  valid_from <= '2025-06-01'
  AND  (valid_to IS NULL OR valid_to > '2025-06-01');

パターン2: 履歴テーブル分離パターン

現在値を保持するテーブルと、変更履歴を保持するテーブルを分ける方式です。

-- 現在値テーブル（常に最新のみ）
CREATE TABLE products (
    product_id   INT            PRIMARY KEY,
    product_name VARCHAR(100)   NOT NULL,
    unit_price   NUMERIC(10,2)  NOT NULL,
    updated_at   TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 履歴テーブル（変更のたびに INSERT）
CREATE TABLE products_history (
    history_id   INT            PRIMARY KEY,
    product_id   INT            NOT NULL,
    product_name VARCHAR(100)   NOT NULL,
    unit_price   NUMERIC(10,2)  NOT NULL,
    changed_at   TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    changed_by   VARCHAR(50),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- 現在値の取得は単純な SELECT
SELECT * FROM products WHERE product_id = 1001;

-- 変更履歴の確認
SELECT * FROM products_history
WHERE  product_id = 1001
ORDER BY changed_at DESC;

設計の進め方まとめ

以下のチェックリストを使い、設計の抜け漏れを防ぎましょう。

概念設計チェックポイント

• 業務要件の文章から、エンティティ（名詞）を洗い出したか
• 各エンティティがリソース系かイベント系かを分類したか
• エンティティ間の関連と多重度（1:1, 1:N, M:N）を定義したか
• M:N は中間テーブルで解消したか
• 概念 ER 図を描き、業務担当者と合意したか

論理設計チェックポイント

• 全テーブルに適切な主キーがあるか
• 外部キーで関連を明示しているか
• 第3正規形（3NF）まで正規化されているか（正規化ページ参照）
• データ型の選択は適切か（金額に FLOAT を使っていないか、など）
• NOT NULL 制約を適切に設定しているか
• 命名規約に沿っているか（スネークケース、英語名、予約語回避）
• 導出値をテーブルに持つ場合、整合性の維持方法を明確にしたか

物理設計チェックポイント

• WHERE 句や JOIN でよく使う列にインデックスを設計したか
• 想定レコード数に対してデータ型のサイズは十分か（INT vs BIGINT）
• 論理削除と物理削除の方針を決めたか
• 履歴管理が必要なテーブルの設計パターンを選択したか
• 文字コード（UTF-8 推奨）と照合順序を決めたか
• バックアップ・リストアの計画があるか