Tuple-versioning (also called point-in-time) is a mechanism used in a
relational database management system
A relational database (RDB) is a database based on the relational model of data, as proposed by E. F. Codd in 1970.
A Relational Database Management System (RDBMS) is a type of database management system that stores data in a structured for ...
to store past states of a
relation
Relation or relations may refer to:
General uses
* International relations, the study of interconnection of politics, economics, and law on a global level
* Interpersonal relationship, association or acquaintance between two or more people
* ...
. Normally, only the current state is captured.
Using tuple-versioning techniques, typically two values for time are stored along with each
tuple
In mathematics, a tuple is a finite sequence or ''ordered list'' of numbers or, more generally, mathematical objects, which are called the ''elements'' of the tuple. An -tuple is a tuple of elements, where is a non-negative integer. There is o ...
: a start time and an end time. These two values indicate the validity of the rest of the values in the tuple.
Typically when tuple-versioning techniques are used, the current tuple has a valid start time, but a
null
Null may refer to:
Science, technology, and mathematics Astronomy
*Nuller, an optical tool using interferometry to block certain sources of light Computing
*Null (SQL) (or NULL), a special marker and keyword in SQL indicating that a data value do ...
value for end time. Therefore, it is easy and efficient to obtain the current values for all tuples by
querying for the null end time.
A single query that searches for tuples with start time less than, and end time greater than, a given time (where null end time is treated as a value greater than the given time) will give as a result the valid tuples at the given time.
For example, if a person's job changes from Engineer to Manager, there would be two tuples in an Employee
table
Table may refer to:
* Table (database), how the table data arrangement is used within the databases
* Table (furniture), a piece of furniture with a flat surface and one or more legs
* Table (information), a data arrangement with rows and column ...
, one with the value Engineer for job and the other with the value Manager for job. The end time for the Engineer tuple would be equal to the start time for the Manager tuple.
The pattern known as
log trigger uses this technique to automatically store historical information of a
table
Table may refer to:
* Table (database), how the table data arrangement is used within the databases
* Table (furniture), a piece of furniture with a flat surface and one or more legs
* Table (information), a data arrangement with rows and column ...
in a
database
In computing, a database is an organized collection of data or a type of data store based on the use of a database management system (DBMS), the software that interacts with end users, applications, and the database itself to capture and a ...
.
See also
*
Temporal database
A temporal database stores data relating to time instances. It offers temporal data types and stores information relating to past, present and future time.
Temporal databases can be uni-temporal, bi-temporal or tri-temporal.
More specifically the ...
*
Bitemporal data
*
Log trigger
References
*
Data modeling
{{comp-stub