Is there a term for a table that has an "effective date" column?
3
votes
3
answers
895
views
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?
Asked by matts
(141 rep)
Feb 28, 2013, 01:07 AM
Last activity: Jan 5, 2024, 02:59 PM
Last activity: Jan 5, 2024, 02:59 PM