http://ayende.comAyende @ RahienCopyright (C) Ayende Rahien 2004 - 2021 (c) 202660I also have problem with: collection was not processed by flush() Any solution so far? http://ayende.com/4157/avoid-soft-deletes#comment33http://ayende.com/4157/avoid-soft-deletes#comment33Mon, 28 Sep 2009 23:53:03 GMTI think there's perfectly reasonable scenarios for both soft and hard deletes, with and without audit trails. 1. Soft delete: A user signs off of your service, but you need to keep (anonymized) stats data consistent for your customers - here you cannot remove the entire user, you just blank his personal data (name, email, phone, ...) and keep the anonymized statistical data (country, birth year, profession). 2. Soft (or super-soft) delete with audit trail: Any financial transaction data, even if entered by error, may only be corrected by adding a correction entry, not by deleting the erroneous entry, or the IRS will be all over you. So either you flag it as deleted (soft delete) or you correct it by adding another entry ("super-soft delete"). 3. Hard delete with audit trail: A user unsubscribes from your newsletter. No need to keep the info "he once was subscribed to it" in the live database, but need to keep the info somewhere accessible in case he sues you for spamming and you can prove "but back in August when you got the mail, you were still subscribed". 4. Hard delete with no audit trail: Personal data as in #1 if your local data protection laws require. (This means *no* more storing, *anywhere*, technically speaking not even in last month's backup, but that's another issue altogether.) http://ayende.com/4157/avoid-soft-deletes#comment32http://ayende.com/4157/avoid-soft-deletes#comment32Thu, 10 Sep 2009 08:26:45 GMTTad, This really should go to the nh users mailing list You need to use a child session in the pre update event http://ayende.com/4157/avoid-soft-deletes#comment31http://ayende.com/4157/avoid-soft-deletes#comment31Thu, 03 Sep 2009 08:20:13 GMTHi, When i implemented audit on preUpdateEvent I got error : collection was not processed by flush() I'm comparing complex types and don't know how to do this that it would work Any solutions ? http://ayende.com/4157/avoid-soft-deletes#comment30http://ayende.com/4157/avoid-soft-deletes#comment30Thu, 03 Sep 2009 07:39:30 GMTI did, I have no further comments, since I mostly agree with him http://ayende.com/4157/avoid-soft-deletes#comment29http://ayende.com/4157/avoid-soft-deletes#comment29Wed, 02 Sep 2009 10:21:53 GMTHave you read Udi's response? [www.udidahan.com/2009/09/01/dont-delete-just-dont/](http://www.udidahan.com/2009/09/01/dont-delete-just-dont/)http://ayende.com/4157/avoid-soft-deletes#comment28http://ayende.com/4157/avoid-soft-deletes#comment28Wed, 02 Sep 2009 07:40:38 GMTI wrote a blogpost about this in february 2009: [weblogs.asp.net/.../...-deletes-are-bad-m-kay.aspx](http://weblogs.asp.net/fbouma/archive/2009/02/19/soft-deletes-are-bad-m-kay.aspx) http://ayende.com/4157/avoid-soft-deletes#comment27http://ayende.com/4157/avoid-soft-deletes#comment27Tue, 01 Sep 2009 11:42:04 GMTWhen you live and work in a regulated environment, you do not have a choice - the data must live on. If it is added to the database, it shall remain in the database - no exceptions. Plus, a secondary rule often comes into play - thou shall not read from the audit trail for anything other than audit reports. Therefore, you live with soft deletes and status tables. The effect of these requirement is that the working of cascading updates and deletions MUST reside in code - whether in the database or in the data layer. The only way that I have seen to get them to work cleanly is to accomplish them in both layers - update triggers in the database and update events/messages in the data layer. http://ayende.com/4157/avoid-soft-deletes#comment26http://ayende.com/4157/avoid-soft-deletes#comment26Mon, 31 Aug 2009 23:51:24 GMTDeletion is an overloaded term: 1) technical deletions ("hard delete") 2) domain deletions ("soft delete") Soft deletes should only be used if the deletion itself is relevant data. For example visualised by a line through the text to indicate the item was "deleted". To avoid confusion it should always be clear that delete is a technical, hard delete. And to avoid confusion I agree there should not be a column "Deleted". Better call the column "Died" or "Dismissed" or "Cancelled" or whatever your domain concept is. http://ayende.com/4157/avoid-soft-deletes#comment25http://ayende.com/4157/avoid-soft-deletes#comment25Mon, 31 Aug 2009 23:16:43 GMTJim, I'll respond to that tomorrow, too late now to really think. @Erik, Soft delete & insert only are two very different models. And I am talking specifically about deletes here, deactivating a customer is something else. http://ayende.com/4157/avoid-soft-deletes#comment24http://ayende.com/4157/avoid-soft-deletes#comment24Mon, 31 Aug 2009 21:36:14 GMTThis Seems like a case where like everything else it depends. Take a discussion forum where you want administrators to see Deleted entries with the note that they were deleted. Well then deleted is just a filter, that is applied to everyone except administrators/moderators and the rest of your code would become more complex to go back and throw those deleted entries in for the administrator to see. It also seems like a lot of the arguments for soft deletes is coming from case that you aren't really deleting, but deactiviating. e.g. Customer. Ayende, How does this same logic apply to something like [ayende.com/.../...ample-design-considerations.aspx](http://ayende.com/Blog/archive/2008/12/07/shopping-cart-sample-design-considerations.aspx) I guess the main difference is that above you are mostly adding things and getting the benefit of an add-only(mostly?) model. A soft delete is still a modification? http://ayende.com/4157/avoid-soft-deletes#comment23http://ayende.com/4157/avoid-soft-deletes#comment23Mon, 31 Aug 2009 21:28:29 GMTOren, [bigjimindc.blogspot.com/.../...n-soft-deletes.html](http://bigjimindc.blogspot.com/2009/08/temporal-database-design-soft-deletes.html) http://ayende.com/4157/avoid-soft-deletes#comment22http://ayende.com/4157/avoid-soft-deletes#comment22Mon, 31 Aug 2009 21:24:18 GMTEdCh, I would "restore" the row, insert it back into the original table. http://ayende.com/4157/avoid-soft-deletes#comment21http://ayende.com/4157/avoid-soft-deletes#comment21Mon, 31 Aug 2009 20:37:42 GMTUriel, A history table, with an Append Only mode, is the simplest, you can pour it into OLAP directly, and it doesn't impact anything else that you do http://ayende.com/4157/avoid-soft-deletes#comment20http://ayende.com/4157/avoid-soft-deletes#comment20Mon, 31 Aug 2009 20:37:05 GMTJim, [ayende.com/.../ayendersquos-razor.aspx](http://ayende.com/Blog/archive/2009/08/31/ayendersquos-razor.aspx)http://ayende.com/4157/avoid-soft-deletes#comment19http://ayende.com/4157/avoid-soft-deletes#comment19Mon, 31 Aug 2009 20:36:16 GMTI can't say I disagree. But, auditing is not the only reason for soft deletes. Sometimes there's a business requirement to "undelete" (i.e. undo or reverse a previous delection). Given this requirement would you do anything differently with respect to soft deletes? http://ayende.com/4157/avoid-soft-deletes#comment18http://ayende.com/4157/avoid-soft-deletes#comment18Mon, 31 Aug 2009 20:13:53 GMTOren, in all seriousness, I thought that problems that were "(a) complex, (b) hard to understand (c) hard to optimize" were the kinds that folks like you and I get paid to solve... http://ayende.com/4157/avoid-soft-deletes#comment17http://ayende.com/4157/avoid-soft-deletes#comment17Mon, 31 Aug 2009 20:09:36 GMTapart from audit tables,what other options exist? Pete is right that for creating OLAP columns keeping those 3 columns on each table makes making a cube very easy. http://ayende.com/4157/avoid-soft-deletes#comment16http://ayende.com/4157/avoid-soft-deletes#comment16Mon, 31 Aug 2009 20:05:18 GMTUriel, You can certainly do this, I done it in a number of systems with NH. It is just complex, and there are simpler solutions available http://ayende.com/4157/avoid-soft-deletes#comment15http://ayende.com/4157/avoid-soft-deletes#comment15Mon, 31 Aug 2009 20:02:16 GMTJim, The problem with that is that it is (a) complex, (b) hard to understand (c) hard to optimize. Especially if you go to multiple temporal times for different properties or associations. In other words, it is just too hard to do for real, and there are just as good solutions out there for it anyway http://ayende.com/4157/avoid-soft-deletes#comment14http://ayende.com/4157/avoid-soft-deletes#comment14Mon, 31 Aug 2009 20:01:07 GMTyou could make some kind of base class that handles all the logic,like adding the soft-delete field,and returning query objects which are already filtered,i can see this done in django ORM pretty easily http://ayende.com/4157/avoid-soft-deletes#comment13http://ayende.com/4157/avoid-soft-deletes#comment13Mon, 31 Aug 2009 19:53:05 GMTI'm still confused as to why the concept of temporal systems ( [http://en.wikipedia.org/wiki/Temporal_database](http://en.wikipedia.org/wiki/Temporal_database)) has yet to really take off in the IT world. Perhaps making temporal querying a first class citizen in a popular ORM such as NHibernate will once and for all bring this concept into the mainstream. Ultimately concepts such as "soft deletes" become a non-issue when properly utilizing temporal systems (specifically bi-temporal ones). Oren, while I've successfully built a number of these systems in the past (based on .NET DataSets at the time) am I crazy for hoping such an "advanced concept" such as temporal databases will become mainstream??? The work Snodgrass, et al did back in the late 80's and early 90's seems so long ago that it's crazy that this concept is still relegated to the edge cases, rather than simply being how all business systems get created. http://ayende.com/4157/avoid-soft-deletes#comment12http://ayende.com/4157/avoid-soft-deletes#comment12Mon, 31 Aug 2009 18:27:59 GMTWill this post self-destruct or self-soft-destruct? :-) http://ayende.com/4157/avoid-soft-deletes#comment11http://ayende.com/4157/avoid-soft-deletes#comment11Mon, 31 Aug 2009 10:40:41 GMTPete, I don't actually see the difference. I can manage those updates to OLAP cube very easily using an audit table rather than using the same table. It might make the OLAP load procedure slightly more complex, but I am willing to take that burden because the alternative is to make all the other stuff more complex http://ayende.com/4157/avoid-soft-deletes#comment10http://ayende.com/4157/avoid-soft-deletes#comment10Mon, 31 Aug 2009 07:45:53 GMTHBD, Take a look at this post: [ayende.com/.../...amp-ipreinserteventlistener.aspx](http://ayende.com/Blog/archive/2009/04/29/nhibernate-ipreupdateeventlistener-amp-ipreinserteventlistener.aspx)http://ayende.com/4157/avoid-soft-deletes#comment9http://ayende.com/4157/avoid-soft-deletes#comment9Mon, 31 Aug 2009 07:44:01 GMT+1 Dennis! Deletes are very much just about filtering in the GUI. In our app deleting a customer simply means that this customer is inactive and cannot be used anymore. But we can still report on that customer and the orders they once placed. So it's not even about aditing at all, in fact we have separate auditing as well. http://ayende.com/4157/avoid-soft-deletes#comment8http://ayende.com/4157/avoid-soft-deletes#comment8Mon, 31 Aug 2009 07:22:59 GMTAyende must be some kind of a magician that he knows the topic we developers want to read about. :-) I saw "Avoid Soft Deletes" coming in 2 days, and I waited and sure enough here it is! This one is very important and has bothered me so much that I have not yet implemented Delete in my app, instead waiting for the time I got the full story right. I think Soft Deletes is a good option, but it adds to the complexity of the code. So... can Deletion be a 'Cross Cutting Concern' instead? Also, conventional soft delete in combination with Auditing tables will take care of most of the issues while we work out the AOP way of handling it. Can we have a blog post on auditing tables now :-) ? Or a pointer to a good one if it exists. http://ayende.com/4157/avoid-soft-deletes#comment7http://ayende.com/4157/avoid-soft-deletes#comment7Mon, 31 Aug 2009 05:37:21 GMTMost of the problems in Soft Deletes can be avoided if it is seen as a "human" thing. Only when you want to display lists of something, do you need to filter the deleted objects. Otherwise, you simple accept that direct key references to a deleted object ignores the fact that the object is deleted. And in reporting parts of the code, you implement features to allow/disallow the inclusion of deleted objects. Atleast that is how we avoid many of the problems. http://ayende.com/4157/avoid-soft-deletes#comment6http://ayende.com/4157/avoid-soft-deletes#comment6Mon, 31 Aug 2009 04:20:36 GMTI have been building data warehouses a cubes based on historical transaction information, and I can tell you that hard deletes are a BAD idea if you want to build an OLAP cube any time in the future. If you want to build a historic fact table in your warehouse based on transactional information, you need: * DateCreated (not null) - this is important for reporting, and it prompts the ETL/warehouse process to load the record when it is new. This can be done with a getdate() default value in the table design. *DateUpdated (nullable) - this is is set/updated every time the record is changed. it is used for the same reasons as above. LogEventListener might be handy here *DateDeleted (nullable) - Yes this is a soft-delete field. If it is null the record is active. It will be important to add this to a ledgered fact table to keep warehouse reports up-to-date. Once properly logged in the warehouse, you can delete this record in a grooming process. LogEventListener might be handy here as well Of course checking that deleted flag can be a pain, but if you start with this kind of convention in tables that represent facts and dimensions, an OLAP cube and ETL process will be easier to build out in the future. In regard to your challenge, two thoughts: 1. Can you make "LastOrder" a query, or a SQL derived/calculated column, not a stored value? 2. Ayende, youre the architect wiz, if #1 is not desirable, is it ugly to update "LastOrder" somewhere in a domain class when an order is killed? http://ayende.com/4157/avoid-soft-deletes#comment5http://ayende.com/4157/avoid-soft-deletes#comment5Mon, 31 Aug 2009 01:18:49 GMTI generally agree that soft deletes are bad, and that a requirement that "no data shall be lost" can be handled by audit/history tracking 99% of the time. However, there are cases where _certain_ data can't be deleted, and having an IsActive column makes sense. The most common example is a user table. If you track “who did what” in a system (e.g. "CreatedBy" or "LastUpdatedBy"), you’ll want to keep a record that that user existed in the system at some point, even if that user should no longer be active or be able to login (like if they left the company). I can’t imagine naming a column “WasDeleted” under any circumstances. If it’s set to false, it’s redundant; if it’s set to true, it’s a lie. :) Also, I've done auditing both with a single table (as both Matt and Oren suggested), and with an auditor table for each entity under audit (as Richard suggested). The latter is far easier to track and query, IMHO, and makes it much easier to reconstruct what happened later. I generally create a single "master" audit table (id, date, user) and then one individual audit table for each entity under audit. The individual table is an exact mirror of the base table, except it has a FK to the master audit table and a column to represent the action that took place (create/update/delete). A row in the master table loosely represents the database transaction, so when a transaction occurs that involves one or more rows in a table(s) under audit, each corresponding audit table will get rows added representing the change(s) made, tied back to a single row in the master table. I’m sure no one cares but I just finished doing this on a new system, so it’s very top of mind. :) http://ayende.com/4157/avoid-soft-deletes#comment4http://ayende.com/4157/avoid-soft-deletes#comment4Mon, 31 Aug 2009 00:10:43 GMT