Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to make application like testing system. Every question has one or many variant of answers (and one or many can be right). I apologize that tutors and students use my testing system. It means that tutor can make CRUD operations with subject, questions and answers. But in this case appeares one big problem as saving version. For example, I am a tutor and I decide edit some question or answer ( or may be several questions and several answers). When I enter as student, I want to get a list of questions in which I made mistakes but tutor already edited this question and I get wrong information.

Main question: How I can save old versions questions and answers?

enter image description here

Each test has questions and every question has naswers. Tutors can edit/add/delete any of those components. For example, he or she can modify one of the questsion's text, add several questions to a test and edit one of the answers to the question that has just been modified. Any hints how I can handle all this so that I could retreive a version of the test at the exact time in the past?

Here is what I have come up with so far. The solution is extremely complex and I feel there is a better way of doing that.

share|improve this question
    
Which DBMS are you using? In Oracle you could simply use a flashback archive to automatically store the history. –  a_horse_with_no_name Nov 5 '12 at 15:33
    
@a_horse_with_no_name I have a choice between mssql and mysql( –  Ray Nov 5 '12 at 15:36
add comment

2 Answers 2

up vote 2 down vote accepted

If a given test must be "frozen", then you should create a whole new test (questions, answers, etc) when the user wants to make changes. Rather than giving them an "edit" button on the question or answers, give them a "clone" button on the test.

The previous suggestion about marking the 'active' version is a good one.

EDIT:

I don't remember seeing your picture before, so I'll update based on it and your later comments. I would still suggest that you create a whole new test by "cloning" an existing one, but you could do that similarly to how your two *Log tables work, though with a little modification. How about this:

test (id), say one row.
question (id, text), 7 rows.
answer (id, text), 28 rows (4 for each question).

Note that there's nothing joining any of those together. Now...

test_question (test_id, question_id).  7 rows.
question_answer (test_id, question_id, answer_id).  28 rows.

Now, to change a question: issue a new test_id (+1 small row). Copy all the unchanged test_question and question_answer values for the new test (6+24 small rows). Add a new question row (+1) and copy the old question_answer to this new question (+4 small rows).

Most of your new rows are comprised of integers, so they'll be small and fast. You didn't copy any of the larger question/answer rows.

This also leaves you with the ability to find everything you need to know given just a test.id. With this type of versioning, you can say that Johnny took test #5, and know exactly what he saw, without any calculations, etc.

Hope that helps.

share|improve this answer
    
Ok, but it means that after every edit size of tables with tests, questions and answers will increase. And If I decide edit just in one test text of one question 7 times I 'll get 1 duplicate test records (nothing worth), 7*count qestions in this test( for example 7*20=140 after 8th edit 160) And if every question has for example 4 variant of answer count almost duplicate answers in table answer ((count question-1)*(variant of answer)* 7 = 19*4*7. –  Ray Nov 6 '12 at 6:56
    
I'll bet your tables are small, your user count is low, and you can archive old data out quickly. If you want to survive an audit, this is the way I'd go. –  Alain Collins Nov 6 '12 at 7:01
    
In my shema of db I try to make AnswerNodifLog Table and QuestionModifLog, and when student want to see passed test I get all modifications with this test as delete question, edit answer, and do it in business logic. How do you think it's better idea or you prefer yours? –  Ray Nov 6 '12 at 7:12
add comment

I've seen two common approaches to storing historical data: Storing history in a separate _history table, and storing it in the same table as the "current" record, but with a version number and maybe an indicator to show that it's historica data.

If you separate the history to a separate table you'd have something like this:

question
--------
    question_id (PK)
    question_text


question_history
----------------
    question_history_id
    original_question_id (FK to question.question_id)
    question_version_number
    version_timestamp
    question_text
    (PK of this table is composed of question_history_id, original_question_id, and question_version_number)

When you make an edit for a question with some question_id, you do it in two stages:

  1. insert into question_history the question_id (as original_question_id) and text into question_history. question_version_number and version_timestamp and question_history_id can be populated via a trigger.

  2. update the existing record in question based on the data from the user's edit.

In this situation, the "current" version is always in the main question table, and if you want historic data you have to query question_history.

If you want to have everything in a single table, you could do it more simply like this:

question
--------
    question_id (PK)
    question_text
    question_version_number
    version_timestamp

This simplifies your database structure and the user's edit operations, but querying for the most current question will now always require you to sort the questions by version_timestamp (or by question_version_number - truth is, you only really need one of these). If you have lots of history, this could become a performance issue. It's also probably a good idea to include question_version_number or version_timestamp in a composite primary key that includes question_id so that you can't have the same version number for two records for the same question.

As for answers:

You can apply the same pattern to answers that I applied to questions. Additionally, you'll probably want to keep track of which version of a question an answer applied to, so you'll need a question_version_id for each version of an answer. Example:

answers
-------
    answer_id (PK)
    answer_text
    student_id (FK to students - I assume you will need a way to know who wrote which answer)
    answered_question_id (FK to questions.question_id)
    answered_question_version_number (FK to questions.version_number or 
                                      question_history.version_number,
                                      depending on how you want to do it
                                       - you could also reference the version_timestamp
                                      if you prefer).

Now for the tests. This is where it could get tricky. You will want to version the tests, but does the version number of the test change every time one of the questions changes? That might be a good way to do it, though if someone makes minor edits to every question on a 50-question test, that might create too many versions. You could also try to only create a version of a test when a user explicitly clicks a "new version" button for the test, but if someone forgets it could screw things up in other ways. Or you could have all questions on one page so one single edit of the test will create one version but with all edits to all questions. That's something that's probably best controlled in the application.

I think the data structures will probably be the same though:

test
----
   test_id
   test_version_number
   version_timestamp

questions
---------
    question_id (PK)
    question_text
    question_version_number
    version_timestamp
    test_id (FK to tests)
    test_version_number (FK to tests.version_number - or you
                        could reference tests.version_timestamp if you prefer)

Now, say you want to get all answers for a particular version of a test for a particular student:

select answers.*
from answers
inner join questions
    on answers.question_id = questions.question_id
    and answers.answered_question_version_number = question.version_number
    and questions.test_id = $TEST_ID
    and answers.student_id = $STUDENT_ID
inner join tests
    on tests.test_id = questions.test_id 
    and questions.test_version_number = tests.test_version_number
    and tests.test_version_number = $TEST_VERSION_NUMBER;
share|improve this answer
    
Thanks, but I still have a problem. Each test has questions and every question has naswers. Tutors can edit/add/delete any of those components. For example, he or she can modify one of the questsion's text, add several questions to a test and edit one of the answers to the question that has just been modified. Any hints how I can handle all this so that I could retreive a version of the test at the exact time in the past? –  Ray Nov 5 '12 at 16:41
    
@Ray: Are you asking how to handle the situation when a student submits an answer to an early version of a question, and then a tutor creates a new version of the same question? –  FrustratedWithFormsDesigner Nov 5 '12 at 20:31
    
Not really. The history of the data is required to support a petition functionality. Students get the latest versions of tests. Some time after the test has been completed a user might submit a petition to disagree with results, however the test could have been changed a number of times since then. –  Ray Nov 6 '12 at 7:31
1  
@Ray: Ah ok so you need to be able to view a "snapshot" of a test, including the correct versions of questions and the answers to those questions at a specific point in time? –  FrustratedWithFormsDesigner Nov 6 '12 at 14:38
    
@FrustratedWithFormsDesigner : Thats what i want i.e to be able to view a "snapshot" of a test at a specific point in time. –  LivingThing Nov 13 '12 at 16:08
show 1 more 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.