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?

  • the design you are suggesting is simple and can solve your problem, and 1000s of users and 1000s of questions is not a problem in today's RDBMs, they can handle millions of records not just 1000s, what DBMS are you using
    – AmmarR
    Commented Jul 1, 2012 at 8:22

1 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


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

enter image description here

  • like the ER table you draw. I think this would useful for all SCHEMA design related questions.
    – zinking
    Commented Jul 2, 2012 at 7:03
  • @zinking, thanks for your kind comment. Some people are visual and find it easy to thing in terms of pictures. You are correct.
    – NoChance
    Commented Jul 2, 2012 at 9:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.