MySQL数据库设计测验 [英] MySql database design for a quiz

问题描述

我正在用php和mysql进行在线测验，需要一些帮助来决定如何设计数据库以最佳地插入问题/答案并选择测验中的问题.该表将包含80个问题，每个问题有4个可能的选项以及正确的答案.

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.

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

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

将所有问题，选项和正确答案都放在一栏中会更好吗?例如:

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...

推荐答案

最好将可能的答案存储在单独的表中.这样一来，每个问题您可以拥有任意数量的答案，而不仅仅是4个问题.还可以使问题拥有不同数量的答案.如果您有多个测验，则可能还需要一个Quizes Table.

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.

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:

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

我最近与您一起使用的系统可以为每个问题和每个答案分配一个点值.错误的答案通常为0，正确的答案为全部.使用此方法，您可能还会得到部分正确的答案.这就是我要使用的方法.

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.

您可以说每个问题都得10分，也可以为不同的问题分配不同的权重:

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

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)

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

一种更简单(但不那么可靠)的解决方案是在答案"表中简单地说出哪个答案是正确的.

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

Answers:
    question
    prompt
    is_correct

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

我不推荐.创建问题时，除非您插入一个问题，否则它不会有正确的答案.这意味着至少3个查询才能正确提出问题.如果您使用外键依赖关系，这将很快变得令人讨厌.

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.

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