Don’t Delete – Just Don’t

Tuesday, September 1st, 2009.

no deletes
After reading Ayende’s post advocating against “soft deletes” I felt that I should add a bit more to the topic as there were some important business semantics missing. As developers discuss the pertinence of using an IsDeleted column in the database to mark deletion, and the way this relates to reporting and auditing concerns is weighed, the core domain concepts rarely get a mention. Let’s first understand the business scenarios we’re modeling, the why behind them, before delving into the how of implementation.

The real world doesn’t cascade

Let’s say our marketing department decides to delete an item from the catalog. Should all previous orders containing that item just disappear? And cascading farther, should all invoices for those orders be deleted as well? Going on, would we have to redo the company’s profit and loss statements?

Heaven forbid.

So, is Ayende wrong? Do we really need soft deletes after all?

On the one hand, we don’t want to leave our database in an inconsistent state with invoices pointing to non-existent orders, but on the other hand, our users did ask us to delete an entity.

Or did they?

When all you have is a hammer…

We’ve been exposing users to entity-based interfaces with “create, read, update, delete” semantics in them for so long that they have started presenting us requirements using that same language, even though it’s an extremely poor fit.

Instead of accepting “delete” as a normal user action, let’s go into why users “delete” stuff, and what they actually intend to do.

The guys in marketing can’t actually make all physical instances of a product disappear – nor would they want to. In talking with these users, we might discover that their intent is quite different:

“What I mean by ‘delete’ is that the product should be discontinued. We don’t want to sell this line of product anymore. We want to get rid of the inventory we have, but not order any more from our supplier. The product shouldn’t appear any more when customers do a product search or category listing, but the guys in the warehouse will still need to manage these items in the interim. It’s much shorter to just say ‘delete’ though.”

There seem to be quite a few interesting business rules and processes there, but nothing that looks like it could be solved by a single database column.

Model the task, not the data

Looking back at the story our friend from marketing told us, his intent is to discontinue the product – not to delete it in any technical sense of the word. As such, we probably should provide a more explicit representation of this task in the user interface than just selecting a row in some grid and clicking the ‘delete’ button (and “Are you sure?” isn’t it).

As we broaden our perspective to more parts of the system, we see this same pattern repeating:

Orders aren’t deleted – they’re cancelled. There may also be fees incurred if the order is canceled too late.

Employees aren’t deleted – they’re fired (or possibly retired). A compensation package often needs to be handled.

Jobs aren’t deleted – they’re filled (or their requisition is revoked).

In all cases, the thing we should focus on is the task the user wishes to perform, rather than on the technical action to be performed on one entity or another. In almost all cases, more than one entity needs to be considered.

Statuses

In all the examples above, what we see is a replacement of the technical action ‘delete’ with a relevant business action. At the entity level, instead of having a (hidden) technical WasDeleted status, we see an explicit business status that users need to be aware of.

The manager of the warehouse needs to know that a product is discontinued so that they don’t order any more stock from the supplier. In today’s world of retail with Vendor Managed Inventory, this often happens together with a modification to an agreement with the vendor, or possibly a cancellation of that agreement.

This isn’t just a case of transactional or reporting boundaries – users in different contexts need to see different things at different times as the status changes to reflect the entity’s place in the business lifecycle. Customers shouldn’t see discontinued products at all. Warehouse workers should, that is, until the corresponding Stock Keeping Unit (SKU) has been revoked (another status) after we’ve sold all the inventory we wanted (and maybe returned the rest back to the supplier).

Rules and Validation

When looking at the world through over-simplified-delete-glasses, we may consider the logic dictating when we can delete to be quite simple: do some role-based-security checks, check that the entity exists, delete. Piece of cake.

The real world is a bigger, more complicated cake.

Let’s consider deleting an order, or rather, canceling it. On top of the regular security checks, we’ve got some rules to consider:

If the order has already been delivered, check if the customer isn’t happy with what they got, and go about returning the order.

If the order contained products “made to order”, charge the customer for a portion (or all) of the order (based on other rules).

And more…

Deciding what the next status should be may very well depend on the current business status of the entity. Deciding if that change of state is allowed is context and time specific – at one point in time the task may have been allowed, but later not. The logic here is not necessarily entirely related to the entity being “deleted” – there may be other entities which need to be checked, and whose status may also need to be changed as well.

Summary

I know that some of you are thinking, “my system isn’t that complex – we can just delete and be done with it”.

My question to you would be, have you asked your users why they’re deleting things? Have you asked them about additional statuses and rules dictating how entities move as groups between them? You don’t want the success of your project to be undermined by that kind of unfounded assumption, do you?

The reason we’re given budgets to build business applications is because of the richness in business rules and statuses that ultimately provide value to users and a competitive advantage to the business. If that value wasn’t there, wouldn’t we be serving our users better by just giving them Microsoft Access?

In closing, given that you’re not giving your users MS Access, don’t think about deleting entities. Look for the reason why. Understand the different statuses that entities move between. Ask which users need to care about which status. I know it doesn’t show up as nicely on your resume as “3 years WXF”, but “saved the company $4 million in wasted inventory” does speak volumes.

One last sentence: Don’t delete. Just don’t.

If you liked this article, you might also like articles in these categories:

If you've got a minute, you might enjoy taking a look at some of my best articles.
I've gone through the hundreds of articles I've written over the past 6 years and put together a list of the best ones as ranked by my 5000+ readers.
You won't be disappointed.

If you'd like to get new articles sent to you when they're published, it's easy and free.
Subscribe right here.

Follow me on Twitter @UdiDahan.

Something on your mind? Got a question? I'd be thrilled to hear it.
Leave a comment below or email me, whatever works for you.

73 Comments

Dare Obasanjo aka Carnage4Life - Building Scalable Databases: Perspectives on the War on Soft Deletes Says:
November 23rd, 2009 at 7:32 am
[…] above that there are situations where one wants to hard delete data from the database in his post Don’t Delete – Just Don’t where he […]

Leo Says:
December 11th, 2009 at 1:35 pm
Thanks pal!!! I was wondering if someone was concern about the domain model.. I’m trying to apply some DDD and it makes sense to me that deletes have a reason behind it.. I will say no to deletes from now on!!!

Saved my day!!!!

Thanks,
Leo!

Service-Oriented, Event-Driven Part 1: Events « christopherDeweese.com Says:
December 24th, 2009 at 7:36 am
[…] Dahan posted an article titled “Don’t Delete – Just Don’t” a few weeks back. In that article Udi explores soft deletes versus hard deletes, but he […]

GBruenetti Says:
February 9th, 2010 at 5:55 am
Of course there is the need to delete things physically. Temporary data, old data, erroneous data.

Before the computers took over we had paper baskets and shredders in our businesses. Since the basic business requirements are mainly unchanged we also need the digital equivalent of them today. I don’t want to filter my valuable information out of a trash mountain.

This observation does not mute your main point: Make sure you understand what your user wants/needs and *then* implement the correct logic for this. However, sometimes that can be a plain DELETE.

SJB Says:
February 12th, 2010 at 1:49 pm
How does this tie in with Data Protection laws, where data can only be held for x-amount of time?

udidahan Says:
February 13th, 2010 at 10:39 am
SJB,

At that point, we create rollups of the data which can be stored for longer periods of time.

ricky lively Says:
August 2nd, 2011 at 6:24 pm
When I have had this kind of issue, I found that using a date range indicating when the record is active helpful. After the date range is over, the record still exists (for joins to records that used it), but is not used in anything going forward.

udidahan Says:
August 3rd, 2011 at 8:09 am
Ricky,

What you’re describing is a kind of soft-delete.

Why you should be using CQRS almost everywhere… Says:
October 2nd, 2011 at 2:45 pm
[…] means you don’t ever delete data (see my previous blog post on the subject), and you definitely don’t overwrite it with an update – even if you think […]

Thomas (new one) Says:
January 30th, 2012 at 6:57 am
Interesting ideas but they are based on data which is directly based on real life objects which basically exist forever.

Do we really want to store everything from now to eternity?

If we don’t, we have to delete something. Like orders older than 10 years or customers who haven’t bought anything in 2 years.

Or stock data from 5 years ago. Storing personal data of ex-employees is probably illegal in many countries, they have to be deleted as soon as employment ends.

So: As a blanket idea ‘Do not delete’ is very bad idea: It will cause massive problems sooner or later.

udidahan Says:
February 1st, 2012 at 7:57 am
Thomas,

When it comes to personally identifying information, often we can comply with the privacy laws by just scrambling the data.

Travis J Says:
March 26th, 2012 at 6:23 pm
A nice article about not deleting. However, not really much attention is paid to what to do with the data that wasn’t deleted. In fact, it is glossed over.

In my opinion this is because you are focusing on the wrong issue. Not just “what does deleting mean”, but what IS being deleted. When a record is to be removed, what is really being removed is a node in a graph – not just a single record. That whole graph integriy is the reason for people to bandaid over the issue with “soft deletes” as advocated here. These bandaid solutions tend to hide the gangrene underneath – a festering problem which only gets worse with time.

What’s worse is that in order to accompany the soft delete logic must be included all over (many times breaking various conventions and implementing anti-patterns) to account for the possible breaks in the object graph. Moreover, what kind of business logic is “isDeleted”?!

I believe a very strong solution to this problem, the problem of removing an object while retaining the referential integrity of the object graph, is to use an archival pattern. On delete of an object, the object is archived then deleted. The archive database, a mirror database with meta data (temporal database design can be used and is very relevant here), would then receive the object to be archived and restored if necessary.

This makes it very direct to avoid listing or including a deleted object as the relevant database will no longer hold it. Now, the same logic which was applied looking for “isDeleted” “isActive” or “DeletedDate” can be applied in the correct place (Not all over the place) to foreign keys of retrieved objects. When a foreign key is present, but the object is not, then there is now a logical explanation and a logical set of options. Display that the containing object was deleted and some course of action: “Restore, Delete Current Containing Object, View Deleted”. These options can be either chosen by the user, or explicitly defined in code in a logical manner. Depending on how advanced the archival database is, perhaps more options exist such as who deleted it, when, why, etc. etc.

Lelala Says:
October 2nd, 2012 at 12:53 pm
Wow,
thanks for that impressive article (i came to you from Ayende’s site). Your example with the marketing department is right, absolutely – while i can, absolutely, imagine a case in which it is not that relevant that you keep all stuff: You could save the final invoice, e.g., instead of storing all products that you have sold 10 years ago…
It depends completely on the use case, without considering this there is no true-vs-false.

Luiyo Says:
June 29th, 2013 at 2:13 pm
well, at some stages the DELETE is nothing but required. for instance in buffer tables, for some continuous flow processing in large scale systems as the ones I work with.

trxs flow in a table through different async processes by changing status, and finally have a “logical deleted” status. But to keep +15 process in the flow with 120 trxs / second throughput, this table has also to be purged.

there are different strategies for the purging activities, and the processes and table structure design some times makes it quite tricky.

of course for usual business applications (even for large companies) the logical deletion is quite fine, but when dealing with massive data and near real time processing capacity things get a cool twist 🙂

cheers,

Lelala Says:
April 13th, 2014 at 11:08 am
The thing is, in smaller webprojects one could delete data, it depends on the context:
If its not your bookkeeping or any critical stuff, i’d say there is really no issue in deleting those rows from their tables. Sure -this should not be applied to your taxing-table 😉
Hence it depends on the situation/problem one wants to solve.
Regards

udidahan Says:
April 16th, 2014 at 2:26 pm
Lelala,

I wouldn’t say it’s the issue of small vs big, but rather (as you said) the context.

When the data is (what I call) private – meaning only seen/used by a single user, then that user can delete that data completely without any concern.

However, when the data becomes “public” – meaning that there is data from other users that gets connected to it, one should avoid deleting and think of more meaningful lifecycle-type changes to it.

Hope that clears things up.

Thanks.

Oscar Says:
September 19th, 2014 at 1:34 am
Hi Udidahan,

I have been facing this topic on several systems and I do believe in soft delete. But I have seen there is a problem when the data is highly connected. A very simple example: let’s say that we are managing stores & employees. When we decide to deactivate a store, we can not think on deactivate the employees too (cause that will be thinking in the cascade way that we are trying to avoid), so, we can mark them as “free” employees, without an assigned store. Seeing this from reporting side, what should we do when we decide to do a report of the stores existing on the months before delete?, should we show the store and the link with all its employees at that time?. And a second report after delete time, should the store not be shown? (of course thinking also on an employee report to show them as free after delete but show them linked to the store on a report before delete).

The thing here is, should all be managed as really historic records? or once we decide to deactivate something the relations should be forgotten by the system?. In my example, would be to never show the store even in reports previous to its deactivation and show the employees as free persons since the start of the time.

It could be confusing on making a difference when deactivation means something (like the closing of a store) or it is just deleting an input error (user wrongly enters a store that never existed).

I know that this could make the topic more complicated, but I believe that in some systems could be need to take in consideration and could have an impact on deciding if we should use soft or physical delete.

From my experience I support the idea soft delete but the final user needs to be part of the process on designing the logic behind it. We as analyst and designers can have a lot of good points and ideas, but the goal of a system is that it can make sense to our clients (even if it looks kind of gross to our logic)

Thanks in advance, regards

udidahan Says:
September 21st, 2014 at 7:03 pm
Oscar,

> the final user needs to be part of the process on designing the logic behind it (the delete)

I definitely agree with that statement.

dan Says:
July 10th, 2015 at 6:33 am
In tables that I use as a queue system, I delete records after processing them.
Otherwise I forbid deleting records in my systems. It’s annoying to have to specify “and deletedDate is null” in your queries, but I usually end up making a view like “select * from FooHistory where deletedDate is null” and use views in app logic.
I suppose soft-deletes can be a pair for ORM users, though as a micro-ORM user, it’s neither more nor less of a pain.
What is less of a pain is determining the state of the system on date X. You can’t do that if you destroy data. Why would a developer care about this–isn’t it a reporting issue? No, it’s a development issue. Ever get a bug report that you can’t reproduce? Maybe it’s because the data you need to reproduce it has been destroyed. There are entire realms of questions that cannot be answered when you destroy data.
I’m sure it wouldn’t be too hard to build a convention-based ORM plugin to deal with soft-delete tables, especially for the readers of this blog.

Another thought is in regards to “it doesn’t make sense for a business object to have to check for isDeleted”. I think, “yes & no”. An entity can be thought of as a series of changes over time. If you update the name of a product “FooA” to “FooB”, the people who bought it before the update still have a box that says “FooA”. Or if instead you delete the product and replace it with “FooB”, the old orders for “FooA” should still exist in your system. When the customer views his order history, it should definitely contain an entry for “FooA”, not the updated name.

udidahan Says:
July 22nd, 2015 at 2:09 pm
Dan,

Those are great points – I’m with you. Depending on the DB you’re using, keeping the entire history in a single table can be problematic at large volumes of data. You’d probably want at least a separate file partition for the deleted rows.

Pepster Says:
October 21st, 2015 at 3:56 pm
In our system we use domain event to insert/update and delete views required for querying. I guess when using ES, it doesn’t matter whether you use Delete in your command as it will be stored in the EventStore as an event. What happened with the aggregate can be always be loaded from history by replaying the events, so no information is lost.
When using a model for read/write, it becomes much harder to find out whether you have to do a real delete.

Jooo Says:
June 1st, 2016 at 1:19 pm
Inspiring post…however, I do not think the example given actually support the point. Basically, The real world does cascade. The order example is a little bit misleading since that is not the real delete scenario. That is just scenarios for stateful entities. Delete is for truly useless data. For instance, if an employee resigns, the company would delete not only his email, but also other record related to him. (assume no ref of this employee is needed in future). Also, audit Trail would take care of the data recovery job. Just make sure the atomic is not broken in transction management.

Regards

udidahan Says:
June 4th, 2016 at 3:01 pm
Jooo,

Even if an employee resigns or is fired, the company shouldn’t delete/expunge their records. Lawsuits can always happen later.