In a "Revisional Data Model," where does code that preserves a previous record's state belong?

Question

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:

1. 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.
2. 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.
3. 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?

Answer 1

Let the database handle it all and store the history off in a different table.

The database is good at handling data and keeping it accessible and clean, use those strengths! Don't over complicate things to give the application extra control or extra flexibility unless you absolutely MUST have it. Also, don't store a ton of history in your live tables unless you don't like speedy results, dump it off somewhere where else so you can better optimize speed and costs.

Answer 2

I answered a similar question on SO some time ago. Here's something that might work.

---

One method that is used by a few wiki platforms is to separate the identifying data and the content you're tracking revisions for. It adds complexity, but you end up with a history of complete records.

So for example, if you had a table called Opportunities to track sales deals, you would actually create two separate tables:

Opportunities
Opportunities_Content (or something like that)

The Opportunities table would have information you'd use to uniquely identify the record and would house the primary key you'd reference for your foreign key relationships. The Opportunities_Content table would hold all the fields your users can change and for which you'd like to keep an audit trail. Each record in the Content table would include its own PK and the modified-by and modified-date data. The Opportunities table would include a reference to the current version as well as information on when the main record was originally created and by whom.

Here's a simple example based on ScrewTurn's data schema:

CREATE TABLE dbo.Page(  
    ID int PRIMARY KEY,  
    Name nvarchar(200) NOT NULL,  
    CreatedByName nvarchar(100) NOT NULL, 
    CurrentRevision int NOT NULL, 
    CreatedDateTime datetime NOT NULL

And the contents:

CREATE TABLE dbo.PageContent(
    PageID int NOT NULL,
    Revision int NOT NULL,
    Title nvarchar(200) NOT NULL,
    User nvarchar(100) NOT NULL,
    LastModified datetime NOT NULL,
    Comment nvarchar(300) NULL,
    Content nvarchar(max) NOT NULL,
    Description nvarchar(200) NULL

I would probably make the PK of the contents table a multi-column key from PageID and Revision provided Revision was an identity type. You would use the Revision column as the FK. You then pull the consolidated record by JOINing like this:

SELECT * FROM Page
JOIN PageContent ON CurrentRevision = Revision AND ID = PageID

There might be some errors up there...this is off the top of my head. It should give you an idea of an alternative pattern, though.

Josh

Answer 3

There is no clear-cut answer to this I'm afraid. It depends on what you want to do, how strict your requirements are and what tools you have available or want to use. To give an extreme example, banks don't just write a log on a file somewhere, but actually print that log line onto real paper...

What I did for one of my applications was your suggestion #3, where the live-data sits in optimized-for-access tables residing entirely in memory while the data for 'undeletes'/'rollbacks' is stored on traditional tables that have just an index on the entry-id.

But that's just me, because I have a lot of searches per second, but only few changes and rollbacks. Your requirements might differ. Maybe your application is more GoogleDocs-like where you expect changes to happen often, and even simultaneously, but don't need that fast lookups/searches. Then it might be better to store the base-document and keep track of change-deltas that happen over time.

Others again, that are already using an enterprise-DBMS like Oracle, might feel much more comfortable 'outsourcing' this to the database. Be it by using triggers, or specialized versioning Add-Ons available from the DBMS vendor directly. This could also be the right choice for your personally if the versioning and traceability is vital to the data and you want to offload the responsibilty of doing it properly to someone else.

And so on...

So you see, it really depends on your requirements

Answer 4

Soft deletes such as these are a performance killer. The main reason for this is that each table needs to have a "IsDeleted" column, and because this will only contain 1 of 2 possible values, indexing this column is pointless as the selectivity is way too low. This means that each query against the table turns into a table scan to find all of the "Current" rows.

See the following article for more details about why this is a bad idea.

http://weblogs.asp.net/fbouma/archive/2009/02/19/soft-deletes-are-bad-m-kay.aspx

Answer 5

First, to answer to your question: To which layer does that code belong?

I would go with the layer of your actual code repository and not your database (procedures, events, etc). That think looks for me to be a business logic, and is good to keep all the business logic in the same layer (now, we talk about the code layer vs database layer)

How will I do it?

Do you really need to keep your historical data in the same way? normalized?

You could have a single table like this

• id
• table_pm_key
• table
• serialized object (you may define what ever means that: attributes, relationships)
• revision_id
• revision_data
• last_mark - optional
• parent_revision -optional

In your ORM code, you could listen for update/delete on tables you want, and add automatically to this table