はじめに
初心者だけど、データベーススペシャリスト試験を受けたくなってしまったために、データベース設計の練習をしてみようと思いました。身近にたくさん業務用データがあるわけではなくて練習のネタに困ったので、身近なもので練習してみたいと思います。
今回は、近所でお買い物をしたときのレシートから関係スキーマを書き起こそうと思いますよ（いわゆるボトムアップ設計というやつです）。
レシートを観察する
今回使ったリサイクルショップのレシート。雑貨しか買わなかったのですが、服やブランドバッグ、家電などもあります（命名規則はわかりません）。
エンティティを見出す
Udemyの講座「はじめてのテーブル設計・データベース設計【わかりやすい解説 + 身近なテーマでレッスン】」では、5W1H（いつ、どこで、だれが、なにを、なぜ、どのように）を考えてエンティティを見出すといいと言われています。
- いつ → 発行日
- どこで → 店名
- 誰が → 担当者
- 何を → 商品（品名）
- どれだけ（購入された商品について）
→ 数量、単価、金額 - どれだけ（レシート全体について
→ 合計、お預り、お釣り
関係スキーマを考える
いったん紙に書き出してみます。
消えかけている部分は「商品情報（商品コード（PK）、品名、単価）」です。
今回のお店はチェーン店なので、おそらく一括管理するシステムがあると想定し、複数の店舗情報を管理するための「店舗情報テーブル」を定義しました。また、各店舗のレジ担当者名がレシートに印字されているので、おそらく照合するための「社員情報テーブル」が定義されてそうだと考えました。
「購入情報テーブル」は今回の買い物の情報を保存しておくためで、「商品情報テーブル」は大量にある商品を管理しているテーブルだと考えます。購入情報テーブルと商品情報テーブルは多対1の関係になると思うのですが…… 例えば「アディダスのTシャツ」は何度も別々のレシートで買われる可能性があるとChatGPTからヒントをもらったのですが、このへんで混乱してきました。
「領収書テーブル」は今回の購入時の状況を全てまとめて控え、印字したい情報を持っているテーブルになります。領収書テーブルと購入情報テーブルは1対多の関係です。購入番号1つに対して商品は買った個数だけありますからね。
ChatGPTさんの意見
■ 良い点
5W1Hでの分解（いつ・どこで・だれが etc.） がとても良いアプローチ。情報を漏らさず、混乱も防げる。
正規化（第3正規形くらい）まで意識されてる構成になってる。
「領収書ID」 → 「購入情報（商品単位）」 の流れも自然。
■ 小さな改善提案（余力があれば）
「購入番号」は「領収書ID + 連番」などで構成して複合主キーにするか、逆に購入情報のIDを別で振ってもいい（正規化次第）。
「社員情報」が抜けているようなので、社員ID→領収書の関係も明示しておくとよいかも（FKだけ書いてあるね）。
商品単価は商品情報に一元化されてるけど、過去に価格が変わる可能性があるなら、購入情報に「単価」を残すのも検討価値あり。
複合主キーについて
ChatGPTさんに複合主キーをどういうときに使うのか聞いてみました。
ChatGPT『今回のレシート設計では、「購入情報」テーブルが複合主キーの良い適用例になっています。なぜか？
購入番号（レシートごとに発行される一意のID）
商品コード（商品を一意に識別するID）
→ この2つの組み合わせが「そのレシート内での購入記録」を一意に特定できるからです。
また、同じレシートで同じ商品を何回も登録する可能性があるか？ も考慮するポイントです。
「1レシートにつき、同じ商品は1回しか登場しない」 → 購入番号, 商品コード を複合主キーにする
「同じレシートで同じ商品が2回登場する可能性がある」 → purchase_detail_id のような別の主キーを作る』
今回は複数購入しても数量でまとめることができ、1レシートにつき同じ商品は1回しか登場しない（アディダスのTシャツを2枚購入するパターン）ので、購入情報テーブルの購入番号と商品コード を複合主キーにします。
答え合わせ
最終的に今回のレシートからデータベース設計をしたときの関係スキーマは以下の通りになりました。
店舗情報（店舗ID、登録番号、店名、場所、電話番号）
社員情報（社員ID、担当者名）
領収書（領収書ID、店舗情報ID、社員ID、購入番号、発行日、合計、お預り、お釣り）
購入情報（購入番号、商品コード、数量、金額）
商品情報（商品コード、品名、単価）
主キー：
店舗情報 → 店舗ID
社員情報 → 社員ID
領収書 → 領収書ID
購入情報 → 購入番号、商品コード（複合主キー）
商品情報 → 商品コード
外部キー：
領収書 → 店舗情報ID、社員ID、購入番号
おわりに
データベース設計をここまできちんと考えて、かつアウトプットしたのは初めてです。間違っているところがあればどんどん教えてください！！励みになります。よろしくお願いします。
表記のしかた
関係スキーマはデータベーススペシャリスト試験の表記法に従っています。
