テーブル設計で迷っています

質問

今、初級シスアドの試験の過去問をデータベースかする為、テーブル設計をしていますが、どうしてもうまく一元化できません。

システムの概要
・ユーザにアカウントとパスワードを作ってもらいログインしてもらう
・作成時に同じアカウントがある場合は別のアカウントにするよう指示を出す

・ログイン終了後、過去問（19年秋～16年春まで）を選ぶか、分野
ごとに分けた分野の問題20問1セット（データベースならデータベースだけの問題　など）を選ぶか選択してもらう。

・試験を開始して終了後、成績を分野別と総合で表示。間違って解答した箇所は解答と解説を表示

大まかなシステムの概要でしてテーブルは今こんな状況の設計です。

・アカウント
アカウント番号　アカウント名　パスワード　氏名

・成績
アカウント番号　日付　分野コード　配点　

・試験範囲
試験コード　試験名

例
0001　　19年春午前

・分野範囲
分野コード　分野名

例
0002　　データベース

・問題
試験コード　分野コード　問番号　選択肢（ア～エ）　解答　配点

・解説
試験コード　問番号　解説

一番問題なのは、成績・試験問題・解説・解答を管理するに当たって
どの試験のどの問題なのかを特定するのに試験コード・分野コードが
重複しすぎてるところが一番悩んでいます。

テーブルの設計に詳しい方教えていただけませんか？どうしてもこのシステムを完成させてシスアド受ける自分の為にも、これから受ける人の
為にも実装させたいです

回答No.7

kalkichi

遅くなりました。
1回の模擬試験が20問と限定されているのであれば、出題マッピングなくてもいいんですけどね。

・模擬試験
　・模擬試験ID
　・アカウントID
　・模擬試験日
　・Q1問題ID
　・Q1正誤
　・Q2問題ID
　・Q2正誤
　　～省略～

でもこれだと問題数変わったり、正解率を抜いたりする時に不便なので出題マッピングがあればいいと思いました。

ユーザーID 123の人が、5問の模擬試験を行うと・・・

・模擬試験
模擬試験ID | アカウントID | 模擬試験日
1　　　　　| 123　　　　　| 2007-01-02 03:04:05

・出題マッピング
出題マッピングID | 模擬試験ID | 問題ID | 正誤フラグ
1 　　　　　　　 | 1 　　　　 | 456 　 | 正解
2 　　　　　　　 | 1 　　　　 | 963 　 | 正解
3 　　　　　　　 | 1 　　　　 | 321 　 | 不正解
4 　　　　　　　 | 1 　　　　 | 147 　 | 正解
5 　　　　　　　 | 1 　　　　 | 789 　 | 不正解

こんな感じでレコードができます。

お礼

わかりました。必ずいいシステムを完成させますありがとうございました。

回答No.6

kalkichi

（U）は主キーとして見てください。
その他は関係を表してます（矢印の向き逆ですね・・・）

> これはただ単にユーザが行った試験の後を記録するものでしょうか？
そーですね。ユーザーが行った模擬試験の結果を保存する所になります。1回20問の模擬試験を行えば、模擬試験テーブルには1つのレコード、出題マッピングには20のレコードができます。
まずは、上から4つのテーブルだけで構築しログイン→模擬試験→結果＆解説発表だけのシステムを作られてはいかがでしょうか。のちのち下のテーブルを追加すれば、後でユーザーが見直しができたり、全体的に正解率の低い分野を抽出したり、データベースとして洗練されていくと思います。

補足

だいたい大方の背景は見えてきたんですけれど、
＞ユーザーが行った模擬試験の結果を保存する所になります。1回20問の模擬試験を行えば、模擬試験テーブルには1つのレコード、出題マッピングには20のレコードができます。
この部分の関連がいまいちまだよくわかりません。すいません
上側4つのテーブルの関連はすぐ見当がつくんですけど下側2つのが上側のデータがどう反映していくのかテーブルに値を入れた例で説明お願いできませんか？模擬試験と出題マッピングがどうなっているのかいまいち理解できていません。すいません

回答No.5

kalkichi

失礼しました。出題マッピングに、正誤フラグがいりますね。
アカウント XXXX の人が、模擬試験日 YYYY/MM/DD に行った問題と正誤の一覧データを抽出する場合

SELECT
　*
FROM
　(
　　問題マスタ INNER JOIN 出題マッピング ON (問題マスタ.問題ID=出題マッピング.問題ID)
　) LEFT JOIN 模擬試験 ON (出題マッピング.模擬試験ID=模擬試験.模擬試験ID)
WHERE
　模擬試験.アカウントID = XXXX
　AND 模擬試験.模擬試験日 = YYYY/MM/DD

補足

テーブルの項目の後についている（Ｕ）というのは主キーのことですか？（）の中の意味が勉強不足でわからないです。すいません
それと模擬試験と出題マッピングというのが何を管理しているのか
よくわからんいんですけれど、これはただ単にユーザが行った試験の後を記録するものでしょうか？

回答No.4

kalkichi

正規化の方法ですか。大ハズししてましたね。
ANo.2の補足の例もちょっと状況がわかりませんので、また外してる可能性も高いですがこんな設計ではどうでしょう。（サイト上で行う試験を実際の試験と分ける為、模擬試験としました。）

・アカウント
　・アカウントID(U)
　・アカウント名
　・パスワード
　・氏名

・実試験マスタ
　・実試験ID(U)
　・試験名

・分野マスタ
　・分野ID(U)
　・分野名

・問題マスタ
　・問題ID(U)
　・実試験ID（n→1 実試験マスタ.実試験ID）
　・分野ID（n→1 分野マスタ.分野ID）
　・選択肢（ア～エ）
　・解答
　・配点
　・解説

-- ユーザーが過去に行った模擬試験の結果を確認できるようにするには以下のDBも必要

・模擬試験
　・模擬試験ID(U)
　・アカウントID（n→1 アカウント.アカウントID）
　・模擬試験日

・出題マッピング
　・出題マッピングID(U)
　・模擬試験ID（n→1 模擬試験.模擬試験ID）
　・問題ID（1→1 問題マスタ.問題ID）

補足

この場合だと成績はどこで管理できるのですかね？

回答No.3

kalkichi

一元化とはもしかして1つのテーブルで全てまかなおうとしてるのでしょうか？せっかくリレーショナルデータベースを使うのであれば正規化してください。中黒が付いてる所でそれぞれテーブルにすればいい設計だと思いますが・・・。外してたらスミマセン。

補足

中黒のところがすべてテーブルです。その正規化がうまくいかなくて悩んでます

回答No.2

kalkichi

では問題ID（オートインクリメント）を作り、それをKEYに他のテーブルをリレーションさせるべきかと思います。

> どの試験のどの問題なのかを特定するのに試験コード・分野コードが
> 重複しすぎてるところが一番悩んでいます。

ちょっと困ってる所がわからないので、具体例などを挙げてもらえると助かります。

補足

ＫＥＹというのは主キーってことでしょうか？
困っているのはこのままのテーブルで行くと一元化されていない状態なので、更新・削除のときにエラーになるので、どうやったら成績と問題と解説のテーブルがうまく整理できるか悩んでいます。

例
試験範囲の試験コードを主キーとした場合、外部キーが問題と解説に今
出てるためこれだとだめです。

回答No.1

kalkichi

問番号というのが一意の番号なんですよね？それをKEYにするのではダメなのでしょうか？

補足

問番号というのは問1とかという意味です。
例　
問番号　　　問題文　　　　　　　　　　　　　　
　1.　　次のうち正解のものはどれか？

みたいな感じです