- 設計
論理設計と物理設計の違い|概念設計からDDLまで同じ題材で通す手順
論理設計とは、データベース製品に依存しない形で表・列・キー・関連を決める工程で、物理設計とは、型・索引・パーティション・容量など製品に依存する事柄を決める工程です。 その前にある概念設計は、業務に出てくる「もの」と「関係」だけを決めます。
この記事では、架空の備品貸出管理を題材に、概念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期連続の黒字決算。