# 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

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?
-

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.

-
en.wikipedia.org/wiki/Temporal_database –  ETL Feb 28 '13 at 2:27
Hi Neil, you're probably not going to attract many up-votes on this answer unless you expand it a bit. Explanations (if only an outline), examples and links are always popular. –  Paul White Feb 28 '13 at 3:06
+1 for answering "is there a term?" with "yes, here's the term." –  Michael - sqlbot Feb 28 '13 at 3:29

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.

-