Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 :)

share|improve this question
    
Have you attempted anything? Had any problems doing this? Do you have any reason to not try your solutions? Asking an open ended question like this will not get you a great answer. –  Max Vernon Nov 14 '12 at 20:14
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.