Gone is Gone: The Case Against “Delete” « Web Development

I love, love, love getting rid of crap.  Maybe that’s why I like to read Unclutterer, or think that the 100 Things Challenge is such a great idea.  In real-life, I’m a minimalist, but when it comes to programming web apps, I’ve become a digital pack-rat.

I don’t care if my database grows into a giant data-monster, ready to ravage my server’s hard disk and slow queries down to a crawl; I’ll deal with it.  The key here is to not allow any important history of data to get lost.

Why Deleting is Bad

Here’s an example, to get us started:

Let’s say you have a simple online store, and you’re selling important consumer items to important people.  You have a database that keeps track of your customers, your products, and your transactions.  It may look something like this:

So, you’ve been in business for a while, and you have a bunch of customers in the system, quite a few products, and a lengthy transaction history.  Recently, you’ve decided to stop selling your Jell-O Brain Mold and you’re ready to remove it from your online store.  Common sense says that you would go to your database, find the record referring to your product, and delete it.

But wait!  If you do this, then the entire history of transactions linked to those products become orphaned (theproducts_id field will point to a non-existent record).  So, if you want to go back in at a future date to see the history of how many of those items that you sold, you will be in trouble.  You haven’t just deleted the item from your website per se, you’ve actually deleted the record of the item ever existing in the first place.

And, you don’t want to do that.

Flag It!

The useful alternative would be to add a flag to the data, so that instead of deleting the record, you could mark it as inactive.  This way, it wouldn’t appear on your website, but it would still exist in the database for future reference.

Your tables will now look something like:

Now you can tell your application to only show the products with `active` = “TRUE” on your website, but refer to products with `active` = “FALSE” when viewing older transaction histories.

This works pretty well, and if you program your User Interface correctly, your website users should never actually be deleting records; instead they should be marking them inactive so they “go away” but never truly die.

Audit History

Okay, now it gets a bit more complex.  In some cases, you don’t just want to mark a record “active” or “inactive” (that is, give it the ability to have two states), you also want to keep a complete revision history of that record, including what change was made, who made it, and when.  This way you can see what a record looked like at a particular moment in time.

This is not a new concept.  Think about your bank account.  It’s pretty much a bunch of numbers stored in a database somewhere.  Do you think that your checking account balance is simply a single field in a single record stored in a database?  Heck no!  Every single debit and credit is stored as a separate transaction, showing a complete history of changes to the account.

There are many ways to accomplish this, really, and for me to put up a tutorial, would be to reinvent the wheel.  The key is to think about which data you want to have complete histories on, and which data is not expendable.  Design your application around these constraints.

Sometimes you Just Have to Kill It

I wrote an application that contains patient records.  When an administrator clicks “Delete”, the patient doesn’t really go away; all of the information remains in the database.  Sometimes, however, we must delete the record permanently. Usually this is because a patient opts out of the program and requests to be removed from the study.  I use a special word for this kind of delete: Purge.

But, before the record is purged, the user has to click a checkbox, and go through two “Are You Sure?” screens informing him or her that not only will the patient record be deleted, but also the complete history of that record, and all data associated with it.  I try to have the application show the data in question on the screen the user has to click yes to.

…well, that’s enough for now.  Hope you’re thinking about data consistency, so you too can become a digital pack rat.