Suggested techniques for storing multiple versions of SQL row data

Question

I am developing an application that is required to store previous versions of database table rows to maintain a history of changes. I am recording the history in the same table but need the most current data to be accessible by a unique identifier that doesn't change with new versions. I have a few ideas on how this could be done and was just looking for some ideas on the best way of doing this or whether there is any reason not to use one of my ideas:

1. Create a new row for each row version, with a field to indicate which row was the current row. The drawback of this is that the new version has a different primary key and any references to the old version will not return the current version.

2. When data is updated, the old row version is duplicated to a new row, and the new version replaces the old row. The current row can be accessed by the same primary key.

3. Add a second table with only a primary key, add a column to the other table which is foreign key to new table's primary key. Use same method as described in option 1 for storing multiple versions and create a view which finds the current version by using the new table's primary key.

Answer 1

PeopleSoft uses (used?) "effective dated records". It took a little while to get the hang of it, but it served its purpose. The business key is always extended by an EFFDT column (effective date). So if you had a table EMPLOYEE[EMPLOYEE_ID, SALARY] it would become EMPLOYEE[EMPLOYEE_ID, EFFDT, SALARY].

To retrieve the employee's salary:

SELECT e.salary
  FROM employee e
  WHERE employee_id = :x
    AND effdt = (SELECT MAX(effdt)
                   FROM employee
                   WHERE employee_id = :x
                     AND effdt <= SYSDATE)

An interesting application was future dating records: you could give every employee a 10% increase effective Jan 1 next year, and pre-poulate the table a few months beforehand. When SYSDATE crosses Jan 1, the new salary would come into effect. Also, it was good for running historical reports. Instead of using SYSDATE, you plug in a date from the past in order to see the salaries (or exchange rates or whatever) as they would have been reported if run at that time in the past.

In this case, records are never updated or deleted, you just keep adding records with new effective dates. Makes for more verbose queries, but it works and starts becoming (dare I say) normal. There are lots of pages on this, for example: http://peoplesoft.wikidot.com/effective-dates-sequence-status

Answer 2

#3 is probably best, but if you wanted to keep the data in one table, I suppose you could add a datetime column that has a now() value populated for each new row and then you could at least sort by date desc limit 1.

Overall though - multiple versions needs more info on what you want to do effectively as much as programatically...ie need more info on what you want to do.

R

Answer 3

Have you considered using AutoAudit?

AutoAudit is a SQL Server (2005, 2008) Code-Gen utility that creates Audit Trail Triggers with:

• Created, CreatedBy, Modified, ModifiedBy, and RowVersion (incrementing INT) columns to table
• Insert event logged to Audit table
• Updates old and new values logged to Audit table
• Delete logs all final values to the Audit tbale
• view to reconstruct deleted rows
• UDF to reconstruct Row History
• Schema Audit Trigger to track schema changes
• Re-code-gens triggers when Alter Table changes the table

Answer 4

For me, history tables are always separate. So, definitely I would go with that, but why create some complex versioning thing where you need to look at the current production record. In reporting, this results in nasty unions that are really unnecessary.

Table has a primary key and who cares what else.
TableHist has these columns: incrementing int/bigint primary key, history written date/time, history written by, record type (I, U, D for insert, update, delete), the PK from Table as an FK on TableHist, the remaining columns all other columns with the same name are in the TableHist table.

If you create this history table structure and populate it via triggers on Table, you will have all versions of every row in the tables you care about and can easily determine the original record, every change, and the deletion records as well. AND if you are reporting, you only need to use your historical tables to get all of the information you'd like.

Answer 5

create table table1 (
    Id int identity(1,1) primary key,
    [Key] varchar(max),
    Data varchar(max)
)
go

create view view1 as
with q as (
    select [Key], Data, row_number() over (partition by [Key] order by Id desc) as 'r'
    from table1
)
select [Key], Data from q where r=1
go

create trigger trigger1 on view1 instead of update, insert as begin
    insert into table1 
    select [Key], Data 
    from (select distinct [Key], Data from inserted) a
end
go

insert into view1 values
     ('key1', 'foo')
    ,('key1', 'bar')

select * from view1

update view1
set Data='updated'
where [Key]='key1'

select * from view1

select * from table1

drop trigger trigger1
drop table table1
drop view view1

Results:

Key Data
key1    foo

Key Data
key1    updated

Id  Key Data
1   key1    bar
2   key1    foo
3   key1    updated

I'm not sure if the disctinct is needed.