Creating a database structure for a quiz

Question

My app has this menu:

1. TestMode - make a quiz with 26 rndom questions from the database with a timer of 30 minutes.
2. LearningMode - make a quiz with all the questions from my db, displaying the correct answers.
3. Statistics- display % of quiz passed and the questions with wrong answers (displaying the correct answers).

• each question have 3 choices(1 choice can be true, 2 choices can be true, or all can be true).
• question can have a picture or not.

For now my db structure looks like this:

enter image description here

I don't know if is better if I put answers in questions; and if I do that, how to store the correct answer.

• CodingYoshi: Is your requirement to store the wrong answers in the db or just in memory? In other words, do you need to store the results so they are available when the application restarts?
• cikicean: yes i need it after restart...

Answer 1

The correct answer is the property of the question.

Thus, the question table should be improved by a column named correct_answer_id, which will be a foreign key to answer(id_answer).

We can see some similar in the database of the Stack Exchange - here the accepted answers are in the AcceptedAnswerId column.

This structure has a possibility of inconsistence - nothing guarantees that the correct_answer_id refers to an answer which belongs to the current question. Ideally, you should use contraints to ensure that.

• Tom V: is it really? the question states that multiple choices can be correct
• peterh: @TomV Right, I think the answers need a boolean property showing if they are correct. I fix it soon.