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.


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

  • 3
    My gut feeling: treat questionnaires that have been public as immutable. Any change creates a new questionnaire that replaces the previous one in the UI. May 24, 2013 at 8:01
  • 3
    Try to keep behavior and naming consistent. Do not call an inactive field isDeleted. Call it what it is: inactive, and carry the principle forward in the UI so that confusion is mitigated. Do not label a command 'Delete' when it is not actually permanently removing it. To take it a step further, the attempt count and attempt limit are related to a 'threshold' concept, if you are searching for terminology.
    – JustinC
    May 24, 2013 at 20:24
  • 1
    Be cautious with the concept of versioning. Versioning often entails small, incremental changes or corrections. Replacement is just that: replacement. The point is you may want to consider the two concepts as separate from the user's perspective, but sharing in much of the same underlying process as you create and remove links within each questionnaire. They may want to leave a question unchanged in version, but replace it with something else.
    – JustinC
    May 24, 2013 at 20:33
  • One version is here: stackoverflow.com/questions/540885/… Oct 22, 2013 at 1:28
  • 2
    Must you use a relational database? Have you considered a document database like RavenDB? ravendb.net
    – Jim G.
    Dec 21, 2013 at 4:52

4 Answers 4


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.


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.

  • 1
    I think that when determining what answer a user gave, you actually have to search for the latest one (not the earliest). Oct 21, 2013 at 12:45
  • Good catch :) Have an upvote :D
    – Matt D
    Oct 22, 2013 at 0:42

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.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.