问题描述
我正在为一个大学项目设计在线数学测验，但在设计数据库时遇到了一些麻烦。
网站的基本概念如下：
教师一旦注册，就可以登录并向其帐户添加问题。他们可以在选择问题的多项选择之间进行选择，或者选择是或否。他们还可以选择公开或私下提问。 （如果他们选择公开问题，其他老师可以查看问题。）
在任何时候，老师都可以使用其私人银行和/或公共银行的问题为学生创建测验。每个问题可以用于多个测验中。
这个想法是让学生稍后登录并进行测验；他们的答案会被存储，老师可以生成报告，并检查学生的个人成绩/得分最高和最低的问题等。
I am designing an online math quiz for a college project and having some trouble with designing my database.The basic idea of the website is as follows:A teacher, once registered may log in and add questions to their account. They can choose between making the questions multiple choice OR true or false. They can also choose between making their questions public or private. (If they choose to make the questions public, other teachers may view the questions.)At any time the teacher may create a quiz for their students using the questions in their private bank and / or questions from the public bank. Each question may be used in multiple quizzes.The idea is that the students will later log in and do the quiz; their answers are stored and the teacher can generate reports and check how the students did individually / highest and lowest scoring questions etc.
我在决定如何存储我希望有人可以帮助我的测验和问题。
到目前为止，我有以下内容：
I am having some trouble deciding how to store the quizzes and questions which I hope somebody may be able to help me with.So far I have the following:
Question表，具有以下属性： QuestionID ，SubjectArea，Concept，QuestionText，TeacherID， QuestionType，PublicYorN
‘Question’ table with attributes: QuestionID, SubjectArea, Concept, QuestionText, TeacherID, QuestionType, PublicYorN
MCQuestions表，其属性为： QuestionID ，AnsA，AnsB，AnsC，AnsD，AnsE，CorrectAns
‘MCQuestions’ table with attributes: QuestionID, AnsA, AnsB, AnsC, AnsD, AnsE, CorrectAns
'TorFQuestions'表，具有属性： QuestionID ，CorrectAns
‘TorFQuestions’ table with attributes: QuestionID, CorrectAns
'Quiz'表，具有属性： QuizID ，CreationDate，TeacherID
‘Quiz’ table with attributes: QuizID, CreationDate, TeacherID
我想我需要另一个表，如下所示：
'QuizQuestions'，唯一的属性是 QuizID，QuestionID ，它们共同构成了一个串联的主键。
I think I then need another table as follows:‘QuizQuestions’ and the only attributes will be QuizID, QuestionID which together make a concatenated primary key.
我觉得我应该有一个单独的表来存储问题的答案，不知道我是否需要像上面所做的那样将是非题与多项选择题分开。
I feel like I should have a separate table to store answers to questions and I'm not sure if I need to separate true or false questions and multiple choice questions as I have done above.
（显然还有其他包含用户数据的表，等等。是我关心的部分。）
任何建议/建议都将不胜感激！
(Obviously there are other tables containing user data etc. but this is the part I’m concerned with.)Any advice / input is greatly appreciated!
推荐答案
一个简单而又简单的方法灵活的设计应该是这样的：
A simple yet flexible design would be something like this:
- 问题表（ID，文本，正确答案ID，所有其他与问题相关的数据）
- 答案表（ID，问题ID文本以及所有其他与答案相关的数据）
- 测验表（ID，文本，所有其他与测验相关的数据）
- 测验问题表（测验ID，问题ID，问题显示顺序，也可能添加其他相关数据，例如问题权重）
- 测验结果表（测验ID，问题ID，答案ID，用户ID，所有其他相关数据，例如答案日期等）。
- Questions table (id, text, correct answer id, all the other question related data)
- Answers table (id, question id text, all the other answer related data)
- Quiz table (id, text, all the other quiz related data)
- Quiz questions table (quiz id, question id, question display order, other related data such as question weight might also be added)
- Quiz results table (quiz id, question id, answer id, user id, all the other related data such as date of answer and such).
这篇关于在线测验的数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！