3 ms·

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 requir

by edw519 17y ago

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.

sophacles 17y ago
I agree that the "hard vs. soft delete" debate is pointless. It seems the article sets up a false dilemma. The interesting thing is how to deal with archive data. There are a few patterns, but the one presented many times in this article is the "log it" pattern (if it has an official name, please let me know). Basically, this transforms all the data into a series of transactions, each of which is stored. It can occur at the database level (log each changing query, e.g. insert, updated, delete). At the data level (columns for is-filled, is-backordered, and so on, the soft delete in the article). At the application level (each transaction is in a different row, what it means is away from the data itself). Really it comes down to how often the data will need to be accessed, and how dynamically it needs to be queried. In the modern age of hadoop clusters everywhere, and clouds being cheap, it may actually be best to keep the database as a maintainer of current state, and the transaction log as the historian (alternative 1 above), shipping off the logs for data-mining on the cluster at the analyst's leisure. The part I find amusing is: depending on which angle you come from, this could be a hard or soft delete :).
joe_the_user 17y ago
This all depends on whether you have a database shared between different applications of business or whether you have a start-up where the application pretty much is the business. In the later case, (web start-up), it makes sense to pare down the DB logic as needed and have application logic do everything from archiving to record locking. Of course, if your website does real financial stuff, you are going to have be super serious about writing routines that keep everything in the legally required formats, no small job. In the former case, (traditional business with multiple apps), a serious database is designed to have what the organization needs. You shouldn't have a particular app trying to maintain either the ACID quality of DB or do archiving etc (unless the app is a script or utility run by the DBA). The database should be doing that for you. -- And so, a lot of this debate comes because the debaters aren't considering the larger situation they are a part of.

by edw519 17y ago

sophacles 17y ago

I agree that the "hard vs. soft delete" debate is pointless. It seems the article sets up a false dilemma. The interesting thing is how to deal with archive data. There are a few patterns, but the one presented many times in this article is the "log it" pattern (if it has an official name, please let me know). Basically, this transforms all the data into a series of transactions, each of which is stored. It can occur at the database level (log each changing query, e.g. insert, updated, delete). At the data level (columns for is-filled, is-backordered, and so on, the soft delete in the article). At the application level (each transaction is in a different row, what it means is away from the data itself). Really it comes down to how often the data will need to be accessed, and how dynamically it needs to be queried. In the modern age of hadoop clusters everywhere, and clouds being cheap, it may actually be best to keep the database as a maintainer of current state, and the transaction log as the historian (alternative 1 above), shipping off the logs for data-mining on the cluster at the analyst's leisure. The part I find amusing is: depending on which angle you come from, this could be a hard or soft delete :).

joe_the_user 17y ago

This all depends on whether you have a database shared between different applications of business or whether you have a start-up where the application pretty much is the business. In the later case, (web start-up), it makes sense to pare down the DB logic as needed and have application logic do everything from archiving to record locking. Of course, if your website does real financial stuff, you are going to have be super serious about writing routines that keep everything in the legally required formats, no small job. In the former case, (traditional business with multiple apps), a serious database is designed to have what the organization needs. You shouldn't have a particular app trying to maintain either the ACID quality of DB or do archiving etc (unless the app is a script or utility run by the DBA). The database should be doing that for you. -- And so, a lot of this debate comes because the debaters aren't considering the larger situation they are a part of.