Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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 Jul 1 '12 at 8:22

1 Answer 1

up vote 3 down vote accepted

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

enter image description here

share|improve this answer
    
like the ER table you draw. I think this would useful for all SCHEMA design related questions. –  zinking Jul 2 '12 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. –  Emmad Kareem Jul 2 '12 at 9:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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