How to archive changes to table rows?

Question

I have a MySQL database where I am storing information that is entered from a PHP web page. I have a page that allows the user to view an existing row, and make changes and save them to the database. I want to know the best way to keep the original entries, as well as the new update and any subsequent updates.

My thought is to make a new table with the same columns as the first, with an additional timestamp field. When a user submits an update, the script would take the contents of the main table's row, and enter them into the archive table with a timestamp when it was done, and then enter in the new values to the main table. I'd also add a new field to the main table to specify whether or not the row has ever been edited.

This way, I can do a query of the main table and get the most current data, and I can also query the archive table to see the change history. Is this the best way to accomplish this, or is there a better way?

Answer 1

You can use triggers on update, delete, or insert to keep track of all changes, who made them and at what time.

Answer 2

Lookup database audit tables. There are several methods, I like the active column which gets set to 0 when you 'delete' or 'update' and the new record gets inserted. It does make a headache for unique key checking. The alternative I've used is the one you have mentioned, a separate table.

As buckbova mentions you can use a trigger to do the secondary insert on 'delete' or 'update'. Otherwise manage it in your PHP code if you don't have that ability.

Answer 3

You don't need a second table. Just have a start and end date on each row. The row without an end date is the active record. I've built entire systems using this method, and just so long as you index the date fields, it's very fast.

When retrieving the current record, AND end_date IS NULL gets added to the WHERE clause.

Answer 4

In this situation, I would recommend you to consider all properties in one table after adding it few columns:

1. active/ not active
2. ID of the person who kept these parameters
3. timestamp of adding