Database structure for hierarchical quiz?

Question

I'm doing a personal project where I have a quiz with a number of boolean questions. Each question will have a two answer choices "true" and "false". Based on the selected answer, a new question will be asked with the similar pattern and based on that an another question. Any question may have any or no number of sub-questions.

The last questions in the tree (E, D) and questions with no sub-questions (F) will also store the correct answer from either "true" or "false".

The alphabets A,B,C,D,E,F are questions. A quiz can have any number of master questions (like A), sub-questions like (like B,C,D,E) and individual questions (like F).

Kindly help me design a simple database schema for for this kind of quiz. I'm open to both relational (mysql) and document (mongodb) databases.

concept diagram

• DForck42: this doesn't help you writing the code, but tools such as Survey Gizmo and Survey Monkey can do this for you, if that's an option.
• Navi: what doesn't help? Survey Monkey or any other quiz platform outside is not an option.

Answer 1

In relational database, you should create a table called Questions which should contain following fields:

1. ID
2. QuestionString
3. QuestionType (Master, Individual, sub-question)
4. TrueCase (here you should store the ID of the next question. Since we'll store all questions in this table, therefore each questions shall have unique ID)
5. FalseCase (ID of the question to ask if answer is not correct. If ID is null, then you should exit)

Hope it helps.

Answer 2

 * User
 - user_id auto integer
 - regtime datetime
 - username varchar
 - useremail varchar
 - userpass varchar
* Question
 - question_id auto integer
 - question varchar
 - is_active enum(0,1)
* Question_choices
 - choice_id auto integer
 - question_id integer
 - is_right_choice enum(0,1)
 - choice varchar
* User_question_answer
 - user_id integer
 - question_id integer
 - choice_id integer
 - is_right enum(0,1)
 - answer_time datetime

• András Váczi: Please explain it a bit and show the relations between the tables.

Answer 3

I have an idea about mongoDB.

You can pre-create the quiz by using as field names the correct answer (true or false). When the player gives an answer you will have to check if the field exists. An example document is the below one:

{Question:"":{true:{Question:{"text goes here",false:{}...}}}

So if he answer true the first in order to validate you have to check if the document Question.true exists. If is the correct answer was true and the next answer is false you have to check if Question.true.false exists...

• Navi: Thanks Antonis, I didn't get what you said after reading for the first time. I'll spend some more time analyzing your suggestion.
• Antonios: No worries, i am much better with drawing than writing. In general the idea is nested documents and for field name of each nested document use the correct answer of the previous question. Its like following a hierarchical tree with two branches (true,false) but always only the "correct branch" is present. We can discuss this further on the chat if you like.
• PirateApp: thats fine but it seems mongodb is not the right database to get 10 random quiz questions for a user without repetition till all options are exhausted