Deleting Foreign Key Records While Keeping a Historical Association to Them

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.

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.

Relevant links

• StackOverflow Question