测验应用程序的数据库设计 [英] Database design for quiz application

问题描述

我正在为测验设计数据库，而且我被卡住了。我不知道该怎么办。



以下是相关信息：



玩家答案10问题。



每个问题有3个答案。只有一个答案是正确的，其他2个是错误的。



我需要数据库设计方面的帮助。



我尝试过：



这是我的设计：



表玩家：



ID bigint（主键，身份）

名称nvarchar（ MAX）



表问题：



ID bigint（主键，身份）

TextOfTheQuestion nvarchar（MAX）



表答案：



ID bigint（主键，身份）

TextOfTheAnswer nvarchar（MAX）

QuestionID bigint （Questions.ID的外键）

isCorrectAnswer位



表 PlayerChoices ：



PlayerID bigint（主键，Players.ID的外键）

AnswerID bigint（主键，Answers.ID的外键）

I am designing database for a quiz, and I am stuck. I do not know how to do it.

Here is the relevant information:

Player answers 10 questions.

Each question has 3 answers. Only one answer is correct, other 2 are wrong.

I need help with database design.

What I have tried:

Here is my design:

Table Players:

ID bigint (primary key, identity)
Name nvarchar(MAX)

Table Questions:

ID bigint (primary key, identity)
TextOfTheQuestion nvarchar(MAX)

Table Answers:

ID bigint (primary key, identity)
TextOfTheAnswer nvarchar(MAX)
QuestionID bigint (foreign key to Questions.ID)
isCorrectAnswer bit

Table PlayerChoices:

PlayerID bigint (primary key, foreign key to Players.ID)
AnswerID bigint (primary key, foreign key to Answers.ID)

推荐答案

嗯...有我会改变。

首先，为什么BIGINT？你打算有多少问题？

其次，为什么每个答案都有一点说这是正确的？

我的方式这是一个CorrectAnswerID作为问题的一部分，我将从问题的答案中删除链接，将其替换为三个索引到Answers。由于您针对每个问题修复了三个答案，因此以后更容易检查答案。你可以通过将它们用于几个问题来回收答案。

我还会添加另一个链接播放器，问题和答案的表格 - 这样你就可以记录他的结果。我可能还会在此表中添加日期，以便您的用户可以重复测试，可能会有不同的问题和/或答案。



除此之外，找出它是否有效的方法是尝试它。如有必要，在纸上执行您需要用户能够执行的所有操作并证明系统正常工作。如果您发现困难，那么这是重新思考设计元素的一个好点 - 尝试在提交实际数据库之前做到正确，事情应该更快。

Well...there are things I'd change.
First off, why BIGINT? How many questions are you planning of having?
Second, why have a bit associated with each answer which says "this is the right one"?
The way I'd do it is to have a "CorrectAnswer" ID as part of the question, and I'd remove the link from the answer to the question, replacing it with three indexes to Answers. Since you are fixed at three answers to each question, that just makes it easier to check answers later. And you can "recycle" answers by using them for several questions.
I'd also add another table linking Player, question, and answer given - so that you have a means of recording his results. I'd probably also add a date to this table so that your user can repeat the test, potentially with different questions and / or answers.

Other than that, the way to find out if it works is to try it. On paper if necessary perform all the actions you need the user to be able to perform and prove that the system works. If you find something difficult, then this is a good point at which to "rethink" elements of your design - try to get it right before you commit to the actual database and things should go faster.

这篇关于测验应用程序的数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！