ある要求を満たすデータベースを設計したい

Question

要求を満たすテーブルを作成して、捜査をsqlで実行したいのですが、うまく正規化できません。具体的にどのようにテーブルを作成すればいいのでしょうか？

【要求】
ある会員制サイトの売上を考える。

• 会員登録に必要なのは名前、年齢、メアド、住所
• 客がどういったものを一緒に購入しているのかを見たい
• 月ごと、週ごと、日毎の売上を見たい
• 客ごとの購入履歴も見たい
• 商品は複数業者から仕入れている
• 業者ごとの売上も見たい
• 業者の情報としては名前、住所がある

＊必要な情報は任意で指定して（名前:けい、たかし..など、商品:a、b..）考えています。

Answer 1

最低限だとこんな感じでしょうか。
在庫の管理の仕方が工夫のしどころですね。
商品テーブルに仕入れ数を持たせて販売履歴テーブルとの計算で在庫数を出せるようになっていますが、
商品テーブルは商品仕入れマスターにして、商品ひとつづつをレコードとする というような管理も可能かと思います。

[会員テーブル]
会員ID
名前
メールアドレス（ユニーク）
住所

[商品マスターテーブル]
商品ID
商品名

[業者テーブル]
業者ID
業者名
業者住所

[商品テーブル]
商品ID
商品マスターID
業者ID
仕入れ価格
販売価格
仕入れ日時
仕入れ数

[販売履歴テーブル]
商品ID
会員ID
販売日時

Comments

[Kei227]

回答ありがとうございます！

商品idと商品マスターidの違いを教えていただきたいです。

また、正規化する際の思考プロセスもおしえていただけないでしょうか？

よろしくお願いいたします。

[tanat]

商品IDと商品マスターIDの違いですが、
同じ商品であっても違う業者から仕入れる可能性がある＆同じ業者でも仕入れるタイミングや数にによって値段が異なる可能性があるため、別テーブルとしました。
商品テーブルは商品仕入れテーブルとして、商品IDを商品仕入れIDとした方がわかりやすいかもしれませんね。

正規化というかデータベースを設計する際の思考プロセスですが、
これは正規化の概念について知識で知った後に、実際に設計を重ねていくことで培われる部分も多いので説明が難しいところではあります。

基本的には、まずはとりあえず表（実際に必要な帳票）や画面、具体的に必要な要求（今書かれている要求よりももっと具体的に、「客ごとの購入履歴をこれこれこういう形式でこういう条件で絞り込んで見たい」）を作ってみて、
それを実現するために必要な情報をリストアップして、管理したい単位でテーブルを作り、
レコードを追加する時に具体的な値が何度も現れたらそれは多分正規化の対象です。
異なる帳票で重複する具体的な値が出てきたら、それも多分正規化の対象です。
という感じでしょうか。

例えば、販売記録として
商品名
販売価格
日時
購入者氏名
購入者メールアドレス
販売金額

という具体的な帳票（レシートや納品書）が必要だとした場合、
少なくとも
購入者氏名と購入者メールアドレスは会員リストという帳票にも存在しているはずなので、購入者IDになっていた方が嬉しいはずです。

という感じでしょうか。

このあたりはあまり近道はないので、基本的な教科書と演習問題をこなして、アプリケーションも作ってみて、知識としての正しさを確認してという繰り返しで身に着けていくのが良いかと思います。

[Orlofsky]

正規化は
http://www.kogures.com/hitoshi/webtext/db-seikika/
http://www.atmarkit.co.jp/ait/articles/1109/07/news124.html
http://sql.main.jp/cont/norm/map.html
などを参考に。
正規化がきちんと出来ていない仕事先に数ヶ月助っ人をやったことがありますが、3層くらいのインラインビューは当たり前、頻繁に副問合せを使わないといけなくて、超職人的なSQL, PL/SQLを書かなければならなくて、このシステムを維持する人たちの苦労が忍ばれました。

同じ列名なのにテーブルが違うとデータ型や桁数が違っていたため、SQLで暗黙の型変換が起きてパフォーマンスが落ちたり、桁あふれが起きたりしないよう、ERツールも使われた方が良いです。
http://thinkit.co.jp/cert/tech/31/2/2.htm