versioning of a table

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

something like if you wou开发者_运维技巧ld 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)

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.

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