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:
original_question_id (FK to question.question_id)
(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:
original_question_id) and text into
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
If you want to have everything in a single table, you could do it more simply like this:
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
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.
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:
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
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_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:
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;