“One of the annoyances that we have to deal when building enterprise applications is the requirement that no data shall be lost.”
Since when is a business requirement an “annoyance”? We keep data for lots of good reasons. Just a few off the top of my head:
- IRS requirements
- SEC requirements
- SOX requirements
- data warehousing
- data auditing
- delayed undo
- business intelligence
- trend reporting & analysis
- research & development
- cooperative databases
- industry databases & statistics
“The usual response to that is to introduce a WasDeleted or an IsActive column in the database and implement deletes as an update that would set that flag.”
Wrong. The usual response is archiving to disk using separate tables. This solves all of the requirements above while streamlining active database tables. It’s not unusual for 98% of all the enterprise data on disk to be in the archived state.
“This sort of cleanup now has to moved up into the application layer.”
So what?
One of the biggest mistakes I consistently see is trying to use the facilities of a database management system to replace application logic. Indexes, triggers, and stored procedures are all critical tools in the developer’s arsenal, but they do not replace application analysis, design, and programming. Like security and scaling, archiving is a “architectural consideration”, not a bolt on. Including proper data archiving in the application’s design renders the hard vs. soft delete debate pointless.