Is there a term for a table that has an "effective date" column?

Question

I work with a system which uses a lot of tables whose primary keys include

1. a foreign key to another table and
2. a column indicating the date that record became/becomes "active".

Example tables:

employee                    (primary key is employee_id)
    employee_id    INT
    hire_date      DATE

employee_salary             (primary key is employee_id, start_date)
    employee_id    INT      (foreign keyed to employee.employee_id)
    start_date     DATE
    salary         TINYINT

When viewing an employee in our application UI, we would display all the information from employee and whichever employee_salary record had a start date most recent before the date we are interested in.

In my working group, we would refer to employee as a "base" table and employee_salary as a "repeating" table, since we consider the salary to merely be an aspect of the "base" entity, the employee. We also say that the employee_salary table uses an "effective date".

But these don't seem to be terms in widespread use. Are there standard terms for

1. tables that represent an entity, and
2. tables that represent a changing aspect of one of those entities?

Answer 1

Temporal Table or History Table are probably the terms you are looking for. Often you will see a nullable "end date" column in the table as well.

Answer 2

In a data warehousing context, this is a form of "type-2 slowly-changing dimension." A "slowly-changing dimension" (SCD) is a table with descriptive information (a "dimension," in the jargon) which changes periodically but not constantly. A classic example is customers: they can change names and addresses often enough that you need to handle it, but not so often that you should record the information for every transaction.

"Type-2" refers to a specific way to handle an SCD, namely the way you are: create a new record each time a change occurs, with an effective date (and typically an end date).

It's common to mark the latest record; e.g.:

StartDate  DATETIME NOT NULL,
EndDate    DATETIME,
IsCurrent  AS CASE WHEN EndDate IS NULL THEN 1 ELSE 0 END

You can then use this field in a filtered index to very quickly access current information for any employee, which is probably the main use case.

SELECT Name FROM Employees WHERE IsCurrent = 1 AND EmployeeID = 123

...or even:

CREATE VIEW CurrentEmployees AS SELECT EmployeeID, Name FROM Employees WHERE IsCurrent = 1
SELECT Name FROM CurrentEmployees WHERE EmployeeID = 123

Again, those terms really only apply in a data warehousing context. In a transactional context, I'd use "history table," as Neil already mentioned.

Answer 3

Timespan Fact Table

Kimball: “In isolated cases, it is useful to add a row effective date, row expiration date, and current row indicator to the fact table, much like you do with type 2 slowly changing dimensions, to capture a timespan when the fact row was effective.”*

Discussions can be found in this blog also :

"This approach can be used to address slowly changing balances. Whilst this approach results in a lot less redundant data than a periodic snapshot, it does, however, require more compute resources during the ETL process."