Architecture of a Quiz Database
So you’re wanting to build a quiz database so you can keep track of the questions, answers, validate those answers, and save them. The important matter to consider is the relationships between each of the tables.
table_questions
+ question_id
– quiz_id
– question_text
The questions table contains a foreign key to the quiz table. There is one quiz and many questions. The questions table is very simple, needing only the primary key, foreign key, and the question text. If you wanted to add an order for the question, you could add another field here.
table_quizzes
+ quiz_id
– quiz_name
The quiz table is fairly straightforward. This could be considered the core database.
table_answers
+ answer_id
– answer_text
– question_id
– correct (bool)
The answers table is getting a little more complicated. It does relate indirectly to the quiz table, because each quiz has many questions and each question has many answers. This architecture is well-suited to a multiple choice test, but perhaps less so for fill_in_the_blank questions. It would be possible, if your questions are all only true/false to put a key in the questions table to indicate this and/or to give the correct answer.
table_user_quizzes
+ user_quiz_id
– user_id
– exam_id
– question_id
– answer_id
The final table is just an intersection table between the other tables. The main thing is that it saves the question and the answer given along with the user. Each user may have multiple quizzes and multiple questions per quiz, but only one answer per question. You could set up or remove a constraint if the user needed to have multiple selections, but that’s neither here nor there.
To determine if the question was answered correctly, you grab the answer_id and see if it was correct in the answers table.
This is the simple architecture for a database to track user’s questions and answers. I hope you will enjoy building your own as much as I have enjoyed working with the architecture.