Developers have a hard time letting go of data. Developers like myself are, for example, likely to keep thousands of photos backed up on a server, half of which are too blurry, overexposed, or underexposed to be usable. I probably have a few that were taken with the lens cap on, and yet I keep them. I’m a byte hoarder, and storage is cheap. At least it’s just data and I still have access to my bathroom, so it could be worse.

The hesitation to delete makes us likely to archive records because it gives us a sense of security, particularly with client applications hosting critical business records. Data retention policies aside, it’s an easy and safe practice provided the means by which data is removed (in this case: “deleted”, “archived” are reasonably synonymous) is documented and easy to follow. Oh, and hard to screw up. We don’t suddenly want old, irrelevant data spilling out into the application.

The Problem

A client recently wanted to delete some Customers from a very custom sales application. Up until this point, the application has been small and pragmatic, and iteratively built. Deleting a Customer is a feature we hadn’t implemented yet (believe it).

Easy enough. In this particular application’s database, we largely followed the pattern used in AdventureWorks (highly normalized, with entities like Customer, Supplier having an “is a” relationship to a BusinessEntity table.) To delete records, I implemented an “Archived” bit in the BusinessEntity table. Arguably, an ArchivedOn field of type DateTime2 would probably have been a better choice, and adding that field to the more specific subtype of Customer may have been more logical, but hindsight, right?

Now a simple flip of the bit “deletes” a record. But, how do we handle the historical foreign key relationship now that the the Customer is “deleted”? If we’re always querying from the Customers table for non-archived records, how do we load the one we need for a Sales Order when we’re editing or viewing it? It’s a deceptively complex problem that some people will naturally understand; perhaps from lack of sleep or overestimating the problem, I was not one of those people! I won’t admit how long I had to chew on this one. :grin:

The Solution

The solution is simple. Like, really simple. When loading the record, we simply need the CustomerID (foreign key) from the SalesOrder record, and we use that in our Customers query to append that single record to the result set. So effectively, our query becomes something like this:

SELECT CustomerID, Name FROM Customers WHERE Archived = 0 OR CustomerID = 56404

That gives us all of the non-archived Customers plus the one we need.


Share This Article

I am available for remote contract work

If you have a project that needs help, a process that needs improvement, or an idea that you want a sounding board for, I would love to have a discussion with you.

Learn More →

I am not available for full-time work

I'm currently employed as a Senior Consultant at Magenic and not looking for other opportunities at this time.

Let's Connect

Want free advice, thoughts, or feedback? Tell me what you're working on or describe a challenge you're facing and I'll do my best to help.

Free eBook: 10 Killer Tips for .NET Web API

Be awesome. Download my free 56-page eBook for building performant, scalable, maintainable software using .NET Web API. (There's also a bonus chapter on effectively using HTTP Status Codes.)

Enter your email address below and get it immediately.