Save history editable data RDBMS

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.

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:

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

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

StackExchange Database Administrators Q#28195, answer score: 3