versioning of a table

Question

anybody has seen any examples of a table with multiple versions for each record

something like if you would had the table

Person(Id, FirstName, LastName)

and you change a record's LastName than you would have both versions of LastName (first one, and the one after the change)

Answer 1

yes generally it is used
1)concurrency control
2)saving history

have a look http://stackoverflow.com/questions/27499/database-what-is-multiversion-concurrency-control-mvcc-and-who-supports-it

Answer 2

I've seen this done two ways. The first is in the table itself by adding an EffectiveDate and CancelDate (or somesuch). To get the current for a given record, you'd do something like: SELECT Id, FirstName, LastName FROM Table WHERE CancelDate IS NULL

The other is to have a global history table (which holds all of your historical data). The structure for such a table normally looks something like

Id bigint not null,
TableName nvarchar(50),
ColumnName nvarchar(50),
PKColumnName nvarchar(50),
PKValue bigint, //or whatever datatype
OriginalValue nvarchar(max),
NewValue nvarchar(max),
ChangeDate datetime

Then you set a trigger on your tables (or, alternatively, add a policy that all of your Updates/Inserts will also insert into your HX table) so that the correct data is logged.

Answer 3

The way we're doing it (might not be the best way) is to have an active bit field, and a foreign key back to the parent record. So for general queries you would filter on active employees, but you can get the history of a single employee with their Employee ID.

declare @employees
(
    PK_emID int identity(1,1),
    EmployeeID int,
    FirstName varchar(50),
    LastName varchar(50),
    Active bit,
    FK_EmployeeID int
    primary key(PK_emID)
)


insert into @employees
(
    EmployeeID,
    FirstName,
    LastName,
    Active,
    FK_EployeeID
)
select 1, 'David', 'Engle', 1,null
union all
select 2, 'Amy', 'Edge', 0,null
union all
select 2, 'Amy','Engle',1,2