When I say "Revisional Data Model", I mean a data model where information is never lost: Deletes never destroy any rows, and updates always cause an insert somewhere else to preserve a row's previous state before the update.
There are a few I've read about, and heard from colleagues:
- Triggers in the database. The main reason why I don't like this approach is because if you depend on triggers to enforce revision integrity, the application becomes tied to the database server. If using an ORM with different providers (Oracle, MySQL, T-SQL, etc), you may need to alter some SQL to switch between them.
- Human-readable trace logs. Last year I played Business Analyst for a project with a new developer because one of our sister departments was slammed with important deadlines. She insisted that, despite the requirements I gathered and the schemas I modeled, she would insert a stored procedure to be executed with each create/update/delete statement. This sproc would just write out the old data as a formatted string, and save it to a ERRORS_LOG table in the database. Her goal was to help her with debugging the application in case anything went wrong: she wanted a trace.
- Creating sister tables. This approach is like having 2 databases living in the same context. For the table Person there would be a PersonHistory table, for Product there would be ProductHistory, and so on. The schemas would be identical, and each time a change happened in the entity table, the previous state would be inserted into ProductHistory.
If you have an application where you want to allow users to "undelete" or "restore previous version" of an entity, only #3 seems like a workable solution. However, since each pair (entity and its history) have the same model/schema, they can in fact be combined. By adding bool/bit properties like IsCurrentVersion and IsDeleted, you can slice a single entity set into different states within the WHERE clause. Consider this example #4.
One disadvantage of #4 is that the database tables become hard to read when you select all rows. Mining data from a db server ide would require pretty extensive use of views, which causes the same problem as in option #1. Looking at all of the different states together in the same table makes it much more difficult to read. With approach #3, you need to look in a different table to find previous versions & deletes.
We've been using approach #4 with success. We have a RevisableEntity supertype with properties that indicate when data was changed, who changed it, whether it was deleted, archived, or modified. Updates occur on rows just as they would under normal circumstances. However as part of the same action, a corresponding insert is made into the same table / entity set. The insert contains the same values as the update before it was updated. The updated row, in addition to whatever changes were made by the user, also updates the when and who properties.
Now for my question: Where does this code belong? In the domain, or in the repository implementation? There are certain aspects of this that are storage dependent, which hint at putting the code where your ORM coupling is. However there are other aspects that the domain / application layers should be able to trust. Maybe the domain entities should manage their own revisional integrity, or maybe there should be a factory to coordinate?