<p>When designing a database to use MVCC (Multi-Version Concurrency Control), you create tables with either a boolean field like "IsLatest" or an integer "VersionId", and you never do any updates, you only insert new records when things change.  </p>

<p>MVCC gives you automatic auditing for applications that require a detailed history, and it also relieves pressure on the database with regards to update locks.  The cons are that it makes your data size much bigger and slows down selects, due to the extra clause necessary to get the latest version.  It also makes foreign keys more complicated.</p>

<p>(Note that I'm <em>not</em> talking about the native MVCC support in RDBMSs like SQL Server's snapshot isolation level)</p>

<p>This has been discussed in other posts here on Stack Overflow. [todo - links]</p>

<p>I am wondering, which of the prevalent entity/ORM frameworks (Linq to Sql, ADO.NET EF, Hibernate, etc) can cleanly support this type of design?  This is a major change to the typical ActiveRecord design pattern, so I'm not sure if the majority of tools that are out there could help someone who decides to go this route with their data model.  I'm particularly interested in how foreign keys would be handled, because I'm not even sure of the best way to data model them to support MVCC.</p>

[{'answer_id': 46753, 'author': 'Guy Starbuck', 'author_id': 2194, 'author_profile': 'https://Stackoverflow.com/users/2194', 'pm_score': 1, 'selected': False, 'text': '<p>To the best of my knowledge, ORM frameworks are going to want to generate the CRUD code for you, so they would have to be explicitly designed to implement a MVCC option; I don\'t know of any that do so out of the box.</p>\n\n<p>From an Entity framework standpoint, CSLA doesn\'t implement persistence for you at all -- it just defines a "Data Adapter" interface that you use to implement whatever persistence you need.  So you could set up code generation (CodeSmith, etc.) templates to auto-generate CRUD logic for your CSLA entities that go along with a MVCC database architecture.</p>\n\n<p>This approach would work with any entity framework, most likely, not just CSLA, but it would be a very "clean" implementation in CSLA.</p>\n'}, {'answer_id': 46784, 'author': 'Tim Howland', 'author_id': 4276, 'author_profile': 'https://Stackoverflow.com/users/4276', 'pm_score': 3, 'selected': True, 'text': "<p>I might consider implementing the MVCC tier purely in the DB, using stored procs and views to handle my data operations. Then you could present a reasonable API to any ORM that was capable of mapping to and from stored procs, and you could let the DB deal with the data integrity issues (since it's pretty much build for that). If you went this way, you might want to look at a more pure Mapping solution like IBatis or IBatis.net.</p>\n"}, {'answer_id': 50103, 'author': 'Zack Peterson', 'author_id': 83, 'author_profile': 'https://Stackoverflow.com/users/83', 'pm_score': 2, 'selected': False, 'text': '<p>I designed a database similarly (only INSERTs &mdash; no UPDATEs, no DELETEs).</p>\n\n<p>Almost all of my SELECT queries were against views of only the current rows for each table (highest revision number).</p>\n\n<p>The views looked like this&hellip;</p>\n\n<pre><code>SELECT\n    dbo.tblBook.BookId,\n    dbo.tblBook.RevisionId,\n    dbo.tblBook.Title,\n    dbo.tblBook.AuthorId,\n    dbo.tblBook.Price,\n    dbo.tblBook.Deleted\nFROM\n    dbo.tblBook INNER JOIN\n    (\n        SELECT\n            BookId,\n            MAX(RevisionId) AS RevisionId\n        FROM\n            dbo.tblBook\n        GROUP BY\n            BookId\n    ) AS CurrentBookRevision ON\n    dbo.tblBook.BookId = CurrentBookRevision.BookId AND\n    dbo.tblBook.RevisionId = CurrentBookRevision.RevisionId\nWHERE\n    dbo.tblBook.Deleted = 0\n</code></pre>\n\n<p>And my inserts (and updates and deletes) were all handled by stored procedures (one per table).</p>\n\n<p>The stored procedures looked like this&hellip;</p>\n\n<pre><code>ALTER procedure [dbo].[sp_Book_CreateUpdateDelete]\n    @BookId      uniqueidentifier,\n    @RevisionId  bigint,\n    @Title       varchar(256),\n    @AuthorId    uniqueidentifier,\n    @Price       smallmoney,\n    @Deleted     bit\nas\n    insert into tblBook\n        (\n            BookId,\n            RevisionId,\n            Title,\n            AuthorId,\n            Price,\n            Deleted\n        )\n    values\n        (\n            @BookId,\n            @RevisionId,\n            @Title,\n            @AuthorId,\n            @Price,\n            @Deleted\n        )\n</code></pre>\n\n<p>Revision numbers were handled per-transaction in the Visual Basic code&hellip;</p>\n\n<pre><code>Shared Sub Save(ByVal UserId As Guid, ByVal Explanation As String, ByVal Commands As Collections.Generic.Queue(Of SqlCommand))\n    Dim Connection As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("Connection").ConnectionString)\n    Connection.Open()\n    Dim Transaction As SqlTransaction = Connection.BeginTransaction\n    Try\n        Dim RevisionId As Integer = Nothing\n        Dim RevisionCommand As SqlCommand = New SqlCommand("sp_Revision_Create", Connection)\n        RevisionCommand.CommandType = CommandType.StoredProcedure\n        RevisionCommand.Parameters.AddWithValue("@RevisionId", 0)\n        RevisionCommand.Parameters(0).SqlDbType = SqlDbType.BigInt\n        RevisionCommand.Parameters(0).Direction = ParameterDirection.Output\n        RevisionCommand.Parameters.AddWithValue("@UserId", UserId)\n        RevisionCommand.Parameters.AddWithValue("@Explanation", Explanation)\n        RevisionCommand.Transaction = Transaction\n        LogDatabaseActivity(RevisionCommand)\n        If RevisionCommand.ExecuteNonQuery() = 1 Then \'rows inserted\n            RevisionId = CInt(RevisionCommand.Parameters(0).Value) \'generated key\n        Else\n            Throw New Exception("Zero rows affected.")\n        End If\n        For Each Command As SqlCommand In Commands\n            Command.Connection = Connection\n            Command.Transaction = Transaction\n            Command.CommandType = CommandType.StoredProcedure\n            Command.Parameters.AddWithValue("@RevisionId", RevisionId)\n            LogDatabaseActivity(Command)\n            If Command.ExecuteNonQuery() &lt; 1 Then \'rows inserted\n                Throw New Exception("Zero rows affected.")\n            End If\n        Next\n        Transaction.Commit()\n    Catch ex As Exception\n        Transaction.Rollback()\n        Throw New Exception("Rolled back transaction", ex)\n    Finally\n        Connection.Close()\n    End Try\nEnd Sub\n</code></pre>\n\n<p>I created an object for each table, each with constructors, instance properties and methods, create-update-delete commands, a bunch of finder functions, and IComparable sorting functions.  It was a huge amount of code.</p>\n\n<p>One-to-one DB table to VB object...</p>\n\n<pre><code>Public Class Book\n    Implements iComparable\n\n#Region " Constructors "\n\n    Private _BookId As Guid\n    Private _RevisionId As Integer\n    Private _Title As String\n    Private _AuthorId As Guid\n    Private _Price As Decimal\n    Private _Deleted As Boolean\n\n    ...\n\n    Sub New(ByVal BookRow As DataRow)\n        Try\n            _BookId = New Guid(BookRow("BookId").ToString)\n            _RevisionId = CInt(BookRow("RevisionId"))\n            _Title = CStr(BookRow("Title"))\n            _AuthorId = New Guid(BookRow("AuthorId").ToString)\n            _Price = CDec(BookRow("Price"))\n        Catch ex As Exception\n            \'TO DO: log exception\n            Throw New Exception("DataRow does not contain valid Book data.", ex)\n        End Try\n    End Sub\n\n#End Region\n\n...\n\n#Region " Create, Update &amp; Delete "\n\n    Function Save() As SqlCommand\n        If _BookId = Guid.Empty Then\n            _BookId = Guid.NewGuid()\n        End If\n        Dim Command As SqlCommand = New SqlCommand("sp_Book_CreateUpdateDelete")\n        Command.Parameters.AddWithValue("@BookId", _BookId)\n        Command.Parameters.AddWithValue("@Title", _Title)\n        Command.Parameters.AddWithValue("@AuthorId", _AuthorId)\n        Command.Parameters.AddWithValue("@Price", _Price)\n        Command.Parameters.AddWithValue("@Deleted", _Deleted)\n        Return Command\n    End Function\n\n    Shared Function Delete(ByVal BookId As Guid) As SqlCommand\n        Dim Doomed As Book = FindByBookId(BookId)\n        Doomed.Deleted = True\n        Return Doomed.Save()\n    End Function\n\n    ...\n\n#End Region\n\n...\n\n#Region " Finders "\n\n    Shared Function FindByBookId(ByVal BookId As Guid, Optional ByVal TryDeleted As Boolean = False) As Book\n        Dim Command As SqlCommand\n        If TryDeleted Then\n            Command = New SqlCommand("sp_Book_FindByBookIdTryDeleted")\n        Else\n            Command = New SqlCommand("sp_Book_FindByBookId")\n        End If\n        Command.Parameters.AddWithValue("@BookId", BookId)\n        If Database.Find(Command).Rows.Count &gt; 0 Then\n            Return New Book(Database.Find(Command).Rows(0))\n        Else\n            Return Nothing\n        End If\n    End Function\n</code></pre>\n\n<p>Such a system preserves all past versions of each row, but can be a real pain to manage.</p>\n\n<p><strong>PROS:</strong></p>\n\n<ul>\n<li>Total history preserved</li>\n<li>Fewer stored procedures</li>\n</ul>\n\n<p><strong>CONS:</strong></p>\n\n<ul>\n<li>relies on non-database application for data integrity</li>\n<li>huge amount of code to be written</li>\n<li>No foreign keys managed within database (goodbye automatic Linq-to-SQL-style object generation)</li>\n<li>I still haven\'t come up with a good user interface to retrieve all that preserved past versioning.</li>\n</ul>\n\n<p><strong>CONCLUSION:</strong></p>\n\n<ul>\n<li>I wouldn\'t go to such trouble on a new project without some easy-to-use out-of-the-box ORM solution.</li>\n</ul>\n\n<p>I\'m curious if the Microsoft Entity Framework can handle such database designs well.</p>\n\n<p>Jeff and the rest of that Stack Overflow team must have had to deal with similar issues while developing Stack Overflow:  Past revisions of edited questions and answers are saved and retrievable.</p>\n\n<p>I believe Jeff has stated that his team used Linq to SQL and MS SQL Server.</p>\n\n<p>I wonder how they handled these issues.</p>\n'}, {'answer_id': 50207, 'author': 'Quibblesome', 'author_id': 1143, 'author_profile': 'https://Stackoverflow.com/users/1143', 'pm_score': 0, 'selected': False, 'text': "<p>I always figured you'd use a db trigger on update and delete to push those rows out into a TableName_Audit table.</p>\n\n<p>That'd work with ORMs, give you your history and wouldn't decimate select performance on that table. Is that a good idea or am I missing something?</p>\n"}, {'answer_id': 268080, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 1, 'selected': False, 'text': '<p>Check out the Envers project - works nice with JPA/Hibernate applications and basically does that for you - keeps track of different versions of each Entity in another table and gives you SVN-like possibilities ("Gimme the version of Person being used 2008-11-05...")</p>\n\n<p><a href="http://www.jboss.org/envers/" rel="nofollow noreferrer">http://www.jboss.org/envers/</a></p>\n\n<p>/Jens</p>\n'}, {'answer_id': 268131, 'author': 'Matthew Watson', 'author_id': 3839, 'author_profile': 'https://Stackoverflow.com/users/3839', 'pm_score': 0, 'selected': False, 'text': "<p>What we do, is just use a normal ORM ( hibernate ) and handle the MVCC with views + instead of triggers.</p>\n\n<p>So, there is a v_emp view, which just looks like a normal table, you can insert and update into it fine, when you do this though, the triggers handle actually inserting the correct data into the base table.</p>\n\n<p>Not.. I hate this method :) I'd go with a stored procedure API as suggested by Tim.</p>\n"}]
['https://Stackoverflow.com/questions/46709', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1219/']