Theophrastus
Time is the most valuable thing a man can spend.
When storing data, their temporal changes are often not sufficiently taken into account. The following example shows the problems of simplified temporal data storage and the advantages of bitemporal data storage.
In the simplest case of a club member database, the membership fee per club member is stored in a table. In this case, the amount of €6 and the membership number in the form of a ULID.
| 6,00 | e66b0b51-59a0-4411-9a70-985471622b87 |
If the treasurer of the club looks at this table, he will see the amount of the membership fee of the member e66b0b51-59a0-4411-9a70-985471622b87. In the event of a change to the membership fee due to a board meeting on Rose Monday, the amount increases to €7. The amount in the table will change accordingly and the next membership fee will be collected in the amount of €7.
| 7,00 | e66b0b51-59a0-4411-9a70-985471622b87 |
Unfortunately, it is no longer possible to deduce from the table how high the membership fee used to be and when a change in the fee took place. For this reason, some form of historisation is usually carried out. In this example, an additional column with the change date is added to the table.
Many years ago, I heard about a university warehouse management system that ignored all employee orders in reports when this employee left the department. The problem only became apparent when a massive ordering employee was actually deactivated. Before that, the discrepancies were blamed on the “usual shrinkage”.
No didactic database model can be modeled too simply not to be in use somewhere!
Instead of changing the current entry, a new entry is inserted with the current date. The table then contains the following two entries.
| 2023-01-01 | 6,00 | e66b0b51-59a0-4411-9a70-985471622b87 |
| 2023-02-22 | 7,00 | e66b0b51-59a0-4411-9a70-985471622b87 |
This allows the treasurer to see that this member has to pay a contribution of €6 until February and a contribution of €7 from March. This temporal handling of the data is sufficient for many use cases. It becomes difficult when subsequent corrections have to be made, but consequences have already arisen from the historical data.
A simple example of such corrections is an incorrect increase in the membership fee. On March 11, the member realizes that, as a member over 50, they should not receive an increase at all. Therefore, after a successful complaint, a new entry is added to the table and the membership fee is back to €6. However, the member has already paid their membership fee and €1 has to be refunded.
| 2023-01-01 | 6,00 | e66b0b51-59a0-4411-9a70-985471622b87 |
| 2023-02-22 | 7,00 | e66b0b51-59a0-4411-9a70-985471622b87 |
| 2023-03-15 | 6,00 | e66b0b51-59a0-4411-9a70-985471622b87 |
However, the information that an incorrect membership fee was collected and the member was refunded €1 is lost here. The reason for this is that only the current date is saved. The system only saves when the change was made (record date) and not from when the change applies (actual date).
With bitemporal data storage, the actual date is stored in addition to the record date. The following table shows the extended entries for the above example.
| 2023-01-01 | 2023-01-01 | 6,00 | e66b0b51-59a0-4411-9a70-985471622b87 |
| 2023-03-01 | 2023-02-22 | 7,00 | e66b0b51-59a0-4411-9a70-985471622b87 |
| 2023-03-01 | 2023-03-15 | 6,00 | e66b0b51-59a0-4411-9a70-985471622b87 |
The table shows that the membership fee in March was €7, but a correction was made on March 11, which already applies to March.
To make things a little more complicated, the club secretariat then reduced the club fee for February and March to €5 as a gesture of goodwill.
| 2023-01-01 | 2023-01-01 | 6,00 | e66b0b51-59a0-4411-9a70-985471622b87 |
| 2023-03-01 | 2023-02-22 | 7,00 | e66b0b51-59a0-4411-9a70-985471622b87 |
| 2023-03-01 | 2023-03-11 | 6,00 | e66b0b51-59a0-4411-9a70-985471622b87 |
| 2023-02-01 | 2023-03-11 | 5,00 | e66b0b51-59a0-4411-9a70-985471622b87 |
| 2023-04-01 | 2023-03-11 | 6,00 | e66b0b51-59a0-4411-9a70-985471622b87 |
The fourth entry corrects the amount of the membership fee from February and the last entry rescinds the correction as of April 1.
The advantage of this data management is that there are different temporal views of the data.
Evaluations that refer to the record date show any corrected membership fees that were valid at that time.
| Evaluation on | January | February | March | April |
| 2023-02-01 | 6 € | 6 € | 6 € | 6 € |
| 2023-03-01 | 6 € | 6 € | 7 € | 7 € |
| 2023-04-01 | 6 € | 5 € | 5 € | 6 € |
Evaluations that refer to the actual date (with a record date before the actual date) show the actual uncorrected membership fees that were valid at that time and collected at that time.
| Evaluation on | January | February | March | April |
| 2023-02-01 | 6 € | 6 € | 6 € | 6 € |
| 2023-03-01 | 6 € | 6 € | 7 € | 7 € |
| 2023-04-01 | 6 € | 6 € | 7 € | 6 € |
The additional effort to establish bitemporal data storage is not difficult, it requires a second timestamp and queries adapted to the respective purpose.