Tuple-versioning
Encyclopedia
Tuple-versioning is a mechanism used in a relational database management system
to store past states of a relation
. Normally, only the current state is captured.
Using tuple-versioning techniques, typically two values for time are stored along with each tuple
: 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
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
, 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
in a database
.
Relational database management system
A relational database management system is a database management system that is based on the relational model as introduced by E. F. Codd. Most popular databases currently in use are based on the relational database model....
to store past states of a relation
Relation (database)
In relational model:A relation value, which is assigned to a certain relation variable, is time-varying. By using a Data Definition Language , it is able to define relation variables.The following is an example of a heading which consists of three attributes....
. Normally, only the current state is captured.
Using tuple-versioning techniques, typically two values for time are stored along with each tuple
Tuple
In mathematics and computer science, a tuple is an ordered list of elements. In set theory, an n-tuple is a sequence of n elements, where n is a positive integer. There is also one 0-tuple, an empty sequence. An n-tuple is defined inductively using the construction of an ordered pair...
: 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
-In computing:* Null , a special marker and keyword in SQL* Null character, the zero-valued ASCII character, also designated by NUL, often used as a terminator, separator or filler* Null device, a special computer file that discards all data written to it...
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 (information)
A table is a means of arranging data in rows and columns.Production % of goalNorth 4087102%South 4093110% The use of tables is pervasive throughout all communication, research and data analysis. Tables appear in print media, handwritten notes, computer software, architectural...
, 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
Log trigger
In relational databases, the Log trigger or History trigger is a mechanism for automatic recording of information about changes -inserting, updating and deleting rows- in a database table....
uses this technique to automatically store historical information of a table
Table (database)
In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns and horizontal rows. A table has a specified number of columns, but can have any number of rows...
in a database
Relational database
A relational database is a database that conforms to relational model theory. The software used in a relational database is called a relational database management system . Colloquial use of the term "relational database" may refer to the RDBMS software, or the relational database itself...
.