Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question
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. –  Bart van Ingen Schenau May 24 '13 at 8:01
2  
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 '13 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 '13 at 20:33
    
One version is here: stackoverflow.com/questions/540885/… –  Michael Durrant Oct 22 '13 at 1:28
2  
Must you use a relational database? Have you considered a document database like RavenDB? ravendb.net –  Jim G. Dec 21 '13 at 4:52
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
1  
I think that when determining what answer a user gave, you actually have to search for the latest one (not the earliest). –  GlenPeterson Oct 21 '13 at 12:45
    
Good catch :) Have an upvote :D –  Matt D Oct 22 '13 at 0:42
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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