Database Design for Revisions?

Question

We have a requirement in the project to store all the revisions(Change History) for the entities in the database. Currently, we have 2 designed proposals for this:

e.g. for "Employee" Entity

Design 1:

-- Holds Employee Entity

"Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)"

-- Holds the Employee Revisions in XML. The RevisionXML will contain

-- all data of that particular EmployeeId

"EmployeeHistories (EmployeeId, DateModified, RevisionXML)"

Design 2:

-- Holds Employee Entity

"Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)"

-- In this approach, we have basically duplicated all the fields on Employees 

-- in the EmployeeHistories and storing the revision data.

"EmployeeHistories (EmployeeId, RevisionId, DateModified, FirstName, 

      LastName, DepartmentId, .., ..)"

Is there any other way of doing this thing?

The problem with the "Design 1" is that we have to parse XML each time when you need to access data. This will slow the process and also add some limitations like we cannot add joins on the revisions data fields.

And the problem with the "Design 2" is that we have to duplicate each and every field on all entities (We have around 70-80 entities for which we want to maintain revisions).

Answer 1

Design 2 has problems with schema changes. For example, if you are changing the Employees table, then you need to change the EmployeeHistories table and all the related sprocs that go with it.

 Whereas Design 1 works well and if done properly, it'll be cost-effective in terms of the performance hit. 

You can also use an XML schema and even indexes to get over possible performance problems. Comment, as you have mentioned about parsing the XML, is valid but you can easily create the view using XQuery, which can be included in queries and join to like this:

CREATE VIEW EmployeeHistory AS, FirstName, , DepartmentId

SELECT EmployeeId, RevisionXML.value('(/employee/FirstName)[1]', 'varchar(50)') AS FirstName,

RevisionXML.value('(/employee/LastName)[1]', 'varchar(100)') AS LastName,

RevisionXML.value('(/employee/DepartmentId)[1]', 'integer') AS DepartmentId,

FROM EmployeeHistories 

Note: Do not put it all in one table with an IsCurrent discriminator attribute. This just causes problems down the line requires surrogate keys and all sorts of other problems.