Tracking Changes

[bdmovies]

Server version: 5.0.41

MySQL client version: 5.0.41

 

I am writing an application and I would like to track changes made to the database. For instance, I add a new client to my clients table. Tomorrow I update the address - I want to be able to track that the phone number was changed, the date it was changed and the user that changed it. (I used the client table for example purposes, generally I don't need this information, but what I do need it for is a tad more in depth, but the concept remains the same).

 

I've done some research, but can't seem to find the most efficient way to do this. I have several tables named history_nameOfTableBeingTracked and they contain id, userId, addressID, changes made, datestamp. (address being something that I'm tracking). Every time a change is made, I write it to the table, where the changes made column contains the SQL that used to make the change. Then I can just use PHP to query that table, interpret the SQL the way I want and display it as something like this "123 Main street: 1/1/2008 - Changed ZIP to 33234"

 

Is this the most efficient way to do this, or can MySQL do it by itself?

 

Thanks.

[fenway]

Great question... and no, mysql won't do this for you.

 

There are a variety of ways to handle this -- it really depends how general a solution you want to implement.