DBの設計の方法が分かりません。

質問

今、Accessでシステムを作っているのですが、DBの設計をどうしたらいいか考えあぐねている部分があります。
長々と要件定義の内容をここに書いてもご迷惑かと思いますので、例え話で代用させていただきます。

「果物持ちより集会」というのがあって、人が様々な果物を持ちよります。
集会ごとに、集まった人の名前、果物の種類、数を登録していきます。

それを、縦軸→果物の種類、横軸→人の名前で集会ごとに帳票を作るというものです。

しかも、果物の種類は膨大です。集会がある前には何を持ってくるかは決定されますが、集会ごとにその対象は変わっていきます。人は１つの集会につき、だいたい２～１０人くらいに集まります。

こういう帳票を作ることは可能でしょうか？
テーブル構造をどうすればいいのか、頭の中で組み立てることができません。
小さなヒントでもいいので、どうかよろしくお願いします。

○帳票はこんな感じです。

帳票１：7/27 ○×集会
　　　　A子　B子　C子
りんご　3　　4　　5
なし　　4　　6　　1
みかん　2　　5　　4

帳票２：6/27 △△集会
　　　　A子　D子
りんご　4　　4
いちご　7　　8
みかん　1　　4
メロン　2　　1

回答No.3

rucifer · ベストアンサー

勘違いしていました。
各集会ごとに参加者は色々な果物を持ち寄るということですね・・。申し訳ありません。

集会テーブル（集会ごとに1レコード)
　集会番号
　日付

参加者テーブル
　集会番号 (集会テーブルを参照
　会員番号 (会員テーブルを参照

持ち寄った果物テーブル
　集会番号 (参加者テーブルを参照
　会員番号 (参加者テーブルを参照
　果物番号 (果物テーブルを参照
　個数

果物テーブル
　果物番号
　果物名

会員テーブル
　会員番号
　会員名

持ち寄った果物テーブルは、集会番号と会員番号と
果物番号を複合キーとします。

下記は持ち寄った果物テーブルの例です。

集会番号、会員番号、果物番号、個数
1, 1, 1, 5
1, 1, 2, 3
1, 2, 1, 5

結合すると、
1, Aさん, りんご, 5
1, Aさん, みかん, 3
1, Bさん, りんご, 5

となります。

SELECT * FROM 持ち寄った果物
WHERE 集会番号 = 1 AND 会員番号 = 1

とするとその集会1に参加した会員1さんが
持ち寄った果物と個数が得られます。

INT2はINTEGER型という意味です。

お礼

アドバイスありがとうございます！読んでてかなりスッキリしてきました！！
でも、ちょっと気になるのが、集会ごとに持ち寄る果物は決まっているので…
1, Aさん, りんご, 5
1, Aさん, みかん, 3
1, Bさん, りんご, 5
1, Bさん, みかん, 1
2, Aさん, いちご, 5
2, Aさん, みかん, 3
2, Cさん, いちご, 5
2, Cさん, みかん, 1
と集会ごとに果物の項目が同じものを繰り返してしまうのは、この仕様の性質上、仕方のないことですよね？
集会は月に300件！くらいあり、一回で集める果物は７，８種類、参加者は１つの集会につき、だいたい２～１０人くらいになるので、このテーブルのレコードは乗掛けでかなりの勢いで増えていきそうです。
確かにこの要件だとこれしか方法はないとは思うのですが…こういう設計って特に珍しくはないのでしょうか？

回答No.4

rucifer

集会ごとに持ち寄る果物は決まっている。
個数も固定であるならば、下記の設計でよいと思います。

会員テーブル
　会員番号
　氏名

果物テーブル
　果物番号
　果物名　

持ち寄る果物テーブル
　会員番号（会員テーブルを参照
　果物番号（果物テーブルを参照
　PRIMARY KEY (会員番号, 果物番号)　複合キー
　個数

集会テーブル
　集会番号
　開催日

参加者テーブル
　集会番号（集会テーブルを参照
　会員番号（会員テーブルを参照
　PRIMARY KEY (集会番号,会員番号) 複合キー

会員と果物の間に持ち寄る果物を定義しています。
持ち寄る果物テーブルには
Aさん, りんご、5個
Aさん, かき、３個
Bさん, りんご、２個
というデータが入ります。

あとから参加者を元に持ち寄る果物テーブルを
参照すれば、何を持ち寄ったか、がわかりますが、
持ち寄る果物を変更してしまうと、過去の集会の
整合性がとれなくなります、先ほどの設計では、
集会ごとに何を持ち寄ったかが分かるので、
持ち寄った果物を変更しても、過去分に影響は
与えません。

お礼

やはり、集会単位での
持ち寄った果物テーブル
　集会番号 (参加者テーブルを参照
　会員番号 (参加者テーブルを参照
　果物番号 (果物テーブルを参照
　個数
という情報は必要だと思います。
-----------------------------
1, Aさん, りんご, 5
1, Aさん, みかん, 3
1, Bさん, りんご, 5
1, Bさん, みかん, 1
-----------------------------
というレコードをどうやって
-----------------------------
帳票１：7/27 ○×集会
　　　　Aさん　Bさん
りんご　5　　　5　
みかん　3　　　1　
-----------------------------
と帳票に出すのかよく分からないのですが…（表の結合？）
アドバイスを元に取り合えずDBを作ってから試行錯誤してみようと思います。何度もありがとうございました。

回答No.2

rucifer

先ほど回答したものですが、若干間違っていました。
下記にDDLを掲載します。

CREATE TABLE 集会 (
集会番号 SERIAL NOT NULL,
日付 DATE,
PRIMARY KEY (集会番号)
);

CREATE TABLE 会員 (
会員番号 SERIAL NOT NULL,
会員名 VARCHAR(40),
PRIMARY KEY (会員番号)
);

CREATE TABLE 果物 (
果物番号 SERIAL NOT NULL,
果物名 VARCHAR(40),
PRIMARY KEY (果物番号)
);

CREATE TABLE 参加者 (
集会番号 INT2 NOT NULL,
会員番号 INT2,
果物番号 INT2,
個数 INT2,
PRIMARY KEY (集会番号)
);

ALTER TABLE 参加者
ADD FOREIGN KEY (集会番号) REFERENCES 集会 (集会番号);

ALTER TABLE 参加者
ADD FOREIGN KEY (会員番号) REFERENCES 会員 (会員番号);

ALTER TABLE 参加者
ADD FOREIGN KEY (果物番号) REFERENCES 果物 (果物番号);

お礼

再びありがとうございます。
DDLは勉強で習ったくらいで、実際に扱ったことがないので、よくは分からないのですが…。

「参加者」テーブルに
集会番号、会員番号、果物番号、個数　という構造を作っておいて、各項目を紐付けしていくっていうことでしょうか？「参加者」テーブルは果物の種類ごとに１レコードになるのでしょうか？（これもまた集会番号で主キーにはならないと思うのですが）

「INT2」がポイントになりそうですが、検索を掛けてもそれらしいものがヒットしませんでした。
考え方のヒント程度で構いませんので、文章でお話していただけるとありがたいです…。

回答No.1

rucifer

厳密にではありませんが、
下記のような構造でよいと思います。
帳票はこれらのテーブルから組み合わせて都合の
良いように作成できると思います。

果物テーブル
　果物番号（主キー）
　果物名

会員テーブル
　会員番号（主キー）
　会員名

集会テーブル
　集会番号（主キー）
　集会名
　集会日
　会員番号（外部キー）
　果物番号（外部キー）
　個数

お礼

早速の返信、ありがとうございます。
「集会テーブル」の構造について質問なんですが、「会員番号」や「果物番号」は一つの集会に対して複数項目ありますので、このテーブル構造にしてしまうと、集会番号で主キーにできないように思うのですが…。
私が「外部キー」の意味を取り違えていますでしょうか？

もしよろしければ、この部分を解説していただけないでしょうか？