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 one MySQL table which contains:

  • a question
  • 4 possible answers separated by a comma
  • a number (0-3) representing the correct answer.
  • unique id

with about 2000 entries.

I also have another table of users. The goal of each user is to solve all of the questions on the website.

I need to be able to randomly pick about 30 questions that the user hasn't answered yet.

How do you suggest I should store data about the questions the user has already solved?

At first I thought about a table with | user_id | - | question_id | (so each time the user solves a question correctly a new row will be added), but I don't know how I'll be able to pick 30 rows that he hasn't answered yet..

share|improve this question
    
Take a look at this: dba.stackexchange.com/questions/20211/… –  Emmad Kareem Jul 13 '12 at 22:16
    
Thanks! But I'm still not sure about how will I be able to randomly select unanswered questions with that database schema. –  Daniel Jul 14 '12 at 5:59
    
Assuming you will use a model such as the one hinted at by the comment above, you first get all un-answered questions, place their ids in an array (0 to 29) and use a random search function to pick as many random entries from that array as you want. For each random entry found, you get the corresponding question from the Question table. –  Emmad Kareem Jul 14 '12 at 9:21

1 Answer 1

Considering that the question is labeled database-design I will offer a solution without that CSV field. Even if you decide to keep it, this may offer some ideas.

  • Question contains all possible questions.
  • Answer has all possible answers (to any question)
  • QuestionAnswer contains all QA combinations offered, note that there must exist a default answer for "I do not know".
  • UserQA contains questions user answered.
  • Note that rule "There can be one and only one correct answer for each question" (IsCorrect = 'Y'), has to be implemented on the application level or as a stored procedure/trigger.

enter image description here

To get questions that a user with specific_UserId has not yet answered

select  qq.QuestionID
from Question as qq
where not exists (select 1 
                    from UserQA as xx 
                   where xx.QuestionID = qq.QuestionID
                     and xx.UserID     = specific_UserId );

Or, better if there may be some new questions which do not have defined answers yet

select distinct qq.QuestionID
from QuestionAnswer as qq
where not exists (select 1 
                    from UserQA as xx 
                   where xx.QuestionID = qq.QuestionID
                     and xx.UserID     = specific_UserId );
share|improve this answer

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.