ORM or Database that works like version control and tracks history

Question

This is just a random idea I was wondering about. Are there ORM's or databases that natively support tracking history? I'm envisioning something were each row has a compound primary key with an Id and a Version. An Update would "append" a new row with the same Id and the next available Version number. A normal select only returns the most recent version of a uniquely identified row, but "SelectHistory" would return all rows. Delete would natively be a soft delete.

Given that this is not a new problem and that there's nothing new under the sun, it hit me that there's a high probability that someone already has designed an elegant solution that abstracts the hard parts of this away, and that it could be done at either the database layer or the ORM layer.

I have an existing application that is built on Entity Framework. It overrides the SaveChanges method of the DbContext to set a CreatedBy/CreatedDate/ModifiedBy/ModifiedDate properties for each new/changed Entity. The client recently asked how much work it would involve to add full history tracking. I gave them a reasonable estimate based on past experience with similar systems. My estimate involved adding additional entities to the model and business layer logic to populate those entities. Client decided not to pursue that work, but it got me to wondering if there was a better way. Is there a better solution with Entity Framework? Are there better solutions with other architectures?

Answer 1

What you describe is a form of multi-version concurrency control. Typically MVCC is used under the covers by a database implementation to avoid having to lock during read-only transactions, but it can also be made explicit as you suggest. There's a discussion of some different options for implementing explicit MVCC in this question. If you're also tracking a CreatedDate for each version, you've got everything you need for a temporal database.