第13章 データベース設計の実践 — 要件から CREATE TABLE まで

この章で学ぶこと

• ボトムアップ (帳票から起こす) とトップダウン (要件から起こす) のアプローチを使い分けられる
• レストラン注文伝票・社内 SNS の 2 題で、要件 → E-R 図 → CREATE TABLE までを一気通貫で実践できる
• 設計の落とし穴 (過度な正規化 / 不適切な主キー / 削除戦略 / 論理削除 vs GDPR) を回避できる

前提知識

第 11 章 正規化 と 第 12 章 E-R 図 を理解していること。

2 つの設計アプローチ

アプローチ	起点	向く場面
ボトムアップ	既存の帳票 / 画面 / Excel	既存業務の DB 化、リプレース
トップダウン	要件定義書・機能リスト	新規システム、抽象的な要件から起こす

実務では両方を混在して使うのが普通です。「要件から大枠を起こしつつ、既存帳票で漏れを確認する」ような往復が起きます。

ボトムアップ実践: レストラン注文伝票

元データ — 紙の注文伝票

================================
        レストラン花まる
         注文伝票
================================
伝票番号: 2026-0001
日時: 2026/01/15 19:30
テーブル: 5
担当: 山田

--------------------------------
品名            数量  単価   金額
--------------------------------
生ビール         2    600   1,200
枝豆             1    400     400
唐揚げ           2    700   1,400
ラーメン         1    900     900
チャーハン       1    800     800
--------------------------------
小計                       4,700
消費税(10%)                 470
--------------------------------
合計                       5,170
================================

Step 1: データ項目の抽出

伝票から「データになりそうな項目」を全部書き出します。

Step 2: エンティティへの整理

「これは独立したモノか?」「他から参照されるか?」を基準にエンティティを抽出します。

項目	エンティティ候補
伝票番号・日時・テーブル・担当	注文 (orders)
品名・単価・カテゴリ	商品 (products)
数量・金額	注文明細 (order_items)
テーブル番号・席数	テーブル (tables)
担当者名	スタッフ (staff)
小計・消費税・合計	計算で導けるためカラム化しない (集計クエリで都度算出)

Step 3: E-R 図を起こす

Step 4: CREATE TABLE 文に展開

レストラン注文システム (PostgreSQL)

-- スタッフ
CREATE TABLE staff (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(50) NOT NULL,
    hire_date   DATE,
    is_active   BOOLEAN NOT NULL DEFAULT TRUE
);

-- テーブル (席)
CREATE TABLE restaurant_tables (
    id            SERIAL PRIMARY KEY,
    table_number  INTEGER UNIQUE NOT NULL,
    seat_count    INTEGER NOT NULL CHECK (seat_count > 0),
    is_active     BOOLEAN NOT NULL DEFAULT TRUE
);

-- 商品マスタ
CREATE TABLE products (
    id            SERIAL PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    unit_price    NUMERIC(10, 2) NOT NULL CHECK (unit_price > 0),
    category      VARCHAR(50),
    is_available  BOOLEAN NOT NULL DEFAULT TRUE
);

-- 注文ヘッダー
CREATE TABLE orders (
    id              SERIAL PRIMARY KEY,
    order_number    VARCHAR(20) UNIQUE NOT NULL,
    table_id        INTEGER NOT NULL REFERENCES restaurant_tables(id),
    staff_id        INTEGER NOT NULL REFERENCES staff(id),
    order_datetime  TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 注文明細
CREATE TABLE order_items (
    order_id    INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id  INTEGER NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    quantity    INTEGER NOT NULL CHECK (quantity > 0),
    unit_price  NUMERIC(10, 2) NOT NULL,  -- 注文時の単価をスナップショット
    PRIMARY KEY (order_id, product_id)
);

CREATE INDEX idx_orders_datetime  ON orders(order_datetime);
CREATE INDEX idx_orders_staff     ON orders(staff_id);

ポイント:

• 小計・消費税・合計は計算で導けるため保存しない: SELECT SUM(quantity * unit_price) で算出
• order_items.unit_price はスナップショット (商品マスタの将来の値上げから注文を守る)
• order_items.order_id に ON DELETE CASCADE (注文を消すなら明細も消える)
• order_items.product_id に ON DELETE RESTRICT (商品を消そうとしても注文で参照中なら拒否)

トップダウン実践: 社内 SNS

要件

• 社員 (ユーザー) は文章を投稿できる
• 投稿は全社公開 / グループ内公開 / 自分のみのいずれかの公開範囲
• 投稿にコメント・いいねが付く
• 社員は複数のグループに所属できる、グループには複数の社員が所属する
• いいねは「誰がいつどの投稿に」した記録

機能リストからエンティティを起こす

エンティティ候補は次の通り: User / Post / Comment / Like / Group / GroupMember。

E-R 図

CREATE TABLE

社内 SNS のテーブル定義 (PostgreSQL)

CREATE TABLE users (
    id                SERIAL PRIMARY KEY,
    employee_number   VARCHAR(20) UNIQUE NOT NULL,
    email             VARCHAR(255) UNIQUE NOT NULL,
    password_hash     VARCHAR(255) NOT NULL,
    display_name      VARCHAR(100) NOT NULL,
    department        VARCHAR(100),
    profile_image_url VARCHAR(500),
    is_active         BOOLEAN NOT NULL DEFAULT TRUE,
    created_at        TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE groups (
    id           SERIAL PRIMARY KEY,
    name         VARCHAR(100) NOT NULL,
    description  TEXT,
    is_public    BOOLEAN NOT NULL DEFAULT TRUE,
    created_by   INTEGER NOT NULL REFERENCES users(id),
    created_at   TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE group_members (
    group_id   INTEGER NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    user_id    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role       VARCHAR(20) NOT NULL DEFAULT 'member'
               CHECK (role IN ('admin', 'member')),
    joined_at  TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (group_id, user_id)
);

CREATE TABLE posts (
    id          BIGSERIAL PRIMARY KEY,
    user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    group_id    INTEGER REFERENCES groups(id) ON DELETE CASCADE,
    content     TEXT NOT NULL,
    visibility  VARCHAR(20) NOT NULL DEFAULT 'public'
                CHECK (visibility IN ('public', 'group', 'private')),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_posts_user_created   ON posts(user_id, created_at DESC);
CREATE INDEX idx_posts_group_created  ON posts(group_id, created_at DESC);

CREATE TABLE comments (
    id          BIGSERIAL PRIMARY KEY,
    post_id     BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    content     TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_comments_post  ON comments(post_id, created_at);

CREATE TABLE likes (
    post_id     BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (post_id, user_id)
);

ポイント:

• likes の主キーは (post_id, user_id) の複合キー (1 ユーザーは 1 投稿に 1 いいねまで)
• posts.user_id は ON DELETE RESTRICT (投稿者削除時は別途処理)
• comments.post_id は ON DELETE CASCADE (投稿削除でコメントも消える)
• idx_posts_user_created (複合インデックス) でユーザータイムライン取得を高速化

設計の落とし穴

落とし穴 1: 過度な正規化

「正規化の理論を厳密にやろうとして、不必要にテーブルを分けてしまう」例です。

過度な正規化 (避けるべき)

-- 名前を別テーブルに分けている (不要な分割)
CREATE TABLE person_names (
    id          SERIAL PRIMARY KEY,
    first_name  VARCHAR(50) NOT NULL,
    last_name   VARCHAR(50) NOT NULL
);

CREATE TABLE persons (
    id        SERIAL PRIMARY KEY,
    name_id   INTEGER NOT NULL REFERENCES person_names(id)
);

適切な設計

CREATE TABLE persons (
    id          SERIAL PRIMARY KEY,
    first_name  VARCHAR(50) NOT NULL,
    last_name   VARCHAR(50) NOT NULL
);

「名前」は「人」の属性であり、独立したエンティティではありません。JOIN が増えて読み書き両方が遅くなるだけです。

正規化の判断軸

「他のテーブルから独立して参照される/管理されるものをエンティティに昇格させる」のが原則です。person_names は誰からも独立して参照されないため、persons のカラムで十分です。

落とし穴 2: 不適切な主キー選択

メールアドレスを主キーにする (避けるべき)

CREATE TABLE users_bad (
    email VARCHAR(255) PRIMARY KEY,  -- メールは変更される可能性
    name  VARCHAR(100)
);

メール変更時に全外部キー参照を書き換える必要があり、参照整合性の保守が破綻します。代理キー (SERIAL) を主キーにして、メールは UNIQUE 制約で守る (第 10 章 キー概念 のハイブリッド設計) のが正解です。

落とし穴 3: 削除戦略の不在

軽率な CASCADE (避けるべき)

CREATE TABLE orders_dangerous (
    id            SERIAL PRIMARY KEY,
    customer_id   INTEGER REFERENCES customers(id) ON DELETE CASCADE  -- 顧客削除で注文消失
);

顧客を削除した瞬間に注文履歴も全消失するため、売上集計・税務報告ができなくなります。RESTRICT にして、顧客削除前に注文の扱いを明示的に決める設計が安全です。

落とし穴 4: 論理削除 vs 物理削除 (GDPR との衝突)

「データを残しておきたい」(過去履歴の参照) と「データを完全に消したい」(GDPR / 個人情報保護法の右削除権) は、しばしば衝突します。

戦略	説明	向くケース
物理削除	DELETE で行を完全削除	GDPR / 個人情報保護法への対応・古いログ削除
論理削除 (deleted_at)	行は残し、削除フラグだけ立てる	業務履歴・誤削除復元・参照整合性維持
アーカイブ	別テーブルに移してから本番から削除	長期保管が必要だがアクセスは稀

論理削除と GDPR を両立する例

-- 業務上の論理削除カラム
CREATE TABLE customers (
    id            SERIAL PRIMARY KEY,
    email         VARCHAR(255) UNIQUE,
    name          VARCHAR(100) NOT NULL,
    is_deleted    BOOLEAN NOT NULL DEFAULT FALSE,
    deleted_at    TIMESTAMPTZ,
    -- GDPR 削除リクエスト用の匿名化
    anonymized_at TIMESTAMPTZ
);

-- 論理削除: 業務的にはここで非表示にする
UPDATE customers SET is_deleted = TRUE, deleted_at = NOW() WHERE id = 1;

-- GDPR 削除リクエストへの対応: 個人情報を匿名化
UPDATE customers
SET    email = NULL,
       name = '削除済みユーザー',
       anonymized_at = NOW()
WHERE  id = 1;

GDPR / 個人情報保護法への対応

GDPR Article 17 (right to erasure) では、個人データを「物理削除 or 完全な匿名化」する必要があります。論理削除フラグ (is_deleted = TRUE) だけでは個人データが残り続けるため不十分なケースがあります。EU 圏のユーザーを扱う場合は、削除リクエスト時に個人を特定できる情報を NULL 化 or 匿名化し、業務に必要な統計用の情報だけ残す設計が必要です。日本の改正個人情報保護法も同様の方向で運用しています。

設計レビューのチェックリスト

設計を実装に渡す前に、次の項目をレビューします。

観点	確認内容
キー設計	すべてのテーブルに主キー、ハイブリッド戦略を適用
正規化	第 3 正規形まで進めたか、意図的な非正規化はコメントで明示
制約	NOT NULL / CHECK / FOREIGN KEY が漏れなく
性能	検索でよく使うカラムにインデックス、JOIN がすぎないか
運用	削除戦略・バックアップ・監視を考慮
命名規則	snake_case / 複数形 / 略語回避 で揃っている

ドメイン駆動設計 (DDD) への接続

ドメイン駆動設計を採用する場合

本ガイドの設計手順は「要件 → E-R 図 → テーブル定義」の流れで、テーブル設計を出発点としています。これは Jr が業務システムの DB 設計をゼロから起こす段階で必要な基礎です。

実装フェーズでドメイン駆動設計 (DDD) を採用する場合は、考え方が逆転します:ドメインモデル (業務ロジックを表すクラス) が先で、テーブルはモデルを永続化する手段になります。

詳しくは Laravel × DDD × クリーンアーキテクチャ 第14章 ドメインモデルとテーブル設計 を参照してください。本 DB ガイドの「テーブル設計の基礎」と Laravel ガイドの「ドメインモデルとマッピング」を、フェーズに応じて使い分けるイメージです。

演習

演習 1: 帳票から設計を起こす

次の請求書のサンプルから、エンティティを抽出し E-R 図 + CREATE TABLE 文を起こしてください。

======================================
            請求書
======================================
発行日: 2026-06-30
請求書番号: INV-2026-0042

請求先:
  株式会社 サンプル
  〒100-0001 東京都千代田区...
  担当: 山田 太郎

請求元:
  鈴木商事
  〒530-0001 大阪府大阪市...

--------------------------------------
品名          数量    単価     金額
--------------------------------------
PC 部品A       10   3,000   30,000
PC 部品B        5   1,500    7,500
作業費          1  10,000   10,000
--------------------------------------
小計                       47,500
消費税(10%)                 4,750
--------------------------------------
合計                       52,250
======================================

解答例

抽出エンティティは次の 5 つ: companies (取引先・自社共通) / contacts (担当者) / products / invoices / invoice_items。

E-R 図:

CREATE TABLE:

請求書システム

CREATE TABLE companies (
    id           SERIAL PRIMARY KEY,
    name         VARCHAR(200) NOT NULL,
    postal_code  VARCHAR(10),
    address      VARCHAR(500)
);

CREATE TABLE contacts (
    id          SERIAL PRIMARY KEY,
    company_id  INTEGER NOT NULL REFERENCES companies(id),
    name        VARCHAR(100) NOT NULL
);

CREATE TABLE products (
    id                  SERIAL PRIMARY KEY,
    name                VARCHAR(200) NOT NULL,
    default_unit_price  NUMERIC(10, 2) NOT NULL CHECK (default_unit_price >= 0)
);

CREATE TABLE invoices (
    id                     SERIAL PRIMARY KEY,
    invoice_number         VARCHAR(20) UNIQUE NOT NULL,
    issued_at              DATE NOT NULL,
    bill_to_company_id     INTEGER NOT NULL REFERENCES companies(id),
    bill_to_contact_id     INTEGER REFERENCES contacts(id),
    bill_from_company_id   INTEGER NOT NULL REFERENCES companies(id)
);

CREATE TABLE invoice_items (
    invoice_id   INTEGER NOT NULL REFERENCES invoices(id) ON DELETE CASCADE,
    line_number  INTEGER NOT NULL,
    product_id   INTEGER NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    quantity     INTEGER NOT NULL CHECK (quantity > 0),
    unit_price   NUMERIC(10, 2) NOT NULL,  -- 請求時の単価をスナップショット
    PRIMARY KEY (invoice_id, line_number)
);

ポイント:

• 「請求元・請求先」は両方 companies テーブル (自社も取引先も会社)
• 集計値 (小計 / 消費税 / 合計) は保存しない (SUM(quantity * unit_price) で算出)
• invoice_items.unit_price はスナップショット保存

ありがちな間違い:

1. 「請求元」「請求先」を別テーブルに分ける: 同じ「会社」という属性を持つので統合可能
2. 「作業費」のように商品でないものを products に入れていいか: 業務的に「サービス + 商品」を統一管理するなら OK、別物として扱うなら services テーブルに分離

演習 2: 削除戦略の判断

次のテーブルそれぞれについて、「物理削除 / 論理削除 / アーカイブ」のどれを採用すべきか、理由とともに答えてください。

1. ユーザー (顧客) テーブル
2. アクセスログテーブル (3 ヶ月分保持で十分)
3. 注文 (orders) テーブル
4. ECサイトのカート (cart_items) テーブル

解答例

テーブル	推奨戦略	理由
1. ユーザー	論理削除 + GDPR 対応の匿名化	業務的には残したい (注文履歴の参照整合性) / GDPR 削除リクエストには個人特定情報を NULL 化
2. アクセスログ	物理削除 (期限切れ) + アーカイブ	3 ヶ月分以外は性能を圧迫するので削除。法定保管期間があれば S3 などにアーカイブ
3. 注文	論理削除 + 長期保持	税務上 7 年保管必須 / キャンセル時は status カラムで管理、物理削除はしない
4. カート	物理削除 (古いものから)	「未確定の買い物候補」で、保存期限を過ぎたら自然消滅させてよい。性能を優先

補足:

• ユーザーの「論理削除」と「GDPR 匿名化」は両立できます。論理削除フラグで業務的に非表示にしつつ、GDPR リクエストには個人特定情報の物理削除/匿名化で応える
• 注文の論理削除は「キャンセル」というステータス変更で表現することが多い (status = 'cancelled')。物理削除すると過去レポートが壊れる
• アクセスログは「3 ヶ月で物理削除」のような自動化を仕込むのが一般的 (cron / pg_cron / partman 拡張など)

ありがちな間違い:

1. 全テーブルに deleted_at カラムを付ける: 用途で使い分けるべき。カートのような「短命データ」に論理削除は不要
2. 論理削除フラグで GDPR に対応する: フラグだけでは個人データが残り続けるため、EU 圏向けサービスでは不十分

まとめ

この章で学んだことを整理します。

• ボトムアップは帳票・画面から起こす、トップダウンは要件から起こす。実務では両方を併用
• 設計の流れ: 項目抽出 → エンティティ整理 → E-R 図 → 正規化 → CREATE TABLE
• 集計値 (小計・合計など) は保存せず、計算で導く (例外: 性能要求があれば選択的非正規化)
• スナップショット (注文時の単価など) はマスタの将来変更から履歴を守る
• 過度な正規化・不適切な主キー・軽率な CASCADE・GDPR 対応不在は典型的な落とし穴
• ドメイン駆動設計を実装フェーズで採用する場合はLaravel ガイド第 14 章へ

本ガイド第 2 部 (設計編) はここまでです。次の章からは応用編に入り、多対多・認証認可・OAuth など実務でよく出会うパターンを学びます。

次の章

第 14 章 多対多のテーブル設計