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 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. – NoChance Jul 14 '12 at 9:21

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

