MySql database design for a quiz

Question

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.

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

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

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.

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)

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

Answer 2

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

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

Answer 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)
• 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.