Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What is the good design of database schema for a multiple-choice quiz engine?

I have a project to create an asp.net mvc site to generate a quiz. Here is the specification:

  1. For each user visiting the site, she/he gets a quiz.
  2. Each quiz contains some multiple-choice problems.
  3. Each problem contains a question and 5 mutually-exclusive choices.

The simplest model I can think of is as follows:

    public class Problem
    {
        public int ProblemId { get; set; }
        public string Question { get; set; }
        public string A { get; set; }
        public string B { get; set; }
        public string C { get; set; }
        public string D { get; set; }
        public string E { get; set; }
    }

I am not sure it is good. Could you give me a suggestion for a better design?

like image 745
LaTeX Avatar asked Feb 04 '11 03:02

LaTeX


People also ask

What makes a good database schema?

A good database design is, therefore, one that: Divides your information into subject-based tables to reduce redundant data. Provides Access with the information it requires to join the information in the tables together as needed. Helps support and ensure the accuracy and integrity of your information.

How to create quiz in MySQL?

The very first step is to create the Quiz Database. It can be created using the query as shown below. CREATE SCHEMA `quiz` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; I have used the character set utf8mb4 to support a wide range of characters.

How do I create a multiple choice quiz in PHP?

php include 'quizclass. php'; $db = new Quiz(); $score = 0; foreach($_POST as $k=>$v) { $answer = $db->answer($k); if($answer[0][2] == $v) { // option is correct $score++; } } $score = $score / 4 *100; if($score < 50) { echo '<h2>You need to score at least 50% to pass the exam.


4 Answers

Properties A through E? No thank you! I'd lay out my tables as follows:

Quiz (int QuizId, ...)
Problem (int ProblemId, int QuizId, string Question)
Answer (int AnswerId, int ProblemId, int Index, string Answer)

The field names should be self-explanatory (Index is the sort index for the answers of a single question, if their order matters)

like image 142
Matti Virkkunen Avatar answered Oct 19 '22 08:10

Matti Virkkunen


Simple and intuitive designs are always the best and since they are really simple, we start doubting ourselves ;-). You are doing good except you can also store the correct answer with the Problem itself. Then it is no longer just a Problem. So now it is ProblemAndAnswer or QuizItem.

So this is all OK to store in a single table as multiple columns. But you also need to understand what it means. This means that you are making an assumption that a question is always going to have 5 choices. If you going to have less than 5 then it is ok as you can store nulls. But what if you are going to have more? This is when the single table model starts falling apart. You would now start thinking that a Question really can have 1 or more choices and would want to split into parent child tables....now you have made a well-informed decision ;-)

like image 44
Aravind Yarram Avatar answered Oct 19 '22 08:10

Aravind Yarram


The table Question has an id and a description, which is the text of the question ("Are cats secretly dogs?").

Edit: Additionally, the Question table has a correct_answer_id, which corresponds to the correct row in the Answer table.

The table Answer has a question_id, linking it back to its Question, and a description, which is the text of the answer ("It depends on the color of your cat.").

With this schema, questions don't have a hardcoded number of answers.

like image 44
ClosureCowboy Avatar answered Oct 19 '22 09:10

ClosureCowboy


I am working on same kind of database structure. It is better to have a choices table to insert choices against a specific question id. Quiz ID is a separate table that includes questionids and duration of test.

like image 33
bootabashir Avatar answered Oct 19 '22 09:10

bootabashir