Don't delete database records, unless you really need to

© copyright 27.Feb.2009 by Paul Bradley filed under MySQL

[Ad] need a break from coding?

While it's important to perform full database backups for those instances where you need to perform a full restore, it can be time consuming restoring a small sub-set of records from a database backup, due to a user deleting records they wanted to keep.

I had to deal with such a situation this week, I acquired a new client last month along with their PHP web application (written by someone else), and they wanted to restore a sub-set of records because they had deleted them by mistake. To be fair to the original PHP developer, the application did prompt the user with an 'Are you sure you want to delete?' message before actually deleting the records from the database - but users will make mistakes, and the time it took to restore the records didn't please the client.

Hard disks are so cheap now, for the average database I don't see any need to actually delete the data from the database, a better solution would be to have a deleted field within your table, which defaults to 'N' - and instead of deleting the record simply flip this field to 'Y' from within your application when a user performs the delete function.

If a user then deletes a record by mistake, your application can provide an 'un-delete' function by presenting the user with a list of records marked as deleted, and performing the restore by simply changing the value of the deleted field back to 'N'.

Obviously you need to code your PHP application so that it suppresses all records which are set to 'Y', but if you include the deleted field as part of a compound index, then this shouldn't add too much overhead to your application.

It boils down to return on investment, what is the cost in time in restoring deleted records, as opposed to adding this type of feature. Neadless to say, I will be changing this cleints application to adopt this method.

About the Author
Paul Bradley is a VB.NET software developer living and working in Cumbria. He has over 20 years programming experience. He also produces e-learning videos at Code by Example and Linux by Example

Other Popular Articles

• Using MySQL's ROUND command to report file sizes in Mb
• How to backup your MySQL tables and data every night using a bash script and cron.
• Adding Page Titles to Analog Reports From A Custom MySQL CMS