Take the 2-minute tour ×
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
add comment

1 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.

share|improve this answer
    
en.wikipedia.org/wiki/Temporal_database –  ETL Feb 28 '13 at 2:27
1  
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  
+1 for answering "is there a term?" with "yes, here's the term." –  Michael - sqlbot Feb 28 '13 at 3:29
add comment

Your Answer

 
discard

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.