はじめに
初心者だけど、データベーススペシャリスト試験を受けたくなってしまったために、データベース設計の練習をしてみようと思いました。身近にたくさん業務用データがあるわけではなくて練習のネタに困ったので、身近なもので練習してみたいと思います。
今回は、モノタロウでダイヤモンドペーストを買ったので、ECサイトの領収書で関係スキーマを書き起こそうと思います（実用的なお題で腕を磨きます）。ちなみに、購入したダイヤモンドペーストでは石を磨きます。
レシートを観察する
今回使った、モノタロウの領収書。
エンティティを見出す
Udemyの講座「はじめてのテーブル設計・データベース設計【わかりやすい解説 + 身近なテーマでレッスン】」で学んだ通り、5W1H（いつ、どこで、だれが、なにを、なぜ、どのように）を考えてエンティティを見出します。
| 5W1H | 項目 |
|---|---|
| いつ | 納品書情報（ご注文日, 出荷日） |
| どこで | ECサイトなのでなし |
| だれが | ユーザー情報 |
| なにを | 商品情報（ブランド, 商品名, 品番, オプション, 内容量） |
| 注文情報（注文コード, 単価, 金額, 消費税, 費用負担先, 数量） | |
| 納品書情報（納品書番号, 参照番号, 合計, 消費税） | |
| どのように | 納品書情報（お支払方法） |
テーブルにしてみる
前回、ここで急に関係スキーマを書いてしまっていたのですが、一旦テーブルにした方が正規化しやすかったり、エンティティの漏れがあるか確認できたりすることに気がつきました（← 流していたら案外気づかないもんだと反省）。
ユーザー情報
| ユーザーID | 名前 | 住所 | 電話番号 | パスワード |
|---|---|---|---|---|
| PK |
商品情報
| 品番 | 商品名 | ブランド | オプション | 内容量 |
|---|---|---|---|---|
| PK |
注文情報
| 注文コード | 品番 | 単価 | 金額 | 消費税 | 数量 |
|---|---|---|---|---|---|
| PK | FK |
納品書情報
| 納品書番号 | 注文コード | ユーザーID | ご注文日 | 出荷日 | お支払方法 | 合計 | 消費税 |
|---|---|---|---|---|---|---|---|
| PK | PK, FK |
注文1項目につき1種類の商品（例：注文コード「07331125」に対して「ダイヤモンドペースト2本」）になるので、商品情報と注文情報は1対1。
納品書1つにつき、複数個の商品を注文できるので（例：納品書1つに対して「ダイヤモンドペースト #6000 2本」と「紙やすり 3枚」）、納品書情報と注文情報は1対多になります。したがって、注文コードは複数になるので、納品書番号と組み合わせて一意になり、複合主キーにします。
ちなみに、実際のモノタロウでは「分納（納品日を商品毎の入荷日に合わせてずらす）」ができますが、より複雑になるのでここでは考えていません。
関係スキーマを考える
ユーザー情報 (ユーザーID, 名前, 住所, 電話番号, パスワード)
商品情報 (品番, 商品名, ブランド, オプション, 内容量)
注文情報 (注文コード, 品番, 単価, 金額, 消費税, 数量)
納品書情報 (納品書番号, 注文コード, ユーザーID, ご注文日, 出荷日, お支払方法, 合計, 消費税)
◇ 主キー
ユーザー情報（ユーザーID）
商品情報（品番）
注文情報（注文コード）
納品書情報（納品書番号、注文コード）
◇ 外部キー
注文情報（品番）
納品書情報（注文コード）
設計したテーブルを基に書き起こしました。キーは下線で表記するのが正式ですが、Qiitaでは上手く表記できなかったので下に並べて書いています。
ChatGPT先生の意見
※ AIの出力は全て内容を理解し、問題点がないか確認しております。
改善点の提案
- 送料や代引手数料の扱い
現在の設計では、送料・手数料が「商品」ではないため扱いづらい。
解決策としては：- 費目 テーブル（「商品」「送料」「代引手数料」など）を作る
- 注文テーブル に 費目ID を入れて管理
- または、送料・手数料は 納品書テーブル に含めてもよい
- 納品書番号と注文コードの関係
今の構成で、1つの納品書に複数商品が紐づく構造はOK。
今後、複数注文が1つの納品書にまとめられる場合に備え、注文テーブルに納品書番号を外部キーとして持たせるのが適切。
まとめ
全体として「正しいスキーマ設計になっている」と言えるよ。元データにしっかり即していて、あと少し拡張すれば業務レベルでも使える設計になる。
→→ 既に業務レベルだと調子に乗っていてすみませんでした！！
改善後のスキーマ概要図（ER図風）
以下は主なテーブル構成とリレーションの概要です：
【納品書】
- 納品書ID（PK）
- 納品書番号
- ご注文日
- 出荷日
- 支払方法
- 合計金額（税込）
- 合計消費税
- 発行日
【注文明細】
- 明細ID（PK）
- 納品書ID（FK）
- 商品ID（NULL可）
- 費目ID（NULL可）← 商品ではない場合はこちら
- 数量
- 単価（税抜）
- 金額（税込）
- 消費税額
【商品】
- 商品ID（PK）
- 品番
- ブランド名
- 商品名
- オプション（#60000など）
- 内容量
【費目】
- 費目ID（PK）
- 名称（例: 配送料、代引手数料）
- 種別（enum: 配送関連, 手数料, その他）
解説ポイント
注文明細 は「商品」か「費目」のどちらかを持つようにし、商品でないもの（送料・手数料など）も一元管理できます。
「商品ID」「費目ID」両方を nullable にし、どちらか一方が入る設計。
送料や代引手数料は「商品」ではないため、費目 テーブルで管理します。
注文コードを主キーにしないのは、
- 文字数や形式など外部システムの事情で変わることがある
- 顧客の要望で再発行されることもある
- 一意でなくなるケース（再注文など）も将来的にはあり得る
だから「見た目上一意」でも、主キーにはしないのがプロの判断です。
→→ ……いや、納品書番号と明細ID（注文コードのこと）で複合主キーにするんとちゃうんかい！！！前回教えてくれたのは何だったのか……
複合主キーをなぜ使わない？
納品書番号（または納品書ID）を主キーにし、注文コードとの複合主キーにしない方が望ましいようです。
1. 納品書と注文は1対1とは限らない
1回の注文に対して複数の納品書が発行されることもあります（例えば分割発送）。逆に、複数の注文が1枚の納品書にまとまることもあり得ます（まとめ配送など）。
→→ 完全に見落としていた……
したがって、「注文コード」は納品書の一意性を保証する鍵にはなりません。
2. 複合キーは運用・設計が煩雑になりやすい
複合主キーは他のテーブルが外部キーを張るときにも同じ複合構造を持たせる必要があり、クエリや結合が煩雑になります。
例えば、返品情報を追加した場合、
注文詳細 ( 注文ID, 商品ID, 数量 ) → PK ( 注文ID, 商品ID )
返品情報 ( 注文ID, 商品ID, 返品理由 ) → FK ( 注文ID, 商品ID )
参照するには「注文ID」と「商品ID」をセットで注文詳細に参照しなければいけません。
将来的に注文管理と納品管理が別ドメインになる可能性を考えると、疎結合にしておく方が後々困らずに済みそうです。
3. 納品書はそれ自体が一意な業務文書
納品書は「納品」という実行行為に対して発行されるドキュメントなので、それ自体で一意なID（納品書番号）を持つのが自然。
業務の中でも「この納品書の控えをください」と言われたときに、納品書番号でトラッキングするのが普通です。
結論
納品書ID（または納品書番号）は独立した主キーとして扱い、注文コードとは関連付けるだけに留めるべきです。複合キーにすると、柔軟性と保守性が下がってしまいます。
むやみにID主キーを作らない方がいいのでは
→→ 私「やたらめったらIDを主キーにしない方がいいと聞いたことがあるけれど、やっぱり複合主キーは使いにくいしIDを決めた方が分かりやすいと思うんだ……」
ID主キーの方が管理しやすい
理由1：シンプルでクエリが書きやすい
WHERE 納品書ID = '117307100002-01' みたいに、単一のカラムで一意性が保てるのはとても大きい。
複合主キーだと WHERE 注文ID = X AND 商品ID = Y のようになって、常に両方セットで考える必要があります。
理由2：外部キーが楽
ID主キーなら、他テーブルの外部キーも1カラムで済む。
複合主キーだと、参照先にも2カラム持たせないといけなくなる（これは非常にやっかい）。
理由3：後からスキーマ変更に強い
将来的に「納品書番号の付け方を変える」とか「注文と商品が1対多になる」など変更が起きたとき、ID主キーなら影響範囲が最小限で済む。
複合主キーは結合・参照が全崩れする。
納品書 ( 納品書番号, 注文コード ) → PK ( 納品書番号, 注文コード )
注文明細 ( 明細ID, 納品書番号, 注文コード ) → PK ( 明細ID )
↓↓↓↓↓
⚠️ 仕様変更で納品番号の付け方を変えただけで複合主キーの結合が壊れる！！！
IDをむやみに使ってはいけないケース
多くはこの2つのケースです：
- 自然キーで十分な一意性が保証できるのに、わざわざIDを作るケース
例：都道府県名（「東京都」「大阪府」など）が一意なのに、「都道府県ID」カラムを別に作る。
- テーブルがシンプルすぎてIDが意味をなさないケース
例：設定情報を1件しか持たないようなテーブルに「設定ID」がある。
結論
「ID主キー」はスキーマ設計の基本兵器。
ChatGPT先生『
過剰に使うのは避けるべきだけど、実務ではむしろ積極的に使う方が安全で強いです。
「意味のある情報はカラムに、識別にはIDを」
→ これが安全・強靭・実用的な設計哲学。
』
→→ おお、なるほど！以後気を付けます。