メインコンテンツまでスキップ

第17章 実践事例 — 商品配送管理とタスク管理の総合演習

この章で学ぶこと​

  • 業務要件 (商品配送管理) からエンティティを抽出し、E-R 図を起こし、テーブル定義 → 想定クエリまで一気通貫で設計できる
  • スーパータイプ / サブタイプ (LOCATION → FACTORY / STORE) の実装ができる
  • タスク管理システムを RBAC・自己参照 (タスクの依存関係) を含めて設計できる
前提知識

第 9 章 設計の流れ〜第 16 章 OAuth/OIDC までの内容を踏まえます。本章は本ガイドの集大成として位置づけ、複数章で学んだ概念を統合します。

題材 1: 商品配送管理システム​

要件​

A 社は 3 つの生産工場で全商品を生産し、約 70 店舗に配送しています。

  • 各店舗は決まった工場から商品を仕入れる
  • 配送は昼食前 / 夕食前の 2 回 / 日
  • 店舗が発注 → 工場が生産 → 工場から店舗へ配送 → 店舗が受領、の業務フロー
  • 工場ごとに配送ルートが定義され、ルート上の店舗を順番に回る

Step 1: エンティティの抽出​

ポイント: 「工場」と「店舗」はどちらも「拠点」という共通属性 (住所・電話) を持つため、スーパータイプ locations + サブタイプ factories / stores で設計します (第 12 章 E-R 図 のスーパータイプ参照)。

Step 2: E-R 図​

Step 3: テーブル定義​

商品配送管理 (PostgreSQL)
-- スーパータイプ: 拠点
CREATE TABLE locations (
    code           VARCHAR(10) PRIMARY KEY,
    name           VARCHAR(100) NOT NULL,
    address        VARCHAR(200) NOT NULL,
    phone          VARCHAR(20),
    location_type  VARCHAR(20) NOT NULL CHECK (location_type IN ('factory', 'store'))
);

-- サブタイプ: 工場
CREATE TABLE factories (
    location_code         VARCHAR(10) PRIMARY KEY REFERENCES locations(code),
    production_capacity   INTEGER NOT NULL,
    operation_start_date  DATE NOT NULL
);

-- 配送ルート
CREATE TABLE delivery_routes (
    route_number     VARCHAR(10) PRIMARY KEY,
    name             VARCHAR(50) NOT NULL,
    factory_code     VARCHAR(10) NOT NULL REFERENCES factories(location_code),
    estimated_min    INTEGER,
    is_active        BOOLEAN NOT NULL DEFAULT TRUE
);

-- サブタイプ: 店舗
CREATE TABLE stores (
    location_code        VARCHAR(10) PRIMARY KEY REFERENCES locations(code),
    route_number         VARCHAR(10) NOT NULL REFERENCES delivery_routes(route_number),
    delivery_order       INTEGER NOT NULL,
    delivery_time_slot   VARCHAR(20) NOT NULL CHECK (delivery_time_slot IN ('morning', 'evening'))
);

CREATE INDEX idx_stores_route_order ON stores(route_number, delivery_order);

-- 商品マスタ
CREATE TABLE products (
    code               VARCHAR(20) PRIMARY KEY,
    name               VARCHAR(100) NOT NULL,
    unit_price         NUMERIC(10, 2) NOT NULL CHECK (unit_price > 0),
    lot_size           INTEGER NOT NULL DEFAULT 1 CHECK (lot_size > 0),
    shelf_life_days    INTEGER NOT NULL CHECK (shelf_life_days > 0),
    temperature_range  VARCHAR(50),
    is_active          BOOLEAN NOT NULL DEFAULT TRUE
);

-- 発注
CREATE TABLE orders (
    order_number          VARCHAR(20) PRIMARY KEY,
    store_code            VARCHAR(10) NOT NULL REFERENCES stores(location_code),
    order_datetime        TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    delivery_scheduled    TIMESTAMPTZ NOT NULL,
    status                VARCHAR(20) NOT NULL DEFAULT 'pending'
                          CHECK (status IN ('pending', 'confirmed', 'in_production', 'shipped', 'delivered'))
);

CREATE INDEX idx_orders_store_date  ON orders(store_code, order_datetime);
CREATE INDEX idx_orders_scheduled   ON orders(delivery_scheduled);

CREATE TABLE order_items (
    order_number  VARCHAR(20) NOT NULL REFERENCES orders(order_number) ON DELETE CASCADE,
    product_code  VARCHAR(20) NOT NULL REFERENCES products(code) ON DELETE RESTRICT,
    quantity      INTEGER NOT NULL CHECK (quantity > 0),
    unit_price    NUMERIC(10, 2) NOT NULL,                -- スナップショット
    PRIMARY KEY (order_number, product_code)
);

-- 生産
CREATE TABLE productions (
    production_number       VARCHAR(20) PRIMARY KEY,
    factory_code            VARCHAR(10) NOT NULL REFERENCES factories(location_code),
    production_date         DATE NOT NULL,
    shift                   VARCHAR(20) NOT NULL CHECK (shift IN ('morning', 'evening')),
    scheduled_completion    TIMESTAMPTZ NOT NULL,
    actual_completion       TIMESTAMPTZ,
    status                  VARCHAR(20) NOT NULL DEFAULT 'planned'
                            CHECK (status IN ('planned', 'in_progress', 'completed'))
);

CREATE TABLE production_items (
    production_number  VARCHAR(20) NOT NULL REFERENCES productions(production_number) ON DELETE CASCADE,
    product_code       VARCHAR(20) NOT NULL REFERENCES products(code),
    planned_quantity   INTEGER NOT NULL CHECK (planned_quantity > 0),
    actual_quantity    INTEGER CHECK (actual_quantity IS NULL OR actual_quantity >= 0),
    lot_number         VARCHAR(50),
    PRIMARY KEY (production_number, product_code)
);

-- 配送
CREATE TABLE deliveries (
    delivery_number   VARCHAR(20) PRIMARY KEY,
    route_number      VARCHAR(10) NOT NULL REFERENCES delivery_routes(route_number),
    delivery_date     DATE NOT NULL,
    vehicle_number    VARCHAR(20),
    driver_name       VARCHAR(50),
    departure_time    TIMESTAMPTZ,
    return_time       TIMESTAMPTZ,
    status            VARCHAR(20) NOT NULL DEFAULT 'preparing'
                      CHECK (status IN ('preparing', 'in_transit', 'completed'))
);

CREATE TABLE delivery_items (
    delivery_number      VARCHAR(20) NOT NULL REFERENCES deliveries(delivery_number) ON DELETE CASCADE,
    store_code           VARCHAR(10) NOT NULL REFERENCES stores(location_code),
    product_code         VARCHAR(20) NOT NULL REFERENCES products(code),
    quantity             INTEGER NOT NULL CHECK (quantity > 0),
    delivered_quantity   INTEGER CHECK (delivered_quantity IS NULL OR delivered_quantity >= 0),
    delivery_time        TIMESTAMPTZ,
    receipt_status       VARCHAR(20) NOT NULL DEFAULT 'pending'
                         CHECK (receipt_status IN ('pending', 'delivered', 'rejected')),
    PRIMARY KEY (delivery_number, store_code, product_code)
);

Step 4: 想定クエリ​

生産指示の集計 (発注 → 工場別商品別の必要ロット数)
SELECT
    o.delivery_scheduled::date              AS production_date,
    dr.factory_code,
    oi.product_code,
    p.name                                  AS product_name,
    p.lot_size,
    SUM(oi.quantity)                        AS total_quantity,
    CEILING(SUM(oi.quantity)::numeric / p.lot_size) AS required_lots
FROM   orders o
JOIN   order_items oi      ON o.order_number = oi.order_number
JOIN   stores s            ON o.store_code = s.location_code
JOIN   delivery_routes dr  ON s.route_number = dr.route_number
JOIN   products p          ON oi.product_code = p.code
WHERE  o.status IN ('pending', 'confirmed')
GROUP BY o.delivery_scheduled::date, dr.factory_code, oi.product_code, p.name, p.lot_size
ORDER BY production_date, dr.factory_code, oi.product_code;

このクエリは「明日昼便分の発注を集計して、工場 1 で何の商品を何ロット作るべきか」を出します。発注テーブル + 配送ルート + 商品マスタを JOIN するだけで、業務上の「生産指示」が導けます。

スーパータイプ / サブタイプ設計の利点

本題材で locations というスーパータイプを置いたことで、「全拠点のリスト」のような横断クエリ (SELECT * FROM locations) が簡単に書けます。一方で、「工場特有の属性」(生産能力) は factories テーブルだけにあり、データの肥大化を防げます。

題材 2: タスク管理システム​

要件​

  • ユーザーは登録・ログインできる
  • プロジェクトを作成し、メンバーを招待できる
  • プロジェクト内でタスクを作成・編集・割当できる
  • タスクには優先度・期限・ステータスがある
  • タスクにコメントを付けられる
  • タスクには「先行タスクが終わってからでないと着手できない」依存関係を表現できる

Step 1: E-R 図​

Step 2: テーブル定義​

タスク管理 (PostgreSQL)
CREATE TABLE users (
    id            SERIAL PRIMARY KEY,
    email         VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    display_name  VARCHAR(100) NOT NULL,
    is_active     BOOLEAN NOT NULL DEFAULT TRUE,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

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

CREATE TABLE project_members (
    project_id  INTEGER NOT NULL REFERENCES projects(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 ('owner', 'admin', 'member', 'viewer')),
    joined_at   TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (project_id, user_id)
);

CREATE TABLE tasks (
    id           SERIAL PRIMARY KEY,
    project_id   INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    title        VARCHAR(200) NOT NULL,
    description  TEXT,
    created_by   INTEGER NOT NULL REFERENCES users(id),
    assigned_to  INTEGER REFERENCES users(id),
    status       VARCHAR(20) NOT NULL DEFAULT 'todo'
                 CHECK (status IN ('todo', 'in_progress', 'review', 'done', 'cancelled')),
    priority     INTEGER NOT NULL DEFAULT 3 CHECK (priority BETWEEN 1 AND 5),
    due_date     DATE,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_tasks_project_status   ON tasks(project_id, status);
CREATE INDEX idx_tasks_assigned         ON tasks(assigned_to, status);
CREATE INDEX idx_tasks_due              ON tasks(due_date) WHERE status NOT IN ('done', 'cancelled');

-- タスク依存関係 (自己参照多対多)
CREATE TABLE task_dependencies (
    dependent_task_id     INTEGER NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    prerequisite_task_id  INTEGER NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    created_at            TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (dependent_task_id, prerequisite_task_id),
    CHECK (dependent_task_id <> prerequisite_task_id)        -- 自分への依存禁止
);

CREATE TABLE comments (
    id           SERIAL PRIMARY KEY,
    task_id      INTEGER NOT NULL REFERENCES tasks(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_task ON comments(task_id, created_at);

Step 3: 想定クエリ​

着手可能なタスクの一覧 (先行タスクが全て完了しているもの)
SELECT t.*
FROM   tasks t
WHERE  t.assigned_to = $1
  AND  t.status = 'todo'
  AND  NOT EXISTS (
        -- 先行タスクのうち未完了のものが 1 つでもあれば除外
        SELECT 1
        FROM   task_dependencies td
        JOIN   tasks pre ON pre.id = td.prerequisite_task_id
        WHERE  td.dependent_task_id = t.id
          AND  pre.status NOT IN ('done', 'cancelled')
      )
ORDER BY t.priority, t.due_date NULLS LAST;

このクエリは、ログイン中ユーザー ($1) に割り当てられた todo 状態のタスクのうち、「先行タスクが全て終わっている」ものだけを優先度・期限順に取り出します。

タスク依存の循環検出

task_dependencies をユーザーが自由に作れる場合、「A → B → C → A」のような循環依存が登録される可能性があります。データベースのレベルで循環を防ぐのは複雑なので、アプリケーション層で挿入前に検出するのが一般的です (DFS / トポロジカルソートで検出)。

演習​

演習: 図書館管理システムの総合設計

本ガイドで学んだ手法を使い、次の要件から E-R 図 + テーブル定義 + 想定クエリ (1 つ) を作成してください。

要件:

  • 会員は本を借りられる (貸出)
  • 1 冊の本に複数の著者がいる
  • 本にはカテゴリがある (階層構造)
  • 貸出には期限と延滞料金がある
  • 会員は本を予約できる (貸出中の場合)
  • 会員のうち、職員 (司書) はシステム管理権限を持つ (RBAC)

想定クエリ: 「現在貸出中の本のうち、返却期限を過ぎているものを会員名・本タイトル・延滞日数で一覧」

解答例

E-R 図 (簡略):

テーブル定義 (主要部分):

CREATE TABLE members (
    id              SERIAL PRIMARY KEY,
    member_number   VARCHAR(20) UNIQUE NOT NULL,
    name            VARCHAR(100) NOT NULL,
    email           VARCHAR(255) UNIQUE NOT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE categories (
    id              SERIAL PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,
    parent_id       INTEGER REFERENCES categories(id)
);

CREATE TABLE books (
    id                 SERIAL PRIMARY KEY,
    isbn               VARCHAR(13) UNIQUE,
    title              VARCHAR(500) NOT NULL,
    publisher          VARCHAR(200),
    category_id        INTEGER NOT NULL REFERENCES categories(id),
    total_copies       INTEGER NOT NULL CHECK (total_copies >= 0),
    available_copies   INTEGER NOT NULL CHECK (available_copies >= 0)
);

CREATE TABLE authors (
    id    SERIAL PRIMARY KEY,
    name  VARCHAR(200) NOT NULL
);

CREATE TABLE book_authors (
    book_id      INTEGER NOT NULL REFERENCES books(id) ON DELETE CASCADE,
    author_id    INTEGER NOT NULL REFERENCES authors(id) ON DELETE RESTRICT,
    author_order INTEGER NOT NULL DEFAULT 1,
    PRIMARY KEY (book_id, author_id)
);

CREATE TABLE loans (
    id           SERIAL PRIMARY KEY,
    member_id    INTEGER NOT NULL REFERENCES members(id) ON DELETE RESTRICT,
    book_id      INTEGER NOT NULL REFERENCES books(id) ON DELETE RESTRICT,
    loan_date    DATE NOT NULL DEFAULT CURRENT_DATE,
    due_date     DATE NOT NULL,
    return_date  DATE,
    fine_amount  NUMERIC(10, 2) DEFAULT 0 CHECK (fine_amount >= 0)
);

CREATE INDEX idx_loans_member_unreturned ON loans(member_id) WHERE return_date IS NULL;
CREATE INDEX idx_loans_due_unreturned    ON loans(due_date)  WHERE return_date IS NULL;

CREATE TABLE reservations (
    id                 SERIAL PRIMARY KEY,
    member_id          INTEGER NOT NULL REFERENCES members(id) ON DELETE CASCADE,
    book_id            INTEGER NOT NULL REFERENCES books(id) ON DELETE CASCADE,
    reservation_date   TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status             VARCHAR(20) NOT NULL DEFAULT 'waiting'
                       CHECK (status IN ('waiting', 'ready', 'fulfilled', 'cancelled'))
);

-- RBAC
CREATE TABLE roles (
    id    SERIAL PRIMARY KEY,
    name  VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE member_roles (
    member_id   INTEGER NOT NULL REFERENCES members(id) ON DELETE CASCADE,
    role_id     INTEGER NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    granted_at  TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (member_id, role_id)
);

想定クエリ:

延滞中の貸出一覧
SELECT
    m.name                                AS member_name,
    b.title                               AS book_title,
    l.loan_date,
    l.due_date,
    CURRENT_DATE - l.due_date             AS overdue_days
FROM   loans l
JOIN   members m ON m.id = l.member_id
JOIN   books b   ON b.id = l.book_id
WHERE  l.return_date IS NULL
  AND  l.due_date < CURRENT_DATE
ORDER BY overdue_days DESC;

ありがちな間違い:

  1. books.available_copies をトリガーで自動更新: 第 14 章で扱ったロック競合リスク。アプリ層で loans への INSERT / UPDATE と同じトランザクション内で UPDATE books SET available_copies = available_copies - 1 を実行
  2. book_authors を主キー (book_id, author_id) でなく単独 ID にする: 同じ著者を同じ本に 2 回登録できてしまう。複合主キーで重複防止
  3. 延滞料金を members.fine_balance で持つ: 個別貸出から計算で導けるため、loans.fine_amount の SUM で集計

別解: loans テーブルの主キーを複合 (member_id, book_id, loan_date) にする選択もあるが、サロゲートキー (id SERIAL) の方が外部キー参照しやすいので推奨。

まとめ​

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

  • 要件 → エンティティ抽出 → E-R 図 → テーブル定義 → 想定クエリ の一気通貫を 2 題で実践
  • 商品配送管理: スーパータイプ / サブタイプ (locations → factories / stores) の実装例
  • タスク管理: 自己参照多対多 (task_dependencies) と RBAC の組み合わせ
  • 集計クエリは正規化されたテーブルから JOIN で導く のが原則 (集計カラムを安易に持たない)
  • 部分インデックス (WHERE return_date IS NULL 等) は不要な行を除外して高速化

次の最終章では、第 11 章で学んだ正規化の理解を深めるため、追加の演習問題を解いていきます。

次の章

第 18 章 データベース正規化演習問題解説