http://ayende.comAyende @ RahienCopyright (C) Ayende Rahien 2004 - 2021 (c) 202660Been a busy week... anyway, thanks for posting this Oren, as obviously I couldn't agree more. And I'm definitely looking forward to the post on the 16th that describes how you would tackle this problem via NHibernate. Here's to hoping that you spending some time writing about this subject will actually bring it closer to the forefront, much as I'd love to see it! Take care... http://ayende.com/4179/soft-deletes-aren-t-append-only-model#comment14http://ayende.com/4179/soft-deletes-aren-t-append-only-model#comment14Fri, 11 Sep 2009 18:51:09 GMTSteve, I have a post about that that will show up in the 16th http://ayende.com/4179/soft-deletes-aren-t-append-only-model#comment13http://ayende.com/4179/soft-deletes-aren-t-append-only-model#comment13Wed, 09 Sep 2009 11:01:28 GMTAh yes, Append-Only with NHibernate.. This is something I'm tinkering with but have been running into a few hurdles. I'm trying to avoid having to do stuff like Evicts. So far meaningless PKs are a must, but this raises issues on how to tie in locking & ensure you aren't appending to an already appended object, and how to ensure cached references reflect the latest entities reliably. I'm looking forward to that post. Building audit trails after the fact with NHibernate around a CRUD system is a really, really messy operation. (Been there, debugged that.) http://ayende.com/4179/soft-deletes-aren-t-append-only-model#comment12http://ayende.com/4179/soft-deletes-aren-t-append-only-model#comment12Wed, 09 Sep 2009 04:16:22 GMTAppend Only also allow different systems to collect information about the same entity. This is the approach taken by openEHR (openehr.org), which allows any number of systems to collect information about a person without fear of conflicts when consolidating records. Not a standard SQL Data Model though. http://ayende.com/4179/soft-deletes-aren-t-append-only-model#comment11http://ayende.com/4179/soft-deletes-aren-t-append-only-model#comment11Tue, 08 Sep 2009 21:57:14 GMTHarry, I'll have a post about it. http://ayende.com/4179/soft-deletes-aren-t-append-only-model#comment10http://ayende.com/4179/soft-deletes-aren-t-append-only-model#comment10Tue, 08 Sep 2009 16:13:18 GMTBertrand, In the DB level, you can't really use constraints. But something that would be easy to do is to have a separate reporting model (physical one) where you do updates. You only read from it to show stuff. http://ayende.com/4179/soft-deletes-aren-t-append-only-model#comment9http://ayende.com/4179/soft-deletes-aren-t-append-only-model#comment9Tue, 08 Sep 2009 14:02:12 GMTImmutable rows... I'm wondering what it would be like if such constraints could be modeled at the database level, and what kind of optimization could be applied to the database engine to favor last version reading for example, or efficient and transparent storage. Also wondering about whether the transaction log has aspects of a different implementation of something similar. Vague questions, I know, thinking out loud and way out of my area of expertise... http://ayende.com/4179/soft-deletes-aren-t-append-only-model#comment8http://ayende.com/4179/soft-deletes-aren-t-append-only-model#comment8Tue, 08 Sep 2009 04:49:48 GMTHarry: That Greg Young talk above talks about it some. Udi Dahan has a talk that also addresses this: [www.infoq.com/.../Making-Roles-Explicit-Udi-Dahan](http://www.infoq.com/presentations/Making-Roles-Explicit-Udi-Dahan) (I watched the NDC 2009 video, not sure if this is all the same stuff). Anyways, the root of most of this is working in Domain Events. Moving to events allows you to have multiple handlers with each (potentially) having their own tailor designed model (domain, reporting, logging, etc). Udi Dahan is a good source on this topic: [www.udidahan.com/.../domain-events-salvation/](http://www.udidahan.com/2009/06/14/domain-events-salvation/)http://ayende.com/4179/soft-deletes-aren-t-append-only-model#comment7http://ayende.com/4179/soft-deletes-aren-t-append-only-model#comment7Mon, 07 Sep 2009 04:50:50 GMTSince you brought it up, Oren, can you shed some light from your experience how do you approach the 'Append Only' model? And, how do you use NHibernate to tackle that (if you use NHibernate at all)? You mentioned the reporting should be done differently, how should we do that without all the labors of ADO.NET / Stored Procedures /SQL ... http://ayende.com/4179/soft-deletes-aren-t-append-only-model#comment6http://ayende.com/4179/soft-deletes-aren-t-append-only-model#comment6Sun, 06 Sep 2009 20:53:17 GMTI've been working in this area for a while. I call this technique "historic modeling". I've written a set of rules, a walkthrough, and a library in support of this idea. [http://historicmodeling.com](http://historicmodeling.com) [http://correspondence.codeplex.com](http://correspondence.codeplex.com) http://ayende.com/4179/soft-deletes-aren-t-append-only-model#comment5http://ayende.com/4179/soft-deletes-aren-t-append-only-model#comment5Sun, 06 Sep 2009 17:35:40 GMTGreg Young has a good talk about this: [www.infoq.com/.../greg-young-unshackle-qcon08](http://www.infoq.com/presentations/greg-young-unshackle-qcon08) Its very interesting stuff. Taking CQS to the architecture level is something I find very interesting but also daunting. It would make code so much cleaner, but the level of infrastructure is rarely required for the simple things I do -- more than CRUD, less than DDD. http://ayende.com/4179/soft-deletes-aren-t-append-only-model#comment4http://ayende.com/4179/soft-deletes-aren-t-append-only-model#comment4Sun, 06 Sep 2009 17:02:54 GMTIt's a paradox that a record is "more" deleted by editing out all its fields than by deleting it in the application which use a isDeleted column. http://ayende.com/4179/soft-deletes-aren-t-append-only-model#comment3http://ayende.com/4179/soft-deletes-aren-t-append-only-model#comment3Sun, 06 Sep 2009 15:21:04 GMTHow about only doing mutations? So you would still only do inserts but not with the whole information but only with what got changed. -Mark http://ayende.com/4179/soft-deletes-aren-t-append-only-model#comment2http://ayende.com/4179/soft-deletes-aren-t-append-only-model#comment2Sun, 06 Sep 2009 10:16:45 GMTA great post as always :) Some times using an append only approach can't be avoided specially if a financial solution is involved and each record in database means real money but as always the DB size and performance issues are my concerns in this kind of approach . I'd like to know if there are any guidelines/best practices/advices on designing such kind of databases. As I realized Microsoft uses separate tables for processing instances and completed instances (in BizTalk for example) although I'm not sure if they are using apply only approach. Thanks again http://ayende.com/4179/soft-deletes-aren-t-append-only-model#comment1http://ayende.com/4179/soft-deletes-aren-t-append-only-model#comment1Sun, 06 Sep 2009 10:14:52 GMT