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