You will find there's requirement in project to keep all of the revisions(Change History) for that organizations within the database. Presently we now have 2 designed plans with this:

e.g. for "Worker" Entity

Design 1:

-- Holds Worker Entity

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

-- Supports the Worker Revisions in Xml. The RevisionXML will contain

-- all data of this particular EmployeeId

"EmployeeHistories (EmployeeId, DateModified, RevisionXML)"

Design 2:

-- Holds Worker Entity

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

-- Within this approach we now have essentially copied all of the fields on Employees

-- within the EmployeeHistories and storing the revision data.

"EmployeeHistories (EmployeeId, RevisionId, DateModified, FirstName,

      LastName, DepartmentId, .., ..)"

Can there be every other method of carrying this out factor?

The issue using the "Design 1" is the fact that we must parse XML every time if you want to gain access to data. This can slow the procedure as well as then add restrictions like we can't add joins around the revisions data fields.

And also the trouble with the "Design 2" is the fact that we must duplicate every single area on all organizations (We now have around 70-80 organizations that you want to maintain revisions).

I believe the important thing question to request here's 'Who / What will be utilising the history'?

If it will likely be mostly for confirming / human readable history, we have implemented this plan previously...

Produce a table known as 'AuditTrail' or something like that which has the next fields...

[ID] [int] IDENTITY(1,1) NOT NULL,

[UserID] [int] NULL,

[EventDate] [datetime] NOT NULL,

[TableName] [varchar](50) NOT NULL,

[RecordID] [varchar](20) NOT NULL,

[FieldName] [varchar](50) NULL,

[OldValue] [varchar](5000) NULL,

[NewValue] [varchar](5000) NULL

After that you can give a 'LastUpdatedByUserID' column to all your tables that ought to be set each time you need to do an update / place up for grabs.

After that you can give a trigger to each table to trap any place / update that occurs and produces an entry within this table for every area that's transformed. Since the table may also be provided using the 'LastUpdateByUserID' for every update / place, you have access to this value within the trigger and employ it when contributing to the audit table.

We make use of the RecordID area to keep the need for the important thing area on the table being up-to-date. Whether it's a combined key, we simply perform a string concatenation having a '~' between your fields.

I am sure this technique might have disadvantages - for heavily up-to-date databases the performance might be hit, however for my web-application, we obtain a lot more reads than creates also it appears to become carrying out pretty much. We even authored just a little VB.Internet utility to instantly write the triggers in line with the table definitions.

Only a thought!

  1. Do not place it all-in-one table by having an IsCurrent descriminator attribute. This just causes problems down the road, requires surrogate secrets and many types of other issues.
  2. Design 2 does experience schema changes. Should you alter the Employees table you need to alter the EmployeeHistories table and all sorts of the related sprocs which go by using it. Potentially doubles you schema change effort.
  3. Design 1 is effective and when done correctly doesn't cost much when it comes to a performance hit. You could utilize an xml schema as well as indexes to conquer possible performance problems. Your comment about parsing the xml applies however, you could easily produce a view using xquery - which you'll use in queries and join to. Something similar to this...

    CREATE VIEW EmployeeHistory AS , FirstName, , DepartmentId

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

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

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

    FROM EmployeeHistories

We now have implemented an answer much like the answer that Balloon indicates, which works pretty much for all of us.

Only difference is the fact that we simply keep new value. That old value is in the end saved in the earlier history row

[ID] [int] IDENTITY(1,1) NOT NULL,

[UserID] [int] NULL,

[EventDate] [datetime] NOT NULL,

[TableName] [varchar](50) NOT NULL,

[RecordID] [varchar](20) NOT NULL,

[FieldName] [varchar](50) NULL,

[NewValue] [varchar](5000) NULL

Allows if you have a table with 20 posts. By doing this you just keep exact column which has transformed rather than needing to keep entire row.