一个测验的MySql数据库设计

I'm making an online quiz with php and mysql and need a bit of help deciding how to design the database for optimal insert of questions/answers and to select questions for the quiz. The table will hold 80 questions each with 4 possible options plus the correct answer.

我正在使用php和mysql进行在线测验,需要一些帮助来决定如何设计数据库以优化插入问题/答案并为测验选择问题。该表将分别包含80个问题,包括4个可能的选项和正确的答案。

When retrieving the questions and options from the database I will randomly select 25 questions and their options.

从数据库中检索问题和选项时,我将随机选择25个问题及其选项。

Is it better to make a single column for all questions, options, and correct answers? For example:

为所有问题,选项和正确答案制作一个专栏是否更好?例如:

ID | Q | OPT1 | OPT2 | OPT3 | OPT4 | ANS

Or would it be better to make a column for each individual question, option, and correct answer? For example:

或者为每个问题,选项和正确的答案制作专栏会更好吗?例如:

Q1 | Q1_OPT1 | Q1_OPT2 | Q1_OPT3 | Q1_OPT5 | Q1_ANS | Q2 | Q2_OPT1 | Q2_OPT2...

3 个解决方案

#1

It'd be better to store the possible answers in a seperate table. This allows you to have any amount of answers per question instead of just 4. It also allows questions to have a different number of answers. If you have more than one quiz, you may also want a Quizes Table.

最好将可能的答案存储在单独的表中。这使您可以在每个问题中获得任意数量的答案,而不仅仅是4.它还允许问题具有不同数量的答案。如果您有多个测验,您可能还需要一个Quizes表。

Quizes:
  id
  name

Questions:
  id
  quiz
  prompt

Answers:
  id
  question
  prompt

QuizResult (someone taking a quiz)
  id
  quiz
  // other information about the quiz taker, possibly including the time

Now the correct answer thing gets a lot more tricky. I prefer the higher implementations here:

现在正确答案的事情变得更加棘手。我更喜欢这里更高级的实现:

Each question has a value and each answer has value

每个问题都有一个值,每个答案都有价值

A system I recently worked with you could assign a point value for each question and each answer. Incorrect answers often got 0, correct answers got the full amount. You could also have partially-correct answers using this method. This is the method I would go with.

我最近与您合作的系统可以为每个问题和每个答案分配一个分值。不正确的答案通常得到0,正确的答案得到了全额。您也可以使用此方法获得部分正确的答案。这是我要采用的方法。

You could go and say every question is worth 10 points or you could assign different weights to different questions:

你可以去说每个问题值10分,或者你可以为不同的问题分配不同的权重:

Questions:
    id
    quiz
    prompt
    value (you can make this question worth more or less)

  Answers:
    question
    prompt
    value (you can make this answer worth more or less)

Store the correct answer in the Answers Table

将正确答案存储在答案表中

A more simple (but less robust) solution is to simply say which answer is correct in the Answers table.

一个更简单(但不太健壮)的解决方案是在Answers表中简单地说出哪个答案是正确的。

Answers:
    question
    prompt
    is_correct

Store the correct answer in the Questions Table

将正确答案存储在问题表中

I wouldn't recommend it. When you create a question, it won't have a correct answer until you insert one. This means at least 3 queries to correctly make a question. If you use foreign key dependencies, this will quickly get annoying.

我不推荐它。当您创建问题时,在插入问题之前,它将没有正确的答案。这意味着至少有3个查询才能正确提出问题。如果您使用外键依赖项,这将很快变得烦人。

#2

Go with option 1 where you are having one row for each question/options/answer.

使用选项1,每个问题/选项/答案都有一行。

Option 2 does not make any sense. Every time you want to add/delete a question you'll be modifying the database schema!! And you'll have just one row always !!

选项2没有任何意义。每次要添加/删除问题时,您都将修改数据库模式!你总会有一排!!

#3

Go for your first option. It is the most normalised option, but that isn't necessarily a clinching argument. But the virtues of the normalised design are manifold:

去寻找你的第一个选择。这是最正常化的选择,但这不一定是一个令人讨厌的论点。但规范化设计的优点是多方面的:

• it is a piece of cake to include new questions into your quiz portfolio. (The other option requires adding new columns to the table).

将新问题纳入您的测验组合中是一件小事。 (另一个选项需要向表中添加新列)。

• it is simple to write the select statement which returns the result set. (the alternative option requires a dynamic SQL)

编写返回结果集的select语句很简单。 (替代选项需要动态SQL)

• it is easy to write a GUI which displays the questions and answers, because each displayed set of text maps to the same coilumn_names.

编写显示问题和答案的GUI很容易,因为每组显示的文本都映射到相同的coilumn_names。