What is an accepted method of keeping old data in MySQL?

Question

I think this may be more of a programming question, but since it directly relates to MySQL, I wanted to ask it here.

Some background: I am running an application whereby users can take tests using data from two tables: questions and answers. Each response from every test is recorded in a test_data table. Now, over time, I will have questions that were either put in wrong, or are no longer relavant. How would I go about keeping that data somewhere in the database so that I maintain referential integrity?

I seem to remember using an application in the past that had an "archive" table with a few columns:

id type (describes what data is archived in this row) data (serialized PHP array) etc...

This could be considered a subjective question, yes. But what I'm looking for here are some possibilities that I can use to build an archiving system with in MySQL.

Thanks in advance :)

Answer 1

You could duplicate the table structure entirely, but under a different name, ie. questions_archive. Then move all of the now-irrelevant data into that table and only refer to it in your query via UNION with the original table if it is truly necessary. To maintain referential integrity you'll likely want to create a similar archive table for answers.

You should also be able to specify this table's data be stored on a higher-latency, higher-capacity volume [ie: spindle drive vs SSD, NFS share vs local, etc] if you are looking for further optimization for your non-archived data.

edit: Alternatively, partitioning.