Table Patterns and Changing Data
by Todd Schrmal


Listing One

/*  TABLE: Employee_Current_Only  */

CREATE TABLE Employee_Current_Only(
    Employee_ID          int             NOT NULL,
    Employee_Name        varchar(100)    NOT NULL,
    Employment_Status    varchar(10)     NOT NULL,
    Create_Dt            datetime        NOT NULL,
    Create_ID            char(10)        NOT NULL,
    Altered_Dt           datetime        NOT NULL,
    Altered_ID           char(10)        NOT NULL,
    CONSTRAINT PK_Employee_Current_Only PRIMARY KEY CLUSTERED (Employee_ID)
)
go

IF OBJECT_ID('Employee_Current_Only') IS NOT NULL
    PRINT '<<< CREATED TABLE Employee_Current_Only >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE Employee_Current_Only >>>'
go

/*  TABLE: Employee_Functional_History  */
CREATE TABLE Employee_Functional_History(
    Employee_ID          int             NOT NULL,
    Start_Dt             datetime        NOT NULL,
    Employee_Name        varchar(100)    NOT NULL,
    Employment_Status    varchar(10)     NOT NULL,
    Create_Dt            datetime        NOT NULL,
    Create_ID            char(10)        NOT NULL,
    Altered_Dt           datetime        NOT NULL,
    Altered_ID           char(10)        NOT NULL,
    Active_Ind           char(1)         NOT NULL,
    Stop_Dt              datetime        NULL,
    CONSTRAINT PK_Employee_Functional_History PRIMARY 
                                    KEY CLUSTERED (Employee_ID, Start_Dt)
)
go

IF OBJECT_ID('Employee_Functional_History') IS NOT NULL
    PRINT '<<< CREATED TABLE Employee_Functional_History >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE Employee_Functional_History >>>'
go

/*  TABLE: Employee_Row_Level_Audit  */

CREATE TABLE Employee_Row_Level_Audit(
    Employee_ID          int             NOT NULL,
    Start_Dt             datetime        NOT NULL,
    Employee_Name        varchar(100)    NOT NULL,
    Employment_Status    varchar(10)     NOT NULL,
    Create_Dt            datetime        NOT NULL,
    Create_ID            char(10)        NOT NULL,
    Altered_Dt           datetime        NOT NULL,
    Altered_ID           char(10)        NOT NULL,
    Active_Ind           char(1)         NOT NULL,
    Action_Code          char(10)        NOT NULL,
    CONSTRAINT PK_Employee_Row_Level_Audit PRIMARY 
                    KEY CLUSTERED (Employee_ID, Start_Dt)
)
go

/* TABLE: Employee_Column_Level_Audit_Option_1  */

CREATE TABLE Employee_Column_Level_Audit_Option_1(
    Employee_ID        int               NOT NULL,
    Event_Timestamp    datetime          NOT NULL,
    Column_Name        varchar(80)       NOT NULL,
    Old_Text           varchar(100)      NULL,
    New_Text           varchar(100)      NULL,
    Old_Number         decimal(10, 2)    NULL,
    New_Number         decimal(10, 2)    NULL,
    Old_Date           datetime          NULL,
    New_Date           datetime          NULL,
    Event_ID           char(10)          NOT NULL,
    CONSTRAINT PK_Employee_Column_Level_Audit_Option_1 
                 PRIMARY KEY CLUSTERED (Employee_ID,  Event_Timestamp)
)
go

IF OBJECT_ID('Employee_Column_Level_Audit_Option_1') IS NOT NULL
    PRINT '<<< CREATED TABLE Employee_Column_Level_Audit_Option_1 >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE Employee_Column_Level_Audit_Option_1 >>>'
go

/*  TABLE: Employee_Column_Level_Audit_Option_2  */

CREATE TABLE Employee_Column_Level_Audit_Option_2(
    Employee_ID        int             NOT NULL,
    Event_Timestamp    datetime        NOT NULL,
    Column_Name        varchar(80)     NOT NULL,
    Old_Text           varchar(100)    NULL,
    New_Text           varchar(100)    NULL,
    Old_Number         varchar(30)     NOT NULL,
    Event_ID           char(10)        NOT NULL,
    CONSTRAINT PK_Employee_Column_Level_Audit_Option_2 
             PRIMARY KEY CLUSTERED (Employee_ID,  Event_Timestamp)
)
go

IF OBJECT_ID('Employee_Column_Level_Audit_Option_2') IS NOT NULL
    PRINT '<<< CREATED TABLE Employee_Column_Level_Audit_Option_2 >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE Employee_Column_Level_Audit_Option_2 >>>'
go

/*  TABLE: Employee_Paycheck_Ledger  */

CREATE TABLE Employee_Paycheck_Ledger(
    Employee_Paycheck_Event_Key    uniqueidentifier    NOT NULL,
    Employee_ID                    int                 NOT NULL,
    Pay_Date                       datetime            NOT NULL,
    Pay_Amount                     decimal(10, 2)      NOT NULL,
    Event_Type                     char(10)            NOT NULL,
    Created_Dt                     datetime            NOT NULL,
    Created_ID                     char(10)            NOT NULL,
    CONSTRAINT PK_Employee_Paycheck_Ledger PRIMARY 
                    KEY CLUSTERED (Employee_Paycheck_Event_Key)
)
go

IF OBJECT_ID('Employee_Paycheck_Ledger') IS NOT NULL
    PRINT '<<< CREATED TABLE Employee_Paycheck_Ledger >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE Employee_Paycheck_Ledger >>>'
go

IF OBJECT_ID('Employee_Row_Level_Audit') IS NOT NULL
    PRINT '<<< CREATED TABLE Employee_Row_Level_Audit >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE Employee_Row_Level_Audit >>>'
go





3


