Save history editable data RDBMS

Question

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?

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.

Answer 1

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;

Answer 2

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.