Creating a database structure for a quiz

database-designsqlite

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.

Best Answer

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.

Related Question