PHP/MySql Test Application

It's rather a general, conceptual question, but I'm developing a test/quiz application at the moment, and want to

a) be able to change the questions/ answers / orders whenever I want

b) save every completed test, complete with report, in the database

I've built the back end (in CI) so there is a nice user friendly way to edit the questions, but I'm trying to think of the best way to save the completed tests, without memory & space wastage.

Trouble is, if I change the test questions, I don't want them to alter the archived saved results that people have tak开发者_开发问答en in the past. So I'm thinking I need to serialize every single question when someone takes a test, so that you can view the old 'report cards' with legacy questions on them, even if those questions no longer exist.

This seems bulky though, with 40+ questions needing to be saved for every test someone takes. Anyone have a better idea?

Thanks!

---

If the questions and the order of questions are changed occasionally, then I suggest to do the following.

In the Question table, add the "version" field. Each time you need to change a question, just save it with the same reference but with another version number. In the Answer table, save the reference of the question and its version. Alternatively, you can save the version of the questions in an extra table.

About the order of questions: if the order is not changed very often or if the order is changed at the same time than a question is changed, then you can save the order number in the Versioning table. Otherwise, the real function place for a question order may be in a Versioning table of the Answers. It's like the versioning table of the questions, but it will save the order of the questions with a Answer id, and the Answer table will have an extra "version" field.

Illustration:

[t_test]
- t_id
- t_title

[t_question]
- q_id
- test_id
- q_qood_answer
- q_current_version (instead of this field, you can choose to always take the last version)

[t_question_version]
- q_id
- q_version
- q_text
- q_order

[t_answers]
- a_id
- q_id
- q_version
- a_answer