Tracking how data changes over time is a crucial requirement in many business systems. This need arises because organizations often want to know not just the current state of a record, but also its previous states—such as how an employee's salary has changed, or what a customer's address was at a certain point in the past. This is known as temporal data management. By maintaining a history of changes, you can answer questions about trends, audits, regulatory compliance, and more. To achieve this, databases use history tables or temporal tables that record every change to important data, along with information about when each version was valid.

-- Schema for employee_salaries history table
CREATE TABLE employee_salaries (
    employee_salary_id SERIAL PRIMARY KEY,
    employee_id INTEGER NOT NULL REFERENCES employees(employee_id),
    salary NUMERIC(12,2) NOT NULL,
    effective_from DATE NOT NULL,
    effective_to DATE
);

Temporal tables like employee_salaries allow you to store not only the current salary for each employee, but also all previous salaries and when they were in effect. The effective_from and effective_to columns define the period during which a particular salary was valid. To find the current salary, you look for the record where effective_to is null; to see historical changes, you query all records for an employee. This approach enables you to answer complex business questions, such as finding out an employee's salary at any point in the past or analyzing how salaries have evolved across the organization.

-- Query to get the entire salary history for employee_id = 2
SELECT salary, effective_from, effective_to
FROM employee_salaries
WHERE employee_id = 2
ORDER BY effective_from DESC;