designing server side database to store user-specific data

Question

I have a database with a number of quiz questions and answers. My basic design consists of these two tables

questions (question_id, question_string);

answers (answer_id, answer_string, correct_answer, question_id);

I would then like to keep track of user data, such as the number of times the user has seen a particular question. Assuming each user has their own unique ID, how could I go about designing a schema to store the data efficiently?

At the moment, I can only think of a table (user_id, question_id, number_of_times_seen). With thousands of users and thousands of questions, this table would grow rather quickly and might be a problem?

Answer 1

There are 2 scenarios to consider. The first is when you have the user take 1 or more questions without a meaningful context such an exam or formal quiz. In which case you solution works. However, the second scenario is about when the user takes a question as part of a formal session. In this case you have the following rules:

1. A user takes zero, one or more quizes
2. A quiz contains 1 or more questions
3. A user provides an answer for zero,1 or more specific question on a specific quiz

Note:

Table names may be different in the solution than from this diagram

A1 = Standard answer for question 1

UA1 = User provided answer for question 1