{"id":1965,"date":"2015-03-10T00:00:00","date_gmt":"2015-03-10T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/self-maintaining-contiguous-effective-dates-in-temporal-tables\/"},"modified":"2021-09-29T16:21:29","modified_gmt":"2021-09-29T16:21:29","slug":"self-maintaining-contiguous-effective-dates-in-temporal-tables","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/self-maintaining-contiguous-effective-dates-in-temporal-tables\/","title":{"rendered":"Self-maintaining, Contiguous Effective Dates in Temporal Tables"},"content":{"rendered":"
\n

Self-maintaining, Contiguous Effective Dates in Temporal Tables<\/h1>\n

In a database that is dealing with price information where a new price immediately supersedes the previous one, you can find the correct price for any particular date when all you have is an ‘effective date’ on each row. This is because the ‘effective end date’ is implied by the effective date on the subsequent row, and the current price is always the one with the latest date on it. It is a simple system that requires few constraints to implement.<\/p>\n

Although this allows us to reproduce account information from the data and run historic financial reports, it is often a better fit for the business requirements to have both an ‘effective start’ date and an ‘effective end’ date on each row. This can handle a range of the more generic cases of a ‘transaction time table’<\/a>, and it also is much quicker to determine the current value since it will have a NULL end-date.<\/p>\n

Joe Celko<\/a>‘s article Contiguous Time Periods<\/a> gives a good summary of this type of table, and he described an ingenious way of enforcing the basic rules of this type of table solely via constraints, in what he called “Kuznetsov’s History Table.” This is a special version of the temporal price table which has the extra ‘previous end-date'<\/code> included in the row. This allows us to use a foreign key constraint to prevent a row being inserted that doesn’t have an existing row with an end-date matching the entry in the ‘previous-end-date’ column. While it is an interesting approach, it does have some issues that he and Alex both cover in good detail. <\/p>\n

In his article “Modifying Contiguous Time Periods in a History Table<\/a>” Alex Kuznetsov shows not only how to set this up but also how to maintain such a table, because with such tight constraints it is no mean task. <\/p>\n

In this article I’ll show how it is possible to design and use such a table, but without the complexity of the ‘previous<\/code>-end-date'<\/code> column, yet still using only constraints to enforce data integrity. Then I’ll show you how to use the MERGE statement to update the table with new prices, while automatically maintaining the effective end date column. Finally, I’ll then show how it is possible to do updates to this table using only simple CRUD (Create, Read, Update, Delete) operations, hiding the complexity of the actual MERGE process in a trigger.<\/p>\n

Enforcing Effective End-to-start Dates Using Constraints without the Additional Column<\/h2>\n

In order to demonstrate this approach, and show how it is used, we’ll use a simple example. Here is the Data Definition Language<\/a> (DDL) to create two tables. The first is a simple Products table (which we will populate with some test data) and the second is a ProductPrices<\/code> table with CONSTRAINTs sufficient to enforce this end-to-start date relationship. <\/p>\n

\tCREATE TABLE dbo.Products\n\t(\n\t    ProductID           INT IDENTITY\n\t    ,ProductName        VARCHAR(50) NOT NULL\n\t    ,DiscontinuedDT     DATETIME    NOT NULL DEFAULT ('2099-12-31')\n\t    ,CONSTRAINT p_pk    PRIMARY KEY (ProductID)     \n\t);\n\t\n\tINSERT INTO dbo.Products (ProductName)\n\tVALUES ('Mouse Trap'), ('Better Mouse Trap'), ('Rat Trap');\n\t\n\tSELECT *\n\tFROM dbo.Products;\n\t\n\tCREATE TABLE dbo.ProductPrices\n\t(\n\t    ProductID           INT         NOT NULL\n\t    ,EffectiveStartDT   DATETIME    NOT NULL\n\t    ,EffectiveEndDT     DATETIME    NULL\n\t    -- Time dependent attributes\n\t    ,ProductPrice       MONEY       NOT NULL\n\t    -- In PK, EffectiveStartDT should occur only once for each product\n\t    ,CONSTRAINT pp_pk   PRIMARY KEY (ProductID, EffectiveStartDT)\n\t    --\n\t    -- EffectiveEndDT should always be greater than EffectiveStartDT (unless NULL)\n\t    ,CONSTRAINT pp_ck1  CHECK (EffectiveEndDT > EffectiveStartDT)  \n\t    --\n\t    -- ProductPrice should always be greater than zero\n\t    ,CONSTRAINT pp_ck2  CHECK (ProductPrice > 0) \n\t    -- \n\t    -- FK1: Each EffectiveEndDT should be linked to an EffectiveStartDT\n\t    ,CONSTRAINT pp_fk1  FOREIGN KEY (ProductID, EffectiveEndDT) \n\t                        REFERENCES dbo.ProductPrices(ProductID, EffectiveStartDT)\n\t    --\n\t    -- FK2: Products\/Prices must reflect existing Products\n\t    ,CONSTRAINT pp_fk2  FOREIGN KEY (ProductID) \n\t                        REFERENCES dbo.Products(ProductID)\n\t    --\n\t    -- EffectiveEndDT must be unique for each row of a product\n\t    ,CONSTRAINT pp_u1   UNIQUE (ProductID, EffectiveEndDT) \n\t);\n\t<\/pre>\n
Let’s describe the CONSTRAINTs we’ve defined on ProductPrices<\/code>.<\/p>\n