As a web developer you are constantly faced with building functionality to remove an object from an application. The object could be anything from a web page in a CMS to a user in a CRM, but generally involves a record(s) stored in a database.
For a user ‘using’ the application whenever they access the functionality to remove the object it disappears from view…but what happens behind the scenes?
There are a couple of standard options available to developers:
- To actually delete the object from the database via a DELETE statement
- To flag the object as deleted (via an UPDATE statement) and have code to handle the ‘removal’ in a view accordingly
Deleting the record(s) is nice and easy and most likely no other code will be needed in your ‘view’, but there are schools of thought where people never (or rarely) physically delete a record from the database even if you do have backups to access at a later date. The main reason for this is that somebody (particularly an administrator) might be interested in all sorts of information on the history of that object, who removed it (and when) and the ability to rollback that object to an active state.
It’s this approach to web applications which I almost always follow. I rarely delete objects from the database, opting to flag the record instead. But this can lead to a lot more time spent completing the project, which could mean a later ‘go live’ date.
Consider that a HUGE portion of your application code is (or should) be taken up by defensive programming like exception handling and safety from malicious intent etc, how much extra code might you spend on keeping these removed (so presumably no longer relevant) objects?
Of course each scenario is different as are the business requirements of a client, but even if a client assuring you that “we’ll never need it again!”, I’d have a long and hard think before actually physically deleting anything. After all, we are professionals and need to think ahead to what the client may need in the future. Your ‘expert’ advise should always be considered and put forward, even if it is the unpopular view.
How much extra time spent will of course differ in each scenario but you’ll clearly need to have your SQL statements to filter out these records (eg. WHERE IsDeleted Is Null). This will most likely need to be done on any gateway type query which is run on the table(s) in question.
Do you need to build in an audit trail for tracking which user deleted the object, when they deleted it and have mandatory comments on why they deleted it?
Do you need to have the ability to rollback to an active state?
In the above 2 scenarios you’ll probably need an interface to access this data as well.
Business logic might also add another layer of complexity (the catalyst for this post) to the question which is “to delete or not to delete”.
I’m still a fan of storing the data for most operations, but in an age of agile/extreme programming, more and more pressure to get the job done in half the time and a great mantra of “release early, release often”…sometimes you may need to ask yourself if you really need to keep this data 
9 Comments
Other than storing “deleted’ information for history and review purposes, I find that it is good for “referential integrity”. Like, if you store the user ID of the user that created some object and then later on that user gets deleted… now you have an ID in a record that does not have a user to which it is referring. However, if you only flag as deleted than you pretty much never have to worry about how to handle referential integrity.
And on the legal side of things, if it’s a company, there should be a data retention policy of some sort, such as clearing out records older than 2 years. Having too much information around can be a bad thing. A data retention policy protects the company when such records are deleted as to providing the reason for deletion.
Automated systems can be in place that automatically purge old records as well as a mechanism that can be enabled to prevent deletion of records should a discovery process occur.
Hi Ben,
Agreed, I always set my relationships during database design so referential integrity is enforced. This way you obviously couldn’t delete the parent record even if you wanted to, because a child exists.
Also I don’t use cascading deletes.
I’ve always been a fan of hanging on to data. E-mail hardly gets deleted, I’ve got Billing statement going back years because I’ve yet to buy a shredder, and on the DB side. I love saving data. The good kind that is. Customer Personal data I try to get rid of the moment it’s entered into the form if possible.
Audit trails haven’t come up ofter but I think for your average mom-and-pop outfit this is just way overkill. They deleted something on accident even though you put the delete button way on the right of the page and added a popup stating “This record is being deleted” They still click it and call you for the magic pill.
Well i got it because everything is marked as deleted or what I’ve been doing more and more is IsActive = 808 or something you would normally never use. You got IsActive = 1 for normal content, 0 for InActive, but those items in 808 stand out in the database and since I still mark last updated by on the update, there is at lease a who was last here type mark on the record.
Very little work is done to make this happen. the button for delete still goes through the same update routine so I’m actually saving myself from having to create a delete routine. If the DB get’s too big, I’ll delete old records then manually.
Hi,
I am doing a web based project using mysql and perl ,I just want to know how to create deletion flags so that the data is
actually not deleted from the database.
Can anybody explain this using an example
Thank you
Hi VIJAYKUMAR,
Often a developer might have a column in a table who’s value is an indicator of that rows status, i.e. whether that row is ‘deleted’ or not.
For example if I had a Users table I might have a column called bIsDeleted which is a bit (or boolean) data type. The initial value of this column would be null or 0, indicating that the current record is NOT flagged as deleted and therefore is active.
If that user was to be deleted I would UPDATE the Users table setting that flag to 1 (i.e. the boolean value of ‘true’). Now my application will know that the user has been ‘deleted’, but my data has been retained.
e.g.
UPDATE Users
SET bIsDeleted = 1
WHERE UserId = 10
Hope that makes sense.
Hi Michael,
Thanks very much for ur help
am doing a web based project using mysql and perl ,I just want to know how to create deletion flags so that the data is
actually not deleted from the database.
@Sohbet – Normally I’d create a column in my table called something like “bIsDeleted” (the ‘b’ standing for boolean).
The datatype in MSSQL would be ‘bit’, but for MySQL I’d typically use ‘tinyint’. I would only store a ’0′ or ’1′ in this column.
When I want to delete that row I simply UPDATE its value to ’1′ meaning true, or yes…this is deleted.
Then when I’m SELECTing data from this table I make sure my WHERE condition has:
WHERE bIsDeleted <> 1
Make sure you don’t have NULL values in that column, otherwise your WHERE clause will need to also look for NULL and <> 1