How to Save Editable Data History in RDBMS

data-versioningdatabase-designdatabase-recommendationMySQLsql server

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.

Best Answer

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.

Related Question