Term for a table that has an “effective date” column

terminology

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?

Best Answer

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.