第2章 業務データをテーブルとして設計する

この章のゴール

データベース設計は、最初からテーブル名やSQLを考える作業ではありません。 まず業務の流れを確認し、何を管理し、何を記録し、どのデータ同士を関係付けるかを整理します。

この章を終えると、次のことができるようになります。

• 業務の資料や会話から、管理対象とその項目を見つける
• マスターデータと、業務の出来事を記録するデータを区別する
• 一件を識別する主キーの候補を考える
• 外部キーによってテーブル同士を関係付ける
• 1対多の関係を、業務上の言葉で説明する
• 単位、状態、日付、更新日時、未入力の扱いを決める
• 簡易データ辞書へ、項目の意味と業務ルールを記録する

研修では、説明と設計例に約55分、演習に約40分、解説と振り返りに約15分を使います。

今回扱う出荷予定管理

架空の飲料メーカー「みなと飲料」は、取引先から注文を受け、出荷先と出荷日に合わせて出荷予定を作成しています。 一つの出荷予定には、複数の商品を含められます。

この研修で扱うのは、次の範囲です。

• 商品、商品分類、容器種別を管理する
• 取引先と、その取引先が持つ出荷先を管理する
• 出荷予定日、出荷先、予定状態を記録する
• 出荷予定に含まれる商品とケース数を記録する
• 確定した出荷予定を検索し、出荷指示書の下書きに利用する

原材料、製造工程、設備、在庫引当、ロット、賞味期限などの本格的な生産管理は扱いません。 また、出荷予定と生産予定は別の情報です。この研修では「いつ、どこへ、何を、何ケース出荷するか」に範囲を絞ります。

業務の流れからデータを見つける

業務担当者へ「どのテーブルが必要ですか」と尋ねても、すぐに答えが出るとは限りません。 代わりに、仕事の順序、使う資料、判断する条件、困っていることを確認します。

図2-1 業務の各段階で、後から検索や確認に使うデータが発生します。

業務の流れを確認するときは、次の問いを使います。

確認すること	出荷予定管理での問い
誰が関わるか	注文した取引先はどこか。実際の出荷先はどこか
何を扱うか	どの商品を、何ケース出荷するか
いつ発生するか	注文日、出荷予定日、最終更新日時はいつか
どの状態か	仮登録、確定、取消、出荷済みのどれか
何で識別するか	どの出荷予定、商品、取引先のことか
どんな規則があるか	確定予定だけを通常の集計対象にするか

「明日の缶飲料は何ケース出荷予定か」という問いも、 この業務ルールを確認しなければ正しく集計できません。 対象日、容器種別、予定状態、数量単位をデータとして区別できる必要があります。

名詞・動詞・条件を分ける

業務の説明から、次の三種類の言葉を拾います。

• 名詞：管理対象や項目の候補
• 動詞：管理対象同士の関係や業務の出来事
• 条件：必須、重複禁止、許可する値などの業務ルール

たとえば、「取引先Aの東配送センターへ、11月20日にみなとソーダを12ケース出荷する」という文を分解します。

種類	見つかる言葉	設計で考えること
名詞	取引先、出荷先、商品、出荷予定日、ケース数	テーブルまたは列の候補
動詞	取引先が出荷先を持つ、予定に商品を含める	テーブル間の関係
条件	ケース数で記録する、確定後に指示へ使う	データ型、必須、状態のルール

名詞をすべてテーブルにするわけではありません。 「商品」は複数の項目を持ち、繰り返し使う管理対象なのでテーブルの候補です。 「商品名」は商品を説明する一つの項目なので、列の候補です。

管理対象と属性

データベース設計では、業務で区別して管理する対象をエンティティと呼びます。 本教材では、最初は「管理対象」という言葉を併記します。

管理対象が持つ特徴や項目を属性と呼びます。

管理対象の候補	役割	属性の例
商品	出荷する飲料を管理する	商品コード、商品名、内容量、入数
取引先	注文元の会社を管理する	取引先コード、取引先名
出荷先	商品を届ける場所を管理する	出荷先コード、出荷先名、住所
出荷予定	いつ、どこへ出荷するかを記録する	出荷予定番号、出荷予定日、状態
出荷予定明細	予定に含まれる商品と数量を記録する	明細番号、商品、ケース数

マスターデータと業務の出来事

商品や取引先のように、複数の業務で繰り返し参照する基本情報をマスターデータと呼びます。 出荷予定のように、日々発生する注文や処理を記録するデータをトランザクションデータと呼びます。

種類	特徴	この研修の例
マスターデータ	比較的長く使い、複数の処理から参照する	商品、商品分類、容器種別、取引先、出荷先
トランザクションデータ	業務の発生ごとに増え、日付や状態を持つ	出荷予定、出荷予定明細

ここでいうトランザクションデータは「業務上の出来事を記録するデータ」という意味です。 DBMSの機能としてのトランザクション制御は、この研修では詳しく扱いません。

一つの列に一つの意味を持たせる

列を考えるときは、一つの列に一つの意味を持たせます。 次のような列は、後から検索条件を指定しにくくなります。

分かりにくい例	問題	分け方の例
みなとソーダ／缶／350ml	商品名、容器、内容量が混在する	商品名、容器種別、内容量へ分ける
12ケース	数値と単位が同じ文字列に入る	ケース数を数値として持ち、単位を定義する
取引先A 東配送センター	取引先と出荷先を区別できない	取引先と出荷先へ分ける
明日	実行日によって意味が変わる	2026-11-20のような日付で持つ

表示するときは、複数の項目を結合して「みなとソーダ 350ml缶」と見せられます。 保存時に分けておけば、容器種別が缶の商品だけを検索したり、内容量で比較したりできます。

データ型を先に考える

データ型は、列へどの種類の値を保存するかを決めるものです。 この章では概念を整理し、MySQLで使用する正確な型は第3章で指定します。

データの種類	用途	出荷予定管理での例
文字列	名称、コード、状態	商品コード、商品名、予定状態
整数	小数を使わない数量	ケース数、1ケース当たりの本数
日付	年月日	出荷予定日
日時	年月日と時刻	最終更新日時
真偽値	はい・いいえ	取扱中かどうか

商品コードPRD001は数字だけに見える部分を含みますが、計算には使わない識別コードなので文字列として扱います。 郵便番号や電話番号も、先頭の0や記号を保持する必要があるため、通常は数値計算用の型にしません。

0、空文字、NULLを区別する

NULLは、値が入っていないことを表す特別な状態です。 数値の0や、長さ0の文字列である空文字とは異なります。

値	ケース数での解釈例
12	12ケースと登録されている
0	0ケースと明示されている
NULL	数量がまだ登録されていない

ただし、「不明」「未入力」「該当なし」をすべて同じNULLにすると、後から理由を区別できません。 業務上区別が必要なら、予定状態や確認状態など、別の項目も使って意味を明確にします。

この研修では、仮登録中はケース数が未入力になる可能性を残します。 確定へ変更する前には、すべての明細へ1以上のケース数が入っていることを確認する、という業務ルールを設けます。

主キーで一件を識別する

同じ名前の取引先や同名商品が存在する可能性があります。 名前だけを使って一件を識別すると、名称変更や表記揺れの影響も受けます。

そこで、テーブルの各行を一意に識別する列、または列の組み合わせを主キーとします。

主キーには、次の性質が必要です。

• 同じ値が重複しない
• NULLにならない
• 業務中に頻繁に変わらない
• 一件を識別するために必要な最小限の列である

内部IDと業務コード

この研修では、DB内部の関係付けに数値のIDを使い、 画面や帳票で確認する業務コードを別に持つ設計を基本とします。

用途	例	扱い
DB内部の識別	product_id	商品テーブルの主キー
業務で使うコード	product_codeのPRD001	重複を許さない商品コード
人が読む名称	product_nameのみなとソーダ	重複や変更の可能性がある

名称は人に分かりやすい一方で、識別子としては不安定です。 AIへ検索結果を渡す場合も、コードと名称の両方を表示すると、どの商品かを人が確認しやすくなります。

複数列で一件を識別する場合

出荷予定明細では、「出荷予定ID」と「明細番号」の組み合わせで一件を識別する案があります。 出荷予定IDだけでは、同じ予定に複数明細があるため一件に決まりません。

複数列を組み合わせた主キーを複合主キーと呼びます。 本研修で出荷予定明細に複合主キーを採用するかは、正規化と構築手順を確認して第5章までに確定します。

外部キーでテーブルを関係付ける

別のテーブルの主キーを参照する列を外部キーと呼びます。 たとえば、出荷予定明細のproduct_idは、商品テーブルのproduct_idを参照します。

図2-2 出荷予定明細が、出荷予定と商品を外部キーで結びます。

外部キーを使うと、存在しない商品IDを持つ出荷予定明細など、関係が切れたデータを防ぎやすくなります。 この仕組みを参照整合性と呼びます。

1対多を業務の言葉で確認する

「1対多」は、片方の一件に対して、もう片方の複数件が関係できることを表します。

• 一つの取引先は、複数の出荷先を持てる
• 一つの出荷先には、複数の出荷予定が作られる
• 一つの出荷予定は、複数の出荷予定明細を持てる
• 一つの商品は、複数の出荷予定明細に現れる

逆向きにも確認します。

• 一つの出荷先は、一つの取引先に属する
• 一つの出荷予定は、一つの出荷先を指定する
• 一つの出荷予定明細は、一つの商品を指定する

「一つ」「複数」「必ず」「場合がある」といった言葉を確認すると、 外部キーをどちらのテーブルへ置くか、未入力を許すかを判断しやすくなります。

単位・状態・時点を設計に含める

AIから業務データを利用するときは、値だけでなく、その解釈条件も必要です。

単位

出荷予定明細の数量はケース数として管理し、列名をcase_quantityとします。 本数へ換算する場合は、商品のunits_per_caseを使います。

合計本数 ＝ ケース数 × 1ケース当たりの本数

同じquantityという列名だけでは、ケース数、本数、重量のどれか分かりません。 列名とデータ辞書の両方で単位を明確にします。

状態

出荷予定は、仮登録、確定、取消、出荷済みなどの状態を持ちます。 状態ごとに、検索や変更のルールが変わります。

状態	意味	通常の明日分集計
仮登録	内容を編集中	含めない
確定	出荷する内容が決まっている	含める
取消	出荷しない	含めない
出荷済み	出荷処理が完了している	問いに応じて扱う

状態の意味を決めずにAIへ「出荷予定」を尋ねると、仮登録や取消を含めるか判断できません。

基準日と更新日時

「明日」は、質問した日によって変わります。 DBには2026-11-20のような日付を保存し、質問を受けた時点で「明日」を具体的な日付へ変換します。

また、回答には最終更新日時が必要です。 同じ出荷予定でも、午前9時の回答と午後3時の回答で数量が変わる可能性があるためです。

簡易データ辞書を作る

データ辞書は、テーブルや列の意味、型、必須条件、業務ルールを記録した資料です。 SQLを書く人だけでなく、業務担当者や、将来AI連携を作る担当者の共通資料になります。

次は、出荷予定テーブルの一部を記述した例です。

日本語項目名	列名案	種類	必須	意味・ルール
出荷予定ID	shipment_plan_id	整数	必須	DB内部で一件を識別する主キー
出荷予定番号	shipment_plan_no	文字列	必須	帳票でも使う重複しない番号
出荷予定日	shipment_date	日付	必須	商品を出荷する予定日
出荷先ID	delivery_destination_id	整数	必須	出荷先テーブルを参照する外部キー
予定状態	shipment_status	文字列	必須	仮登録、確定、取消、出荷済み
最終更新日時	updated_at	日時	必須	最後に内容が変更された日時

実際のデータ辞書では、許可する状態コード、値の範囲、入力例、個人情報の有無なども記録します。 MySQLの正確なデータ型と長さは、第3章でテーブルを作るときに追加します。

演習1：問題のある出荷予定表を設計し直す

次は、みなと飲料で使われていた一枚の出荷予定表です。 実際の表では、基本情報の右側に「商品1」と「商品2」の列グループが横並びになっていると考えてください。

この演習では、まだ第1正規形などの用語は使いません。 業務担当者として困る点を見つけ、管理対象、項目、関係へ置き換えます。

元の出荷予定表：基本情報

出荷予定番号	出荷日	取引先コード	取引先名	出荷先名
SP20261120-001	2026/11/20	CUS001	取引先A	東配送センター
SP20261120-002	2026/11/20	CUS001	取引先A	西店舗
SP20261120-003	2026/11/20	CUS002	取引先B	中央倉庫

出荷予定番号	状態	便
SP20261120-001	確定	午前便
SP20261120-002	仮登録	午後便
SP20261120-003	取消	午前便

元の出荷予定表：商品1の列グループ

出荷予定番号	商品1コード	商品1の説明	数量
SP20261120-001	PRD001	みなとソーダ／缶／24本入	12
SP20261120-002	PRD001	みなとソーダ／CAN／24本入	8
SP20261120-003	PRD004	港町レモン／PET／12本入	0

元の出荷予定表：商品2の列グループ

出荷予定番号	商品2コード	商品2の説明	数量
SP20261120-001	PRD003	みなと天然水／ペット／24本入	20
SP20261120-002
SP20261120-003	PRD002	しおかぜ茶／カン／24本入

表の値と名称はすべて研修用の架空データです。

手順1：問題点を見つける

次の観点で、少なくとも5件の問題を挙げてください。

• 同じ情報を何度も入力していないか
• 一つの欄へ複数の意味が入っていないか
• 表記が統一されているか
• 単位が分かるか
• 未入力と0を区別できるか
• 商品が3件以上になっても記録できるか
• 情報がいつ更新されたか分かるか

番号	見つけた問題	業務で起きる困りごと
1
2
3
4
5

手順2：管理対象と項目を分ける

見つけた名詞を、管理対象の候補と、その項目に分けてください。

管理対象の候補	その対象が持つ項目	一件を識別する候補
例：商品	商品コード、商品名、容器種別、入数	商品ID、商品コード

手順3：関係を文章にする

候補同士の関係を、「一つ」「複数」「属する」「含む」を使って文章にしてください。

例：一つの出荷予定は、複数の出荷予定明細を含む。

最低限、取引先と出荷先、出荷先と出荷予定、出荷予定と明細、商品と明細の関係を考えます。

手順4：データ辞書を3項目作る

AIへの質問「明日の缶飲料の確定済み出荷予定は何ケースですか」に必要な項目を三つ選び、 意味とルールを記入してください。

日本語項目名	列名案	種類	必須	意味・ルール

設計例を確認する

問題点の例

1. 商品1、商品2という繰り返し列があり、3商品目を記録できない
2. 商品名、容器、入数が出荷予定ごとに繰り返されている
3. 容器種別が「缶」「CAN」「カン」、「PET」「ペット」と揺れている
4. 数量がケース数か本数か、列だけでは分からない
5. 数量の0と未入力が混在し、意味を判断できない
6. 取引先名が複数行に繰り返され、名称変更時に一部だけ古くなる可能性がある
7. 取引先と出荷先が別の管理対象であることを表現できていない
8. 最終更新日時がなく、回答時点の新しさを確認できない
9. 状態の許可値と、集計へ含める状態が定義されていない

管理対象の候補

管理対象	主な項目の候補	識別子の候補
商品	商品コード、商品名、分類、容器、内容量、入数	商品ID
商品分類	分類コード、分類名	商品分類ID
容器種別	容器コード、容器名	容器種別ID
取引先	取引先コード、取引先名	取引先ID
出荷先	出荷先コード、出荷先名、住所	出荷先ID
出荷予定	予定番号、予定日、状態、更新日時	出荷予定ID
出荷予定明細	明細番号、商品、ケース数	出荷予定ID＋明細番号

商品分類と容器種別を独立したテーブルにするかは、 重複や変更時の問題を確認しながら第5章で改めて判断します。

関係の例

• 一つの取引先は、複数の出荷先を持てる
• 一つの出荷先は、一つの取引先に属する
• 一つの出荷先には、複数の出荷予定を作れる
• 一つの出荷予定は、複数の出荷予定明細を含む
• 一つの出荷予定明細は、一つの商品を指定する
• 一つの商品は、複数の出荷予定明細に現れる

データ辞書の例

日本語項目名	列名案	種類	必須	意味・ルール
出荷予定日	shipment_date	日付	必須	「明日」を具体的な日付へ変換して検索する
容器種別ID	package_type_id	整数	必須	商品が属する容器種別を参照する
ケース数	case_quantity	整数	条件付き	仮登録では未入力可、確定時は1以上
予定状態	shipment_status	文字列	必須	通常集計は確定だけを含める
最終更新日時	updated_at	日時	必須	回答がいつの情報かを確認する

この章で作成したもの

この章の演習で作成した次の内容を、後続章でも使用します。

• 元の出荷予定表にある問題点の一覧
• 商品、取引先、出荷先、出荷予定、出荷予定明細などの候補
• 管理対象同士の1対多の関係
• AIから検索するときに必要な項目のデータ辞書

第3章では、この候補のうち小規模なテーブルをMySQLへ作り、 データの登録、検索、更新、削除を通して設計を確認します。

この章のまとめ

• テーブル設計は、業務の流れと業務上の問いを確認するところから始めます。
• 名詞は管理対象や項目、動詞は関係、条件は業務ルールの候補になります。
• 主キーは一件を識別し、外部キーは別テーブルの主キーを参照します。
• 1対多は、両方向から業務の言葉で確認します。
• 数量には単位、予定には状態、回答には基準日と更新日時が必要です。
• 0、空文字、NULLは同じ意味ではありません。
• データ辞書は、人、SQL、AIの間でデータの意味を共有する資料になります。

次章では、MySQL Workbenchを使い、設計したテーブルを実際に構築・操作します。

第3章「MySQLでデータベースを構築・操作する」へ進む