Check if user completed all tests-CodePudding

I have multiple tests. Each user can take all these tests more than 1 time. But he has to complete all the tests, Before taking them again.

So if there are 5 tests, The user has to finish these 5 tests to be able to take them again later.

Tests table:

_________________
| id |   test   |
|____|__________|
|  1 |   test1  |
|____|__________|

Questions table:

____________________________
| id | question |  test_id |
|____|__________|__________|
|  1 |    q1    |     1    |
|____|__________|__________|

Answers table:

_______________________________
| id |  answer  |  question_id |
|____|__________|______________|
|  1 |    a1    |       1      |
|____|__________|______________|

I would then create a table to save user's answers:

User's answers table:

________________________________________________________
| id |  user_id  |  test_id  |  question_id | answer_id |
|____|___________|___________|______________|___________|
|  1 |      1    |     1     |       1      |     1     |
|____|___________|___________|______________|___________|

And to check if the user completed the test, I would count the user's answers for the test with id = x from table user_answers and compare them to the number of questions for this test from table questions. If the 2 numbers match, Then it's completed. If not, Then it's not completed.

But the issue is if the user answered all the questions for a test and then new questions were added to that test, It would be considered as not completed.

So how to check if the user answers all the test questions without considering the newly added questions nor deleted ones?

Please note: Each question is submitted individually, So the user doesn't have to submit all the questions at 1 time, Thus I can't depend on the date when the user answers a question

CodePudding user response：

You could add a new table which saves question_id user_id, and when a user answers a question you mark that question for him as not new

| question_id |  user_id | new_question

To know if all answers are answered, you exclude all questions that are marked as new

CodePudding user response：

add new table

user_tests [id, test_id, user_id, question_count, answer_count]

You can get count of uncompleted tests :

SELECT * 
FROM user_tests 
WHERE question_count != answer_count 
AND user_id = 11