I am little stuck with the database design for my quiz app. Kindly guide me on this -
我很少被我的测验应用程序的数据库设计所困扰。请指导我 -
My app UI will go something like this -
我的应用UI将会是这样的 -
My DB Tables -
我的数据库表 -
User -
用户 -
user_id
user_name
user_email
user_password
user_registration_date
user_active
Question -
题 -
question_id
question_title
question_category_id
question_level_id
question_author_id
question_status
question_create_date
Category -
类别 -
cat_id
cat_name
cat_author
cat_create-date
cat_status
Level -
等级 -
level_id
level_name
Answer -
答案 -
?????????
Queries -
查询 -
I am stuck with the answer table..means I am not sure what columns to be set in answer table with the data type for this.
我被困在答案表中。意思是我不确定在答案表中设置哪些列的数据类型。
MY answer will only going to be in option format means 1,2,3,4 options or true false in an option NO text type answer ..just a choice selection.
我的答案只会在选项格式中表示1,2,3,4选项或选项中没有文本类型答案的真假。只是选择选项。
Kindly guide me as I am stuck with this.
请指导我，因为我坚持这个。
Please signify if there is something wrong in the above mentioned tables as well and let me know if crating a separate table for Level is OK or its a bad (Level will only be - Beginner, Intermediate, Advance).
请在上面提到的表中表示是否有问题，并告诉我是否为单独的表创建Level是好的还是坏的（Level只会是 - Beginner，Intermediate，Advance）。
2 个解决方案
#1
4
In the past when I have done this I have handled answers in this way:
在我做这个的过去，我已经用这种方式处理了答案：
Table: QuestionAnswers
Fields: ID, QuestionID, AnswerText (string), AnswerCorrect (bool)
表：QuestionAnswers字段：ID，QuestionID，AnswerText（字符串），AnswerCorrect（bool）
Table: QuestionAnswerResponses
Fields: ID, QuestionAnswerID (linking to the ID table above), Userid, AnswerSelected (bool), TimeAnswered (datetimr).
表：QuestionAnswerResponses字段：ID，QuestionAnswerID（链接到上面的ID表），Userid，AnswerSelected（bool），TimeAnswered（datetimr）。
#2
1
First, it's kind of redundant to repeat the name of a table in every field contained in the table. So I'd suggest not doing that. The only possible exception is the primary key/auto-increment field, as some people find it confusing or extra work to have to type myTable.id when two tables both have an ID field. I'm not sure why myTable.id is more work than myTable_id, but there you have it.
首先，在表中包含的每个字段中重复表的名称是多余的。所以我建议不这样做。唯一可能的例外是主键/自动增量字段，因为有些人发现当两个表都有ID字段时必须输入myTable.id会造成混乱或额外的工作。我不确定为什么myTable.id比myTable_id更有效，但是你有它。
What I've used in the past is
我过去使用的是
table Distractor (which is what eLearning types call the available answers for a question)
id
question_id (FK to the question table)
text (text to display on the screen for that answer)
isCorrect
displayOrder
表Distractor（这是电子教学类型调用问题的可用答案）id question_id（FK到问题表）文本（该答案的屏幕上显示的文本）isCorrect displayOrder
If you want the flexibility of reusing answers, you'd want to go with a junction table instead of having the question id in the distractors table. However, since you want to nail down the creation date of the question, you probably want to keep the answer strictly pointing to one question.
如果您希望灵活地重用答案，那么您需要使用联结表而不是在distractors表中使用问题ID。但是，由于您要确定问题的创建日期，您可能希望将答案严格指向一个问题。
#1
4
In the past when I have done this I have handled answers in this way:
在我做这个的过去，我已经用这种方式处理了答案：
Table: QuestionAnswers
Fields: ID, QuestionID, AnswerText (string), AnswerCorrect (bool)
表：QuestionAnswers字段：ID，QuestionID，AnswerText（字符串），AnswerCorrect（bool）
Table: QuestionAnswerResponses
Fields: ID, QuestionAnswerID (linking to the ID table above), Userid, AnswerSelected (bool), TimeAnswered (datetimr).
表：QuestionAnswerResponses字段：ID，QuestionAnswerID（链接到上面的ID表），Userid，AnswerSelected（bool），TimeAnswered（datetimr）。
#2
1
First, it's kind of redundant to repeat the name of a table in every field contained in the table. So I'd suggest not doing that. The only possible exception is the primary key/auto-increment field, as some people find it confusing or extra work to have to type myTable.id when two tables both have an ID field. I'm not sure why myTable.id is more work than myTable_id, but there you have it.
首先，在表中包含的每个字段中重复表的名称是多余的。所以我建议不这样做。唯一可能的例外是主键/自动增量字段，因为有些人发现当两个表都有ID字段时必须输入myTable.id会造成混乱或额外的工作。我不确定为什么myTable.id比myTable_id更有效，但是你有它。
What I've used in the past is
我过去使用的是
table Distractor (which is what eLearning types call the available answers for a question)
id
question_id (FK to the question table)
text (text to display on the screen for that answer)
isCorrect
displayOrder
表Distractor（这是电子教学类型调用问题的可用答案）id question_id（FK到问题表）文本（该答案的屏幕上显示的文本）isCorrect displayOrder
If you want the flexibility of reusing answers, you'd want to go with a junction table instead of having the question id in the distractors table. However, since you want to nail down the creation date of the question, you probably want to keep the answer strictly pointing to one question.
如果您希望灵活地重用答案，那么您需要使用联结表而不是在distractors表中使用问题ID。但是，由于您要确定问题的创建日期，您可能希望将答案严格指向一个问题。