SQL - Type2 Tables

Consider the following SQL Server table

create table dbo.Staff
(
ID long identity
constraint staff_ID_PK
primary key,
NtUserName varchar(20) not null,
NameFirst varchar(20) not null,
NameLast varchar(20) not null,
DateHire date not null,
DateTermination date null,
ChangedBy varchar(40) not null, – this is the person who last changed this record
ChangedDate datetime not null – this is the time this record was last changed.
)
go

Usually there are more columns, but this will work for this example.

Generally speaking, for a Staff table you want to keep a history of changes. There are several ways to accommplish this:

• Create a history table (perhaps called Staff_History), add triggers to the staff table, when a record is added, updated, or deleted, it adds a record to the Staff_History table.
• Convert the Staff table into a history table.

Here is an example of a Type2 table

create table dbo.Staff
(
– simple counter - a unique number
ID bigint identity
constraint Staff_ID_Unique
unique,

-- Staff Internal Key 
-- For example the position table will include
--  a column of StaffID which will reference this
--  particular staff record (actually it will reference
--  the one where isCurrent=1)
StaffID             bigint not null    
    constraint Staff_StaffId_PK
        primary key,

NtUserName          varchar(20) not null,
NameFirst           varchar(20) not null,
NameLast            varchar(20) not null,
DateHire            date not null,
DateTermination     date null,

StartDate           datetime not null,   -- version start
EndDate             datetime null,       -- version end (null = current)

-- IsCurrent = 1: Marks the current row per StaffID.
IsCurrent           bit not null
    constraint DF_Staff_IsCurrent default 1,

ChangedBy           varchar(40) not null,
ChangedDate         datetime not null

);
go

create unique index Staff_NtUserName_IsCurrent_Unique
on dbo.Staff(NtUserName)
where IsCurrent = 1;
go

Imagine, an employee is added to the staff table (on Jan 1), and over the course of that employee’s career, the staff record is changed a few times. (maybe there was a name change (on Feb 1), and a little while later, the NTUserName was changed (March 1). Finally that person leaves (June 1)). This means the staff record was changed 4 times.

What will happen is, instead of changing the record 4 times, we will create 4 records. A new record each time the record is changed.

What happens is we’ve added a few new columns

• StartDate and EndDate - will be set showing when the staff record was was effective.
• on Jan 1 - there is one staff record, it’s EndDate will be blank
• on Feb 1 (or shortly after), there are two staff records. The first dated Jan 1 - Jan 31. The second will have an updated last name and will have a start date of Feb 1.
• This IsCurrent column will only be set on the record that is current.

To make this work, it’s usually advisable to update those records using stored procedures. Here’s what they might look like.

create procedure dbo.Staff_Insert
@NtUserName varchar(20),
@NameFirst varchar(20),
@NameLast varchar(20),
@DateHire date,
@DateTermination date = null,
@ChangedBy varchar(40)
as
begin
set nocount on;

--
-- here the staffid is a random number. The problem occurs if 
-- it generates the same random number multiple times. 
declare @StaffID bigint = cast(round(rand() * 1000000000, 0) as bigint); -- or use a sequence or other logic

insert into dbo.Staff (
    ID, StaffID, NtUserName, NameFirst, NameLast,
    DateHire, DateTermination,
    StartDate, EndDate,
    IsCurrent, ChangedBy, ChangedDate
)
values (
    default, @StaffID, @NtUserName, @NameFirst, @NameLast,
    @DateHire, @DateTermination,
    getutcdate(), null,
    1, @ChangedBy, getutcdate()
);

end
go

create procedure dbo.Staff_Update
@StaffID bigint,
@NtUserName varchar(20),
@NameFirst varchar(20),
@NameLast varchar(20),
@DateHire date,
@DateTermination date = null,
@ChangedBy varchar(40)
as
begin
set nocount on;

declare @CurrentDate datetime = getutcdate();

-- Mark current record as outdated
update dbo.Staff
set
    EndDate = @CurrentDate,
    IsCurrent = 0,
    ChangedBy = @ChangedBy,
    ChangedDate = @CurrentDate
where StaffID = @StaffID and IsCurrent = 1;

-- Insert new version
insert into dbo.Staff (
    ID, StaffID, NtUserName, NameFirst, NameLast,
    DateHire, DateTermination,
    StartDate, EndDate,
    IsCurrent, ChangedBy, ChangedDate
)
values (
    default, @StaffID, @NtUserName, @NameFirst, @NameLast,
    @DateHire, @DateTermination,
    @CurrentDate, null,
    1, @ChangedBy, @CurrentDate
);

end
go

create procedure dbo.Staff_Delete_Soft
@StaffID bigint,
@ChangedBy varchar(40)
as
begin
set nocount on;

update dbo.Staff
set
    EndDate = getutcdate(),
    IsCurrent = 0,
    ChangedBy = @ChangedBy,
    ChangedDate = getutcdate()
where StaffID = @StaffID and IsCurrent = 1;

end
go

create procedure dbo.Staff_Delete_Hard
@StaffID bigint
as
begin
set nocount on;

delete from dbo.Staff
where StaffID = @StaffID;

end
go