MySQLで学ぶ受注管理システム開発（1）「テーブル設計とER図を描こう」

開発・動作確認環境：GitHub Codespaces（前回のブログ記事で構築した環境を使用）

はじめに
1. 連載の全体像
なぜ「受注管理システム」なのか
要件整理
ER図
正規化のポイント
テーブル定義（DDL）
サンプルデータの投入
動作確認
GitHubへの反映
まとめ
コードのダウンロード
1. 関連記事
2. 関連

はじめに

以前、「GitHub Codespacesで MySQL学習環境をつくろう！」にて、社員表・部署表を使ったMySQL学習環境の構築手順を紹介した。今回からは、その続編として、より実践的な「受注管理システム」をゼロから設計・構築する全5回の連載を始める。

連載の全体像

回	テーマ
第1回（今回）	テーブル設計とER図を描こう
第2回	CRUD操作を極めよう
第3回	ビューとストアドプロシージャを作ろう
第4回	トリガーとユーザー権限管理
第5回	パフォーマンスチューニングとバックアップ

前回の環境（mysql-learningリポジトリ、Codespaces上のMySQL8.0）をそのまま流用するので、まだ環境構築を済ませていない方は、先に前回のブログ記事を参照してほしい。

今回は、社員表・部署表のような2テーブル構成ではなく、顧客・商品・注文・注文明細という4テーブルからなる、より実務に近い構成を設計する。テーブル設計は、DB学習における最初にして最大の関門である。ここで正規化の考え方をしっかり押さえておこう。

なぜ「受注管理システム」なのか

社員表・部署表は「1対多」の関係が1つだけのシンプルな構成だった。実務のシステムでは、以下のような関係が複雑に絡み合う。

1人の顧客が複数回注文する（1対多）
1回の注文に複数の商品が含まれる（多対多）
商品には在庫や価格といった属性が付随する

この「多対多」の関係をどう扱うかが、リレーショナルデータベース設計の核心である。今回は、この関係を「注文明細（order_details）」という中間テーブルで解消する設計を学ぶ。

要件整理

以下の4つの管理対象を洗い出した。

顧客（customers）：注文する人の情報
商品（products）：販売する商品の情報
注文（orders）：いつ、誰が注文したかという注文のヘッダー情報
注文明細（order_details）：その注文で、どの商品を何個買ったかという明細情報

ER図

文字だけで関係を表すと、以下の通りとなる。

customers (顧客)
    │ 1
    │
    │ N
orders (注文)
    │ 1
    │
    │ N
order_details (注文明細)
    │ N
    │
    │ 1
products (商品)

1人の顧客は複数の注文を持つ（customers 1 : N orders）
1件の注文は複数の注文明細を持つ（orders 1 : N order_details）
1つの商品は複数の注文明細に登場しうる（products 1 : N order_details）

こうして「注文」と「商品」という2つの1対多の関係を経由することで、結果的に「注文」と「商品」の多対多の関係が表現できている。これが、注文明細テーブル（中間テーブル）の役割である。

正規化のポイント

テーブル設計における「正規化」とは、データの重複や矛盾が起きないように、テーブルを適切に分割することをいう。今回の設計では、以下の点を意識した。

商品名や単価を注文明細に直接持たせない
これらは商品マスタ（products）だけが持ち、注文明細はproduct_idで参照する。こうすることで、単価変更時に1箇所だけ直せばよくなる
顧客名や住所を注文に直接持たせない
これも同様に、顧客マスタ（customers）だけが持つ
繰り返し項目を別テーブルに分離する
1つの注文に複数の商品が含まれる場合、注文テーブルに「商品1、商品2、商品3…」のような列を増やすのではなく、注文明細という別テーブルで行を増やして対応する

これが、第1正規形〜第3正規形の考え方の実践的な適用である。
まずは「同じ情報を2箇所に書かない」という原則を体で覚えることが大切だろう。

テーブル定義（DDL）

それでは、実際にテーブルを作成しよう。Codespacesを起動し、MySQLに接続しよう。

mysql -h db -u root -ppassword

以下のSQL文を順次実行する。

-- データベース作成（前回のcompany_dbとは別に作成する）
CREATE DATABASE order_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE order_db;

-- 顧客表
CREATE TABLE customers (
    customer_id VARCHAR(10) PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 商品表
CREATE TABLE products (
    product_id VARCHAR(10) PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0,
    CHECK (unit_price >= 0),
    CHECK (stock_quantity >= 0)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 注文表
CREATE TABLE orders (
    order_id VARCHAR(10) PRIMARY KEY,
    customer_id VARCHAR(10) NOT NULL,
    order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status ENUM('受付', '出荷済', 'キャンセル') NOT NULL DEFAULT '受付',
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 注文明細表
CREATE TABLE order_details (
    order_detail_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id VARCHAR(10) NOT NULL,
    product_id VARCHAR(10) NOT NULL,
    quantity INT NOT NULL,
    unit_price_at_order DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    CHECK (quantity > 0)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ポイント

order_detailsのunit_price_at_orderは、あえて商品表のunit_priceと同じ値を持たせている。これは正規化に反するようにも見えるが、「注文した時点の単価」を記録として残す（後から商品の値段が変わっても、過去の注文金額が変わらないようにする）という実務上の理由による。
このように、正規化は絶対的なルールではなく、目的に応じて意図的に崩す判断も必要になる
各表にCHECK制約を設けることで、マイナスの単価や0個以下の注文数量といった、明らかにおかしいデータの登録をデータベース側で防いでいる

サンプルデータの投入

設計したテーブルに、動作確認用のデータを入れておこう。

-- 顧客データ
INSERT INTO customers (customer_id, customer_name, email, phone) VALUES
    ('C001', '山田太郎', 'yamada@example.com', '090-1111-1111'),
    ('C002', '佐藤花子', 'sato@example.com', '090-2222-2222'),
    ('C003', '鈴木一郎', 'suzuki@example.com', '090-3333-3333');

-- 商品データ
INSERT INTO products (product_id, product_name, unit_price, stock_quantity) VALUES
    ('P001', 'ノートパソコン', 98000.00, 15),
    ('P002', 'ワイヤレスマウス', 2500.00, 50),
    ('P003', 'USBメモリ 64GB', 1200.00, 100);

-- 注文データ
INSERT INTO orders (order_id, customer_id, status) VALUES
    ('O001', 'C001', '出荷済'),
    ('O002', 'C002', '受付'),
    ('O003', 'C001', '受付');

-- 注文明細データ
INSERT INTO order_details (order_id, product_id, quantity, unit_price_at_order) VALUES
    ('O001', 'P001', 1, 98000.00),
    ('O001', 'P002', 2, 2500.00),
    ('O002', 'P003', 5, 1200.00),
    ('O003', 'P002', 1, 2500.00);

動作確認

正しくデータが入っているか確認しよう。

SHOW TABLES;

実行例

mysql> SHOW TABLES;
+--------------------+
| Tables_in_order_db |
+--------------------+
| customers          |
| order_details      |
| orders             |
| products           |
+--------------------+
4 rows in set (0.00 sec)

続いて、注文と顧客、商品を結合し、注文内容が一覧できるか確認する。

SELECT
    o.order_id,
    c.customer_name,
    p.product_name,
    od.quantity,
    od.unit_price_at_order,
    (od.quantity * od.unit_price_at_order) AS subtotal
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id
ORDER BY o.order_id;

実行例

mysql> SELECT
    -> o.order_id,
    -> c.customer_name,
    -> p.product_name,
    -> od.quantity,
    -> od.unit_price_at_order,
    -> (od.quantity * od.unit_price_at_order) AS subtotal
    -> FROM orders o
    -> JOIN customers c ON o.customer_id = c.customer_id
    -> JOIN order_details od ON o.order_id = od.order_id
    -> JOIN products p ON od.product_id = p.product_id
    -> ORDER BY o.order_id;
+----------+---------------+--------------------+----------+---------------------+----------+
| order_id | customer_name | product_name       | quantity | unit_price_at_order | subtotal |
+----------+---------------+--------------------+----------+---------------------+----------+
| O001     | 山田太郎      | ノートパソコン     |        1 |            98000.00 | 98000.00 |
| O001     | 山田太郎      | ワイヤレスマウス   |        2 |             2500.00 |  5000.00 |
| O002     | 佐藤花子      | USBメモリ 64GB     |        5 |             1200.00 |  6000.00 |
| O003     | 山田太郎      | ワイヤレスマウス   |        1 |             2500.00 |  2500.00 |
+----------+---------------+--------------------+----------+---------------------+----------+
4 rows in set (0.00 sec)

4つのテーブルを結合しても、正しく注文の中身が読み取れることが確認できた。これが、正規化されたテーブル設計の強みである。

GitHubへの反映

続けて、作成したSQLをmysql-learningリポジトリに追加し、コミット＆プッシュしておこう。

mysql-learning/
├── .devcontainer/
├── FE-OPEN-R7/
├── order-management/
│   └── 01_schema.sql   ← 今回作成したDDL＋サンプルデータ
├── README.md
└── SETUP.md

git add .
git commit -m "受注管理システムのテーブル設計を追加（第1回）"
git push

まとめ

今回は、社員表・部署表よりも一歩進んだ「受注管理システム」を題材に、以下を学んだ。

顧客・商品・注文・注文明細という4テーブル構成の設計
「多対多」の関係を中間テーブル（注文明細）で解消する考え方
正規化の基本原則と、あえて崩す実務上の判断
CHECK制約による、データベース側でのデータ品質担保

次回は、今回作成したテーブルに対して、INSERT・UPDATE・DELETEを実践し、トランザクションを使ってデータの整合性を担保する方法をご紹介しよう。

コードのダウンロード

今回のコードは、私のGitHubリポジトリからダウンロードできる。

▶ Rocky-Seven/mysql-learning/order-management

GitHub Codespacesで MySQL学習環境をつくろう！

月	火	水	木	金	土	日
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30
31