In an online questionnaire, what is a best way to design a database for keeping track of users all attempts?

Question

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

Let's 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 questionnaire 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 the modified question.

How do we manage this at 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.

Answer 1

If you just add the timestamp to the answer table, then you are either committing yourself to multiple queries, or to bringing back extra data when you only need the latest. Here is a sample query with a sub-query (which is slow):

select * from answer
    where question_id = ? and user_id = ?
        and update_time =  (select max(update_time) from answer
                                where question_id = ? and user_id = ?)

To avoid this, you can declare a separate table for history. For instance, if your latest answers are in the answer table, you could have an answer_history table which is exactly the same (both tables should have a last_updated_time timestamp column). This way you can store unlimited answer attempts and your queries against the latest answers will never slow down from history bloat, and never return extra records.

Not sure how far along you are with your design, but have you considered using Datomic? It stores all history by default and has the ability to query history in different ways. I don't think you have to declare any schema. It breaks data structures into their component parts on save and reassembles the ones you need on read. It's compatible with most JVM languages, though it was developed by the Clojure community. It's not SQL, and it's not the record-based No-SQL that is currently popular. Its design should be able to handle high loads comparable with SQL databases. By pulling the "transactor" apart from the rest of the database, it allows you to scale your storage engine on cloud instances that you spin up as needed to handle load.

Answer 2

A Few Things:

• Each answer gets a Date Stamp. The typical DD/MM/YY HH:MM:SS type stamp will do nicely. Ensure you index this field.
• When determining what answer a user gave, you search for the latest one.
• Each question gets a Start Date, and End Date stamp.
• You can now change questions and have the answers still be meaningful, as you can retrieve answers within the date range that's valid for that question.

For deletion, Simply marking that question as "Deleted" will suffice. And will keep the history regarding that question around for reporting purposes.

Answer 3

Regarding deletes, your suggestion is right. A common best practice is to use flags to declare a property. In this example in your question table (wherever you store questions) add a new field called isDeleted. Set it to 0 per default upon inserting a new entry and update to 1 when you're deleting a question.

Now to the modifications. Your suggestion seems right to me again (as far as I've understood your explanation correctly). You could do this by adding a new field to your question table called something like parent, which should refer to the id of the updated question. I strongly recommend you use foreign keys at this point, as this is essential to maintain data integrity.

Answer 4

Mark the records as inactive using an 'active' flag (set to false) and...

create database views that show all records, active records and inactive records (using that 'active' flag) and then use them in your application as desired.