論理設計とは、データベース製品に依存しない形で表・列・キー・関連を決める工程で、物理設計とは、型・索引・パーティション・容量など製品に依存する事柄を決める工程です。 その前にある概念設計は、業務に出てくる「もの」と「関係」だけを決めます。

この記事では、架空の備品貸出管理を題材に、概念ER図 → 論理モデル → 物理DDL を同じ題材で最後まで通します。物理DDLはPostgreSQLとMySQLの両方で実行し、製品によって変わる部分を実行結果とエラー文で示します。「この判断はどの段階で行うのか」の線引き表も付けました。

確認環境:Ubuntu 24.04、PostgreSQL 16.15、MySQL 8.0.46(InnoDB)、Mermaid 12.1.0(@mermaid-js/mermaid-cli 12.0.0 と Chromium で描画できることを確認)。確認日 2026-10-05。出力例のデータベース名は appdb に置き換えています。

この記事の結論

  • 概念設計:業務の「もの」と「関係」を決める。列や型は決めない。
  • 論理設計:表・列・主キー・外部キー・正規化を決める。どの製品でも通じる内容にとどめる。
  • 物理設計:型・索引・パーティション・非正規化・容量を決める。製品と版を決めてから行う。
  • 線引きの基準:「製品を替えても変わらない判断」は論理、「製品やデータ量で答えが変わる判断」は物理。

概念設計・論理設計・物理設計の早見表

観点 概念設計 論理設計 物理設計
目的 業務で扱うものと関係を合意する 重複や矛盾のないデータの構造を決める 決めた構造を、性能と容量を満たす形で実装する
製品への依存 なし なし あり(製品と版を決めてから)
成果物 概念ER図、用語一覧 論理ER図、表一覧、列一覧(キー付き) DDL、索引一覧、容量見積り、テーブル定義書
決めること エンティティ、関連、多重度 表、列、主キー、外部キー、一意制約、NULL可否、値の範囲 データ型、桁、索引、パーティション、非正規化、採番方式、文字コード
決めないこと 列、キー、型 型の製品別表記、索引、パーティション 業務ルールそのもの(論理へ戻して直す)
主な相手 業務を知る人 設計者、開発者 開発者、DBを運用する人
表記の例 「社員が備品を借りる」 loans.employee_id は employees を参照 employee_id integer NOT NULL と索引
見直すきっかけ 業務の範囲が変わる 項目や関連が変わる データ量、検索のしかた、製品の版が変わる

本記事の内容は、ご自由にお使いください。
ご利用の際は、出典として本ページへのリンクを記載いただけますようお願いします。

(記載例)出典:株式会社RJC「論理設計と物理設計の違い|概念設計からDDLまで同じ題材で通す手順」

どの判断をどの段階で行うか(線引き表)

設計で迷いやすいのは「これは論理で決めるのか、物理で決めるのか」です。製品を替えても答えが変わらないなら論理、製品・データ量・検索のしかたで答えが変わるなら物理と分けると整理できます。

判断 行う段階 理由
何をエンティティにするか 概念 業務の言葉で決まる
多対多の関係をどう表すか(中間の表を作る) 論理 どの製品でも同じ形になる
主キーを何にするか(どの列で1行が決まるか) 論理 行の同一性は業務ルール
外部キー(どの表を参照するか) 論理 関連は業務ルール
一意にしたい列の組み合わせ 論理 重複を許すかは業務ルール
NULLを許すか、値の範囲 論理 「未返却は空」などは業務ルール
正規化(第3正規形まで) 論理 重複を除く作業は製品に依存しない
主キーを連番にするかUUIDにするか 物理 採番の仕組みと索引の大きさは製品で変わる
データ型と桁(integer か bigint か など) 物理 型の種類と範囲が製品で違う
索引を張る列 物理 検索のしかたとデータ量で決まる
非正規化(集計値を持つ、列を重複して持つ) 物理 性能のために論理モデルを崩す判断
パーティション 物理 データ量と製品の制約で決まる
文字コード、照合順序 物理 製品の設定
容量見積り 物理 型と索引が決まって初めて計算できる

論理で止めるか、物理まで進めるかの判断基準

状況 選ぶもの
業務の人と項目の過不足を確認したい 論理モデルまで(型や索引は見せない)
製品がまだ決まっていない 論理モデルまで
見積りや容量の相談をしたい 物理設計まで(行数と型が要る)
検索が遅い、容量が足りない 物理設計だけを見直す(論理は変えない)
項目の意味や関連が変わった 論理へ戻って直し、物理へ反映する

題材:備品貸出管理の要件

架空の社内システムです。

  • 社員は部署に所属する。
  • 備品(ノートPC、プロジェクターなど)にはカテゴリがあり、1台ずつ資産番号が付いている。
  • 社員は1回の貸出で複数の備品を借りられる。返却は備品ごとに行える。
  • 貸出には返却予定日がある。
  • 想定データ量:社員300人、備品2,000台、貸出は1日200件ほど。

手順1:概念設計(概念ER図)

概念設計では、要件の文章から名詞(もの)と動詞(関係)を拾います。列・キー・型は書きません。 「社員が備品を借りる」は多対多のまま書いてかまいません。

erDiagram
    DEPARTMENT ||--o{ EMPLOYEE : "所属する"
    EMPLOYEE }o--o{ ITEM : "借りる"
    CATEGORY ||--o{ ITEM : "分類する"
    DEPARTMENT["部署"]
    EMPLOYEE["社員"]
    ITEM["備品"]
    CATEGORY["カテゴリ"]
成果物 内容
概念ER図 上の図。エンティティ4つと関連3つ
用語一覧 「貸出=社員が備品を借りる1回の手続き」「備品=資産番号が付いた1台」など

この段階で確認するのは「備品は種類ではなく1台ずつを指す」「1回に複数借りられる」といった業務の前提です。

手順2:論理設計(論理モデル)

論理設計では、概念ER図を表・列・キーに落とします。行う作業は次の4つです。

作業 今回の内容
多対多を分解する 「社員が備品を借りる」を、貸出(loans)と貸出明細(loan_items)に分ける
主キーを決める 各表に1行を決める列を置く。貸出明細は「貸出+備品」の組み合わせ
外部キーを決める 参照先の表を決める
正規化する カテゴリ名を備品の表に直接持たず、カテゴリの表に分ける

論理ER図

erDiagram
    departments ||--o{ employees : "所属"
    employees ||--o{ loans : "借りる"
    loans ||--|{ loan_items : "含む"
    items ||--o{ loan_items : "貸し出される"
    categories ||--o{ items : "分類"
    departments {
        int department_id PK
        string department_name UK
    }
    employees {
        int employee_id PK
        string employee_code UK
        string employee_name
        int department_id FK
    }
    categories {
        int category_id PK
        string category_name UK
    }
    items {
        int item_id PK
        string asset_code UK
        string item_name
        int category_id FK
        string status
    }
    loans {
        int loan_id PK
        int employee_id FK
        datetime loaned_at
        date due_date
    }
    loan_items {
        int loan_id PK, FK
        int item_id PK, FK
        datetime returned_at
    }

図の中の int や string は「数値」「文字」という大まかな区別です。integer か bigint か、何桁かは、ここでは決めません。

正規化後の表一覧

表 役割 主キー 外部キー 一意にする列
departments 部署 department_id なし department_name
employees 社員 employee_id department_id → departments employee_code
categories 備品カテゴリ category_id なし category_name
items 備品(1台ずつ) item_id category_id → categories asset_code
loans 貸出(1回の手続き) loan_id employee_id → employees なし
loan_items 貸出明細(貸出に含まれる備品) loan_id + item_id loan_id → loans、item_id → items なし

列一覧(論理)

表 列 意味 必須 値の範囲・ルール
items asset_code 資産番号 ○ 重複しない
items status 状態 ○ 貸出可・貸出中・廃棄のいずれか
loans loaned_at 貸出日時 ○
loans due_date 返却予定日 ○ 貸出日以降
loan_items returned_at 返却日時 空なら未返却

「返却日時が空なら未返却」「状態は3種類」は業務ルールなので、論理設計で決めます。それを CHECK 制約で書くか、どの型にするかは物理設計です。

手順3:物理設計(PostgreSQLのDDL)

製品を PostgreSQL 16 と決めたうえで、型・採番・索引を決めます。

-- 物理DDL(PostgreSQL 16)
CREATE TABLE departments (
    department_id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    department_name varchar(50) NOT NULL UNIQUE
);

CREATE TABLE employees (
    employee_id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    employee_code char(6)     NOT NULL UNIQUE,
    employee_name varchar(50) NOT NULL,
    department_id integer     NOT NULL REFERENCES departments (department_id)
);

CREATE TABLE categories (
    category_id   smallint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    category_name varchar(30) NOT NULL UNIQUE
);

CREATE TABLE items (
    item_id     integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    asset_code  varchar(12)  NOT NULL UNIQUE,
    item_name   varchar(100) NOT NULL,
    category_id smallint     NOT NULL REFERENCES categories (category_id),
    status      varchar(10)  NOT NULL DEFAULT 'available'
                CHECK (status IN ('available', 'on_loan', 'retired'))
);

CREATE TABLE loans (
    loan_id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    employee_id integer     NOT NULL REFERENCES employees (employee_id),
    loaned_at   timestamptz NOT NULL DEFAULT now(),
    due_date    date        NOT NULL
);

CREATE TABLE loan_items (
    loan_id     bigint  NOT NULL REFERENCES loans (loan_id),
    item_id     integer NOT NULL REFERENCES items (item_id),
    returned_at timestamptz,
    PRIMARY KEY (loan_id, item_id)
);

-- 索引(物理設計で決める)
CREATE INDEX idx_employees_department ON employees (department_id);
CREATE INDEX idx_items_category       ON items (category_id);
CREATE INDEX idx_loans_employee       ON loans (employee_id, loaned_at DESC);
CREATE INDEX idx_loan_items_item      ON loan_items (item_id);
-- 返却されていない明細だけを対象にした部分索引
CREATE INDEX idx_loan_items_unreturned ON loan_items (item_id) WHERE returned_at IS NULL;

物理設計で決めた点は次のとおりです。

決めたこと 内容 理由
採番 GENERATED ALWAYS AS IDENTITY 連番を自動で振る
型の大きさ 貸出は bigint、社員・備品は integer、カテゴリは smallint 件数の見込みに合わせる
日時 timestamptz タイムゾーン付きで時点を保存する
状態 varchar + CHECK 3種類の値に限定する
外部キーの列の索引 自分で CREATE INDEX する PostgreSQLでは自動では作られない
部分索引 WHERE returned_at IS NULL 未返却の明細だけを小さな索引にする

テスト用のデータを入れて大きさを確かめる

貸出10万件、貸出明細15万件を入れて確かめます。

INSERT INTO departments (department_name) SELECT '部署' || g FROM generate_series(1, 10) g;
INSERT INTO employees (employee_code, employee_name, department_id)
SELECT 'E' || lpad(g::text, 5, '0'), '社員' || g, (g % 10) + 1 FROM generate_series(1, 300) g;
INSERT INTO categories (category_name) SELECT 'カテゴリ' || g FROM generate_series(1, 8) g;
INSERT INTO items (asset_code, item_name, category_id)
SELECT 'A-' || lpad(g::text, 6, '0'), '備品' || g, (g % 8) + 1 FROM generate_series(1, 2000) g;
INSERT INTO loans (employee_id, loaned_at, due_date)
SELECT (g % 300) + 1, timestamptz '2024-01-01 09:00+09' + g * interval '10 minutes',
       date '2024-01-08' + (g / 144)
FROM generate_series(1, 100000) g;
INSERT INTO loan_items (loan_id, item_id, returned_at)
SELECT g, (g % 2000) + 1,
       CASE WHEN g > 99500 THEN NULL
            ELSE timestamptz '2024-01-03 09:00+09' + g * interval '10 minutes' END
FROM generate_series(1, 100000) g;
INSERT INTO loan_items (loan_id, item_id, returned_at)
SELECT g, ((g + 7) % 2000) + 1,
       CASE WHEN g > 99500 THEN NULL
            ELSE timestamptz '2024-01-03 09:00+09' + g * interval '10 minutes' END
FROM generate_series(1, 100000, 2) g;
ANALYZE;
-- 表と索引の大きさ
SELECT c.relname AS name, c.relkind AS kind, c.reltuples::bigint AS rows,
       pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relname LIKE ANY (ARRAY['loan%', 'idx_loan%'])
  AND c.relkind IN ('r', 'i')
ORDER BY c.relkind DESC, c.relname;
           name            | kind |  rows  |  size
---------------------------+------+--------+---------
 loan_items                | r    | 150000 | 7640 kB
 loans                     | r    | 100000 | 5888 kB
 idx_loan_items_item       | i    | 150000 | 1440 kB
 idx_loan_items_unreturned | i    |    730 | 40 kB
 idx_loans_employee        | i    | 100000 | 4848 kB
 loan_items_pkey           | i    | 150000 | 6176 kB
 loans_pkey                | i    | 100000 | 2208 kB

rows は統計による概数です(未返却の明細は実際には750行)。部分索引(idx_loan_items_unreturned)は40kBで、同じ列の通常の索引(1440kB)の約36分の1です。 未返却の行だけを対象にしているためです。検索でこの索引が使われることも確認できます。

EXPLAIN (COSTS OFF)
SELECT li.loan_id, li.item_id FROM loan_items li
WHERE li.item_id = 123 AND li.returned_at IS NULL;
                         QUERY PLAN
-------------------------------------------------------------
 Index Scan using idx_loan_items_unreturned on loan_items li
   Index Cond: (item_id = 123)

容量見積り

上の測定値から、1行あたりの大きさを出して見積ります。

対象 測定値 1行あたり 5年分の行数(例) 見積り
loans(表) 10万行で 5888 kB 約60バイト 245,000行 約14 MB
loans(索引2本) 10万行で 7056 kB 約72バイト 245,000行 約17 MB
loan_items(表) 15万行で 7640 kB 約52バイト 367,500行 約18 MB
loan_items(索引3本) 15万行で 7656 kB 約52バイト 367,500行 約18 MB

行数は「1日200件 × 年245日 × 5年」、明細は貸出の1.5倍と置いた例です。索引の合計が表本体と同じか、それより大きくなることが分かります。容量見積りが物理設計に属するのは、このように型と索引が決まらないと計算できないためです。

手順3の続き:同じ論理モデルをMySQLのDDLにする

論理モデルは同じまま、製品だけを MySQL 8.0 に替えます。

-- 物理DDL(MySQL 8.0 / InnoDB)
CREATE TABLE departments (
    department_id   INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    department_name VARCHAR(50)  NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE employees (
    employee_id   INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    employee_code CHAR(6)      NOT NULL UNIQUE,
    employee_name VARCHAR(50)  NOT NULL,
    department_id INT UNSIGNED NOT NULL,
    CONSTRAINT fk_employees_department FOREIGN KEY (department_id) REFERENCES departments (department_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE categories (
    category_id   SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(30) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE items (
    item_id     INT UNSIGNED      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    asset_code  VARCHAR(12)       NOT NULL UNIQUE,
    item_name   VARCHAR(100)      NOT NULL,
    category_id SMALLINT UNSIGNED NOT NULL,
    status      VARCHAR(10)       NOT NULL DEFAULT 'available',
    CONSTRAINT fk_items_category FOREIGN KEY (category_id) REFERENCES categories (category_id),
    CONSTRAINT chk_items_status CHECK (status IN ('available', 'on_loan', 'retired'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE loans (
    loan_id     BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    employee_id INT UNSIGNED    NOT NULL,
    loaned_at   DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    due_date    DATE            NOT NULL,
    CONSTRAINT fk_loans_employee FOREIGN KEY (employee_id) REFERENCES employees (employee_id),
    INDEX idx_loans_employee (employee_id, loaned_at DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE loan_items (
    loan_id     BIGINT UNSIGNED NOT NULL,
    item_id     INT UNSIGNED    NOT NULL,
    returned_at DATETIME(6)     NULL,
    PRIMARY KEY (loan_id, item_id),
    CONSTRAINT fk_loan_items_loan FOREIGN KEY (loan_id) REFERENCES loans (loan_id),
    CONSTRAINT fk_loan_items_item FOREIGN KEY (item_id) REFERENCES items (item_id),
    -- 部分索引が無いため、返却日時を2列目に持つ複合索引にする
    INDEX idx_loan_items_item_returned (item_id, returned_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

PostgreSQLとMySQLで変わった箇所

項目 PostgreSQL 16 MySQL 8.0(InnoDB)
採番 GENERATED ALWAYS AS IDENTITY AUTO_INCREMENT
整数の型 integer、bigint、smallint INT UNSIGNED など符号なしを選べる
タイムゾーン付きの日時 timestamptz DATETIME(6)(保存した値のまま)または TIMESTAMP
外部キーの列の索引 自動では作られない 自動で作られる
部分索引 WHERE 付きで作れる 構文エラーになる。複合索引で代える
パーティションと外部キー 併用できる(主キーにパーティションキーが必要) 併用するとエラーになる

表の数、列、主キー、外部キーの参照先は1つも変わっていません。 変わったのは型・採番・索引・書き方だけで、これが「論理設計は製品に依存しない」の実物です。

外部キーの列の索引

MySQLでは、外部キーを付けた列に索引が自動で作られます。

SELECT table_name, index_name, GROUP_CONCAT(column_name ORDER BY seq_in_index) AS cols
FROM information_schema.statistics
WHERE table_schema = 'appdb' AND table_name IN ('employees', 'items')
GROUP BY table_name, index_name ORDER BY table_name, index_name;
+------------+-------------------------+---------------+
| TABLE_NAME | INDEX_NAME              | cols          |
+------------+-------------------------+---------------+
| employees  | employee_code           | employee_code |
| employees  | fk_employees_department | department_id |
| employees  | PRIMARY                 | employee_id   |
| items      | asset_code              | asset_code    |
| items      | fk_items_category       | category_id   |
| items      | PRIMARY                 | item_id       |
+------------+-------------------------+---------------+

PostgreSQLで同じことを確かめると、主キーの索引しかありません。

CREATE TABLE fk_parent (id integer PRIMARY KEY);
CREATE TABLE fk_child  (id integer PRIMARY KEY, parent_id integer NOT NULL REFERENCES fk_parent (id));
SELECT indexname FROM pg_indexes WHERE tablename = 'fk_child';
   indexname
---------------
 fk_child_pkey

部分索引

CREATE INDEX idx_unreturned ON loan_items (item_id) WHERE returned_at IS NULL;
ERROR 1064 (42000) at line 7: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE returned_at IS NULL' at line 1

MySQLでは (item_id, returned_at) の複合索引にすると、未返却の検索でその索引が使われます。

EXPLAIN FORMAT=TREE
SELECT loan_id, item_id FROM loan_items WHERE item_id = 123 AND returned_at IS NULL;
-> Filter: (loan_items.returned_at is null)  (cost=0.35 rows=1)
    -> Covering index lookup on loan_items using idx_loan_items_item_returned (item_id=123, returned_at=NULL)  (cost=0.35 rows=1)

日時の型

同じ「09:00」を入れてから、接続のタイムゾーンを日本時間からUTCに変えて読み出した結果です。

-- PostgreSQL
SET timezone = 'Asia/Tokyo';
CREATE TABLE tz_demo (a timestamp, b timestamptz);
INSERT INTO tz_demo VALUES ('2026-10-05 09:00:00', '2026-10-05 09:00:00');
SET timezone = 'UTC';
SELECT * FROM tz_demo;
          a          |           b
---------------------+------------------------
 2026-10-05 09:00:00 | 2026-10-05 00:00:00+00
-- MySQL
SET time_zone = '+09:00';
CREATE TABLE tz_demo (a DATETIME, b TIMESTAMP);
INSERT INTO tz_demo VALUES ('2026-10-05 09:00:00', '2026-10-05 09:00:00');
SET time_zone = '+00:00';
SELECT * FROM tz_demo;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2026-10-05 09:00:00 | 2026-10-05 00:00:00 |
+---------------------+---------------------+

PostgreSQLの timestamptz とMySQLの TIMESTAMP は接続のタイムゾーンに合わせて表示が変わり、PostgreSQLの timestamp とMySQLの DATETIME は入れた値のまま返ります。論理設計では「貸出日時」とだけ決め、どの型で持つかは物理設計で、アプリケーションのタイムゾーンの扱いと合わせて決めます。

CHECK制約

状態に定義外の値を入れると、どちらの製品でも止まります。

-- PostgreSQL
ERROR:  new row for relation "items" violates check constraint "items_status_check"
DETAIL:  Failing row contains (2001, B-000001, ノートPC 1号機, 1, lost).

-- MySQL
ERROR 3819 (HY000) at line 29: Check constraint 'chk_items_status' is violated.

パーティションは物理設計で、製品の制約を確かめてから決める

貸出を年ごとに分けたい、という判断はデータ量から来るので物理設計です。そしてパーティションは、論理設計で決めた主キーや外部キーに影響することがあります。

PostgreSQL:主キーにパーティションキーを含める必要がある

CREATE TABLE loans_p (
    loan_id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    employee_id integer     NOT NULL,
    loaned_at   timestamptz NOT NULL,
    due_date    date        NOT NULL
) PARTITION BY RANGE (loaned_at);
ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  PRIMARY KEY constraint on table "loans_p" lacks column "loaned_at" which is part of the partition key.

主キーを (loan_id, loaned_at) にすると作成できます。

CREATE TABLE loans_p (
    loan_id     bigint GENERATED ALWAYS AS IDENTITY,
    employee_id integer     NOT NULL REFERENCES employees (employee_id),
    loaned_at   timestamptz NOT NULL,
    due_date    date        NOT NULL,
    PRIMARY KEY (loan_id, loaned_at)
) PARTITION BY RANGE (loaned_at);
CREATE TABLE loans_p_2024 PARTITION OF loans_p FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE loans_p_2025 PARTITION OF loans_p FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

ただし、こうすると貸出明細から loan_id だけで参照できなくなります。

CREATE TABLE loan_items_p (
    loan_id bigint  NOT NULL REFERENCES loans_p (loan_id),
    item_id integer NOT NULL,
    PRIMARY KEY (loan_id, item_id)
);
ERROR:  there is no unique constraint matching given keys for referenced table "loans_p"

MySQL:パーティションと外部キーは併用できない

外部キーの無い履歴用の表なら分けられます。

CREATE TABLE loan_history (
    loan_id     BIGINT UNSIGNED NOT NULL,
    employee_id INT UNSIGNED    NOT NULL,
    loaned_at   DATETIME(6)     NOT NULL,
    PRIMARY KEY (loan_id, loaned_at)
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS (loaned_at) (
    PARTITION p2024 VALUES LESS THAN ('2025-01-01'),
    PARTITION p2025 VALUES LESS THAN ('2026-01-01'),
    PARTITION pmax  VALUES LESS THAN (MAXVALUE)
);

ここへ外部キーを足すとエラーになります。

ALTER TABLE loan_history ADD CONSTRAINT fk_h FOREIGN KEY (employee_id) REFERENCES employees (employee_id);
ERROR 1506 (HY000) at line 26: Foreign keys are not yet supported in conjunction with partitioning

この題材での判断

選択肢 内容 この題材での判断
分けない 5年で貸出24.5万行、表は約14MB これを選ぶ。この量なら索引で十分
履歴用の表だけ分ける 返却済みの貸出を別表へ移し、そちらを年で分ける 行数が数千万を超える見込みが出たら検討
貸出の表そのものを分ける 主キー・外部キーの見直しが要る 論理モデルへ戻って関連を見直す前提で検討

物理設計の判断が論理モデルに跳ね返るときは、DDLだけを直さず、論理モデルに戻って主キーと関連を書き直します。 2つの成果物がずれないようにするためです。

非正規化も物理設計で決める

「備品ごとの貸出回数を毎回数えるのは重いので、items に loan_count 列を持つ」といった判断は、論理モデルをわざと崩す非正規化です。論理モデルは正規化したまま残し、物理設計の記録に「どの列を、なぜ重複して持ち、どう更新するか」を書きます。

記録する項目 記入例
対象 items.loan_count(貸出回数)
元になるデータ loan_items の item_id ごとの件数
理由 備品一覧で毎回集計すると遅い(測定値を書く)
更新のしかた 貸出登録と同じトランザクションで+1する
ずれたときの直し方 夜間に loan_items から数え直して上書きする

工程ごとのチェック項目

概念設計

No チェック項目
1 エンティティの名前は業務の人が使う言葉になっている
2 関連に動詞(借りる、所属する)が書いてある
3 多重度(1対多、多対多)が書いてある
4 「1台ずつか、種類か」など、数え方の単位を用語一覧に書いた
5 列や型を書いていない

論理設計

No チェック項目
1 多対多の関連が中間の表に分解されている
2 すべての表に主キーがある
3 外部キーの参照先が表一覧に書いてある
4 業務上重複してはいけない列に一意制約がある(資産番号、社員番号)
5 第3正規形になっている(主キー以外の列で決まる列が残っていない)
6 NULLを許す列に、空のときの意味が書いてある(返却日時が空=未返却)
7 製品固有の型名や索引を書いていない

物理設計

No チェック項目
1 製品と版を決めた
2 件数の見込みから整数型の大きさを選んだ
3 日時の型を、タイムゾーンの扱いと合わせて決めた
4 外部キーの列に索引がある(PostgreSQLは自分で作る)
5 よく使う検索条件ごとに索引を決め、実行計画で使われることを確かめた
6 テスト用のデータを入れて表と索引の大きさを測り、容量を見積もった
7 パーティションを使うなら、主キー・外部キーへの影響を確かめた
8 非正規化した列に、理由・更新のしかた・直し方を書いた
9 論理モデルの表・列・キーとDDLが一致している

タブ区切り版(表計算ソフトに貼り付けて使えます):

工程  No  チェック項目  結果  メモ
概念  1   エンティティの名前は業務の人が使う言葉になっている       
概念  2   関連に動詞が書いてある     
概念  3   多重度が書いてある       
概念  4   数え方の単位を用語一覧に書いた     
概念  5   列や型を書いていない      
論理  1   多対多の関連が中間の表に分解されている     
論理  2   すべての表に主キーがある        
論理  3   外部キーの参照先が表一覧に書いてある      
論理  4   重複してはいけない列に一意制約がある      
論理  5   第3正規形になっている     
論理  6   NULLを許す列に空のときの意味が書いてある      
論理  7   製品固有の型名や索引を書いていない       
物理  1   製品と版を決めた        
物理  2   件数の見込みから整数型の大きさを選んだ     
物理  3   日時の型をタイムゾーンの扱いと合わせて決めた      
物理  4   外部キーの列に索引がある        
物理  5   検索条件ごとの索引を実行計画で確かめた     
物理  6   表と索引の大きさを測り容量を見積もった     
物理  7   パーティションの主キー・外部キーへの影響を確かめた       
物理  8   非正規化した列に理由・更新のしかた・直し方を書いた       
物理  9   論理モデルとDDLが一致している        

確かめて分かったこと

  • 論理モデルを変えずに製品だけ替えると、DDLの差は型・採番・索引・書き方に収まりました。 6表の列、主キー、外部キーの参照先はPostgreSQLとMySQLで同じです。
  • 外部キーの列の索引は、MySQLでは自動で作られ、PostgreSQLでは作られませんでした。 同じ論理モデルでも、索引一覧は製品ごとに書き分ける必要があります。
  • 部分索引は通常の索引の約36分の1の大きさでした(40kBと1440kB、未返却は15万行のうち750行)。MySQL 8.0.46 では WHERE 付きの CREATE INDEX は構文エラーになり、複合索引で代えました。
  • 索引の合計は表本体より大きくなることがあります。 loans は表が5888kBに対して索引2本で7056kBでした。容量見積りは索引を含めて行います。
  • パーティションは主キーと外部キーに影響しました。 PostgreSQLでは主キーにパーティションキーを含める必要があり、MySQLでは外部キーと併用するとエラーになりました。物理の判断が論理モデルへ跳ね返る代表例です。
  • MermaidのER図は、エンティティに日本語の表示名を付けて描画できました(DEPARTMENT["部署"] の書き方)。概念ER図を業務の人に見せるときに使えます。

よくある質問

Q. 論理設計と物理設計の違いは何ですか? A. 論理設計は製品に依存しない表・列・キー・関連を決め、物理設計は型・索引・パーティション・容量など製品に依存する事柄を決めます。製品を替えても変わらない判断が論理、変わる判断が物理です。

Q. 概念設計と論理設計の違いは何ですか? A. 概念設計は業務の「もの」と「関係」だけを決め、論理設計はそれを表・列・キーに落とします。概念設計では多対多の関係をそのまま書き、論理設計で中間の表に分解します。

Q. インデックスは論理設計と物理設計のどちらで決めますか? A. 物理設計で決めます。索引は検索のしかたとデータ量、製品の機能で答えが変わるためです。一意にしたい列の組み合わせ(一意制約)は業務ルールなので論理設計で決めます。

Q. 非正規化はどの段階で行いますか? A. 物理設計で行います。論理モデルは正規化したまま残し、性能のために重複して持つ列と、その理由・更新のしかたを物理設計の記録に書きます。

Q. 小さなシステムでも3段階に分ける必要はありますか? A. 成果物を3つに分ける必要はありませんが、判断は分けて考えると見直しやすくなります。表が数個なら、論理ER図とDDLの2つで足ります。

まとめ

  • 概念設計は「もの」と「関係」、論理設計は「表・列・キー」、物理設計は「型・索引・パーティション・容量」を決める。
  • 線引きの基準は製品への依存:製品を替えても変わらない判断は論理、変わる判断は物理。
  • キーと関連は論理、索引と非正規化は物理で決める。
  • 同じ論理モデルから、PostgreSQLとMySQLのDDLを書き分けられる。変わるのは型・採番・索引・書き方。
  • 容量見積りはテスト用のデータを入れて測る。索引の合計が表本体を上回ることがある。
  • パーティションは主キーと外部キーに影響する。物理の判断が跳ね返るときは論理モデルへ戻って直す。
  • 工程ごとのチェック項目で、決めることと決めないことを確かめる。

本記事の内容は、ご自由にお使いください。
ご利用の際は、出典として本ページへのリンクを記載いただけますようお願いします。

(記載例)出典:株式会社RJC「論理設計と物理設計の違い|概念設計からDDLまで同じ題材で通す手順」

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。

WE ARE HIRING

RJCで一緒に開発しながら、
成長を楽しみませんか?

RJCは、Web・モバイル・AIを活用した開発プロジェクトで、テックリードやPM・PMOも活躍するシステム開発会社です。会社を知る、待遇を確かめる、話を聞いてみる。気になるところ見てみてください!

  • 127日年間休日
  • 12時間平均残業時間
  • 毎日ガチャ遊びココロも大切にする福利厚生。アマギフなどの賞品ラインナップ!

ほかにも、チケットレストラン、書籍読み放題、2年ごとの慰労報奨(休暇 or 金一封)、11期連続の黒字決算。

ABOUT RJC RJCがどんな会社か知る 考え方、研修、働き方、福利厚生、社員の前職まで。RJCのことが丸わかり! RJC丸わかりページへ JOB DESCRIPTION 仕事内容・待遇を見てみる 仕事内容、給与・待遇、選考の流れ。経験者も未経験も!応募前に知りたいこと、まとめました! 募集要項を見る ENTRY エントリーする エントリーは1〜2分・履歴書不要です。まずは話を聞いてみたい、という方でも歓迎です! エントリーフォームへ

RJCで一緒に開発しながら、 成長を楽しみませんか?