SQL 2005/2008 Database track field change

I do not want Auditing or History tracking.

I have an application that pulls its data from a external source. We mirror the external tables in our own DB. We allow our users to update the data in our DB. Next time the system syncs with the external data, we only override fields that we have not changed locally.

Off the top of my head I can think of 2 ways to do this

1) Store 2 Rows for each Object. First 1 is the external version, the 2nd row links to the external version but will only have data in a field if that field has been changed. e.g.

id      |     parentId    |    field1      | field2
1       |      null       |     foo        |   bar
2       |        1        |     new foo    |   null

This illustrates what the data would look like when a local user changed field1. If no change occurred there would only be the first row.

2) Double the number of columns in the table. e.g name_external name_internal

I like option 1 better as it seems like it would provides better separation and easier to query and to do in code comparisons between the 2 objects. The only downside is that it will result in more rows, but the DB will be pretty small.

Is there any other patterns I could use? Or a reason I shouldn't go with option 1.

I will be using .NET 4 WCF services

Solution

I will go with the two table answer provided below. and use the following SQL to return a Row that has the fields which have changed locally merged with the orginal values

SELECT 
    a.[ID], 
    isnull(b.[firstName], a.[firstName]),
    isnull(b.[lastName], a.[lastName]), 
    isnull(b.[dob], a.[dob]), 
    isnull(b.active, a.active)
From tbl1 a
left join tbl2 b on a.[ID] = b.[ID]

As in my the DB will only ever be able to be updated via the UI system. And I can ensure people are not allowed to enter NULL as a value instead I force them to enter a blank string. This will allow me to overcome the issue what happens if the user updates a value to NULL

There are two issues I can think of if you choose option 1.

Allowing users to update the data means you will either have to write procedures to perform the insert/update/delete statements for them, managing the double row structure, or you have to train all the users to update the table correctly.

The other problem is modelling fields in your table which can be NULL. If you are using NULL to represent the field has not changed how can you represent a field changing to NULL?

The second option of doubling the number of columns avoids the complexity of updates and allows you to store NULL values but you may see performance decrease due to the increased row size and therefore the amount of data the server has to move around (without testing it I realise this claim is unsubstantiated but I thought it worth mentioning).

Another suggestion would be to duplicate the tables, perhaps putting them in another schema, which hold a snapshot of the data just after the sync with the external data source is performed.

I know you are not wanting a full audit, this would be a copy of the data at a point in time. You can then allow users to modify the data as they wish, without the complexity of the double row/column, and when you come to re-sync with the external source you can find out which fields have changed.

I found a great article: The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION! describing such a comparison.

Consider having an update trigger that updates an UpdatedBy field. We have a user for our imports that no other process is allowed to use that updates the field if they were done by the import. So if any value other than user -1 was the last to update you can easily tell. Then you can use the merge statment to insert/delete/update.