Note that these two time periods do not have to be the same for a single fact. Imagine that we come up with a temporal database storing data about the 18th century. The valid time of these facts is somewhere between 1700 and 1799, whereas the transaction time starts when we insert the facts into the database, for example, January 21, 1998.
For illustration, we will take data from the following short biography of a fictional man John Doe. John Doe was born on April 3rd, 1975 in the Kids Hospital of Medicine County, as son of Jack Doe and Jane Doe who lived in Smallville. Jack Doe proudly registered the birth of his first-born on April 4th, 1975 at the Smallville City Hall. John grew up as a joyful boy, turned out to be a brilliant student and graduated with honors in 1993. After graduation he went to live on his own in Bigtown. Although he moved out on August 26th, 1994, he forgot to register the change of address officially. It was only at the turn of the seasons that his mother reminded him that he had to register, which he did a few days later on December 27, 1994. Although John had a promising future, his story ends tragically. John Doe was accidentally hit by a truck on April 1st, 2001. The coroner reported his date of death on the very same day.
John's father officially reported birth on April 4th, 1975. This means that a Smallville official, inserted the following entry in the database on this date: Person (John Doe, Smallville) Note that the date itself is not stored in the database.
After graduation John moves out, but forgets to register his new address. John's entry in the database is not changed until December 27, 1994, when he finally enters Bigtown's city hall. A Bigtown official updates his address in the database. The Person table now contains Person (John Doe, Bigtown)
Note that the information of John living in Smallville has been overwritten. There is no way to retrieve that information from the database. Any official accessing the database on December 28, 1994 would be told that John lives in Bigtown.
More technically: if a computer scientist ran the query
SELECT ADDRESS FROM PERSON WHERE NAME='John Doe' on December 26, 1994, the result would be: Smallville. Running the same query 2 days later would result in Bigtown.
Until his tragic death the database would state that he lived in Bigtown. On April 1, 2001 the coroner deletes the John Doe entry from the database. Running the above query would return no result at all.
|Date||What happened in the real world||Database Action||What the database shows|
|April 3rd, 1975||John is born||Nothing||There is no person called John Doe|
|April 4th, 1975||John's father officially reports John's birth||Inserted:Person(John Doe, Smallville)||John Doe lives in Smallville|
|August 26, 1994||After graduation, John moves to Bigtown, but forgets to register his new address||Nothing||John Doe lives in Smallville|
|December 26, 1994||Nothing||Nothing||John Doe lives in Smallville|
|December 27, 1994||John registers his new address||Updated:Person(John Doe, Bigtown)||John Doe lives in Bigtown|
|April 1, 2001||John dies||Deleted:Person(John Doe)||There is no person called John Doe|
Person(John Doe, Smallville, 3-Apr-1975, ∞).
December 27, 1994 John reports his new address in Bigtown where he has been living since August 26th, 1994. The Bigtown official does not change the address of the current entry of John Doe in the database. He adds a new one:
Person (John Doe, Big Town, 26-Aug-1994, ∞).
The original entry Person (John Doe, Smallville, 3-Apr-1975, ∞) is then updated (not removed!). Since it is now known that John stopped living in Smallville on August 26, 1994 the Valid-To entry can be filled in. The database now contains two entries for John Doe
Person(John Doe, Smallville, 3-Apr-1975, 26-Aug-1994).
Person(John Doe, Bigtown, 26-Aug-1994, ∞).
When John dies the database is once more updated. The current entry will be updated stating that John does not live in the Bigtown any longer. No new entry is being added because officials never report heaven as a new address. The database now looks like this
Person(John Doe, Smallville, 3-Apr-1975, 26-Aug-1994).
Person(John Doe, Bigtown, 26-Aug-1994, 1-Apr-2001).
What happens if the person's address as stored in the database is incorrect? Suppose an official accidentally entered the wrong address or date? Or, suppose the person lied about their address for some reason. Upon discovery of the fact, the officials go back and update the database.
For example, from 1-Jun-1995 to 3-Sep-2000 John Doe moved to Beachy. But, to avoid paying Beachy's exorbitant residence tax, he never reported it to the authorities. Later, it is discovered on 2-Feb-2001, during a tax investigation that he was in fact in Beachy during these dates, so they update the database as follows:
Person(John Doe, Bigtown, 26-Aug-1994, 1-Jun-1995).
Person(John Doe, Beachy, 1-Jun-1995, 3-Sep-2000).
Person(John Doe, Bigtown, 3-Sep-2000, 1-Apr-2001).
So the existing two records are updated and a new record is inserted recording his residence in Beachy.
However, this leaves no record that the database ever claimed that he lived in Bigtown during 1-Jun-1995 to 3-Sep-2000. Which might be important for say auditing reasons (or to use as evidence in the official's tax investigation.) This is where transaction time comes in. We record in each record when it was entered and when it was superseded. Thus we get something like this:
Person(John Doe, Smallville, 3-Apr-1975, ∞, 4-Apr-1975, 27-Dec-1994).
Person(John Doe, Smallville, 3-Apr-1975, 26-Aug-1994, 27-Dec-1994, ∞ ).
Person(John Doe, Bigtown, 26-Aug-1994, ∞, 27-Dec-1994, 2-Feb-2001 ).
Person(John Doe, Bigtown, 26-Aug-1994, 1-Jun-1995, 2-Feb-2001, ∞ ).
Person(John Doe, Beachy, 1-Jun-1995, 3-Sep-2000, 2-Feb-2001, ∞ ).
Person(John Doe, Bigtown, 3-Sep-2000, ∞, 2-Feb-2001, 1-Apr-2001 ).
Person(John Doe, Bigtown, 3-Sep-2000, 1-Apr-2001, 1-Apr-2001, ∞ ).
So we record not only changes in what happened at different times, but also changes in what was officially recorded at different times.
A particularly challenging issue is the support of temporal queries in a transaction time database under evolving schema. In order to achieve perfect archival quality it is of key importance to store the data under the schema version under which they firstly appeared. However even the most simple temporal query rewriting the history of an attribute value would required to be manually rewritten under each schema versions, potentially hundreds as in the case of MediaWiki This process would be particularly taxing for users. A common solution is to provide automatic query rewriting.