Temporal Table Modeling

In this lesson, you will learn how to design database schemas that capture the evolution of data over time using effective and expiration date columns.

The Problem with Static Records

In a standard relational table, updating an attribute (like a customer’s address) usually overwrites the previous value. While this keeps your current data accurate, it destroys the history. If you need to know where a customer lived six months ago for a historical tax report, that information is gone. Temporal Table Modeling solves this by treating records as versions that exist within a specific time window.

Effective and Expiration Timestamps

To model this, we shift away from a "Current State" mindset to a "Versioned State" mindset. Every row in your table needs two additional columns: effective_date (when the record became true) and expiration_date (when the record stopped being true).

Consider a Product_Prices table:

CREATE TABLE Product_Prices (
    product_id INT,
    price DECIMAL(10, 2),
    effective_date DATE,
    expiration_date DATE,
    PRIMARY KEY (product_id, effective_date)
);

Managing the Timeline

When a price changes, you don't UPDATE the row. Instead, you perform two actions:

1. Expire the old record: Update the expiration_date of the current row to the current date.
2. Insert the new record: Add a new row with the new price, setting its effective_date to today and its expiration_date to a distant future value (often 9999-12-31).

By using a composite Primary Key of (product_id, effective_date), you ensure that a product can have multiple historical versions, but only one "active" version at any point in time. When querying for a specific date, you simply filter where your target date falls between the effective_date and expiration_date.

This pattern transforms your database from a simple collection of current facts into a robust audit trail, allowing you to "travel back in time" to reconstruct the state of your business at any historical moment.

Key Takeaway: Temporal tables preserve historical truth by replacing standard UPDATE operations with a versioning pattern using effective_date and expiration_date boundaries.