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

Question

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?

Answer 1

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)

Answer 2

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 ;-)

Answer 3

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.

Answer 4

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.