MySql Quiz Database Development

Question

I am doing an online quiz with php and mysql and need some help deciding how to design a database to optimally insert questions / answers and select questions for the quiz. The table will have 80 questions, each of which has 4 possible options plus the correct answer.

When I receive questions and parameters from the database, I randomly select 25 questions and their options.

Is it better to make one column for all questions, options and the right 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 is better to save the possible answers in a separate table. This allows you to get any number of answers to a question, not just 4. It also allows you to ask different answers. If you have more than one quiz, you may also need the Quites 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 right thing to answer is getting a lot more complicated. I prefer higher implementations here:

Every question matters, and every answer matters.

The system I recently worked with you can assign a point value for each question and each answer. Wrong answers often got 0, right answers got the full amount. You can also get partially correct answers using this method. This is the method I would go with.

You can go and say that each question costs 10 points, or you can 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) 

Save the correct answer in the answer table

A simpler (but less reliable) solution is to simply say which answer is correct in the "Answers" table.

 Answers: question prompt is_correct 

Save the correct answer in the question table

I would not recommend it. When you create a question, it will not have the correct answer until you enter it. This means at least 3 questions to correctly ask a question. If you use foreign key dependencies, this will quickly become annoying.

Answer 2

Go with parameter 1, where you have one line for each question / options / answers.

Option 2 makes no sense. Every time you want to add / remove a question, you will change the database schema! And you will only have one line!

Answer 3

Choose the first option. This is the most normalized option, but it is not necessarily an argument about the clinch. But the advantages of a normalized design are manifold:

• This is a piece of cake that includes new questions in your quiz portfolio. (Another option requires adding new columns to the table).
• just write a select statement that returns a result set. (alternative option requires dynamic SQL)
• It’s easy to write a graphical interface that displays questions and answers, because each set of text cards displayed matches the same coilumn_names.