In an online questionnaire, what is a best way to design a database for keeping track of users all attempts?
- by user1990525
We have a web app where users can take online exams.
Exam admin will create a questionnaire. A questionnaire can have many Questions. Each question is a multiple choice question (MCQ).
Lets say an admin creates a questionnaire with 10 questions. Users attempt those questions. Now, unlike real exams users can attempt single questionnaire multiple times. And we have to keep track of his all attempts.
e.g.
User_id Questionnaire_id question_id answer attempt_date attempt_no
1 1 1 a 1 June 2013 1
1 1 2 b 1 June 2013 1
1 1 1 c 2 June 2013 2
1 1 2 d 2 June 2013 2
Now it can also happen that after user has attempted same questionnare twice, admin can delete a question from same questionnaire, but users attempt history should still have reference to that so that user can see his that question in his attempt history in spite of admin deleting that question.
If user now attempts this changed questionnaire he should see only 1 question.
User_id Questionnaire_id question_id answer attempt_date attempt_no
1 1 1 a 3 June 2013 3
Also, after this user modified some part of question, users attempt history should show question before modification while any new attempt should show modified question.
How do we manage this at the database level?
My first gut feeling was that,
For deletes, do not do physical delete, just make a question inactive so that history can still keep track of users attempt.
For modifications, create versions for questions and each new attempt refres to latest version of each question and history keeping reference to version of question at attempt time.
