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..

  • Take a look at this: dba.stackexchange.com/questions/20211/…
    – NoChance
    Commented Jul 13, 2012 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
    Commented Jul 14, 2012 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.
    – NoChance
    Commented Jul 14, 2012 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 );

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.