ORM or database that works like version control and keeps track of history

Question

This is just a random idea that I was interested in. Are there ORMs or databases that support tracking history? I assume that each row has a composite primary key with identifier and version. The update will “add” a new line with the same identifier and the next available version number. A normal selection returns only the latest version of a uniquely defined row, but "SelectHistory" will return all rows. Delete would initially be a soft delete.

Given that this is not a new problem, and that there is nothing new under the sun, it struck me that there is a high probability that someone has already developed an elegant solution that abstracts the hard parts of it, and that it can be either on a level database, or at the ORM level.

I have an existing application that is built on the Entity Framework. It overrides the SaveChanges method for DbContext to set the CreatedBy / CreatedDate / ModifiedBy / ModifiedDate properties for each new / changed Entity. Recently, a client asked how much work would be required to add full history tracking. I gave them a reasonable assessment based on past experience with similar systems. My assessment included the addition of additional objects to the model logic and business layer to populate these objects. The client decided not to continue this work, but I wondered if there was a better way. Is there a better solution with Entity Framework? Are there any better solutions with other architectures?

Answer 1

What you describe is a form of multi-version concurrency control . Typically, MVCC is used under covers using a database implementation to avoid blocking during read-only transactions, but can also be made explicit, as you suggest. It discusses several different options for implementing explicit MVCC in this matter . If you also keep track of the generated file for each version, you have everything you need for a temporary database .