Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question

2 Answers 2

up vote 7 down vote accepted

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.

share|improve this answer
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.:

EndDate    DATETIME,

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.