help with database design and application logic

I'm trying to set up a database for multiple choice test that will be taken by multiple users. And I am having a difficulty setting up the database and the application logic.

This is how I see the application should behave:

1. A user s开发者_运维技巧tarts the test and answers as many questions as they want (questions should be picked randomly).
2. The questions that the users see should not be the ones that were answered correctly before by the same user.

Here is how I set up the database:

So my questions are:

1. Is my database set up correctly so that it satisfies the above requirements?
2. What is the best way to show questions to the users (keeping in mind the above requirements)? Should I get all the questions that were answered correctly in an array and then all the existing questions in another array, and the exclude the first array from the second, and shuffle the resulting array? I don't think this way is efficient at all.

Thanks

Your schema will almost work, as long as you only have one test, and as long as you don't want to record the users' answers, just whether they got it right or not. One issue that I see is that I can't see where the right answer is recorded. How will the system know if the user picks the right answer?

Also, some people might be a little concerned about your questions table having unnormalized answers. Another way to do this would be to have an answer table like this:

answers
( id,
  question_id,
  sequence,
  answer_value
)

The advantage of this is that you can have any number of answers per question and you don't need to either make up extra wrong answers or change your system should a question with more answers be required.

Note too that if you use this approach then your question table should also have an indication of which answer is the correct one, such as "correct_answer_id" - This type of relationship is tricky to manage if you have referential integrity implemented in the database, due to the chicken and egg problem, so you're going to need some application code to ensure that every question has an answer and that it's answer is not for a different question!

You didn't mention this in your post, but stats.is_answered_correctly allows you avoid showing users questions that they got wrong, as well as questions that they got right. If you only want to know which questions the user got right and are prepared to show them the same question many times until they get it right, then you don't really need the "is_answered_correctly" attribute. Instead the mere existence of the intersection record tells you that the question was answered correctly.

In terms of picking questions, there are two ways you could go about it. If you have many more questions than any single user will ever answer, then you could just pick a question randomly, check to see if they happen to have answered it, and if so, go back and get a different one. If the question set is much larger than the answer set there will be a very low probability of collisions and you should be fine.

If your question set is not much bigger than your answer set, you could instead make a work list of unaswered questions. This would involve an intersection table containing a pairing of the user with every question. You would build this once when the user first signs up. When you ask a question you pick it randomly from the questions related to that user in this work list table. When the user gets a question right, you delete the intersection record so that question won't be asked again. This is pretty efficient, but it will require a database call to count the number of unanswered questions for your particular user prior to randomly selecting one from the work list table.