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

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

• a foreign key to another table and

• 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

• tables that represent an entity, and

• tables that represent a changing aspect of one of those entities?

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.

StackExchange Database Administrators Q#35635, answer score: 2