Sometimes, due to business requirements, we need to manage complex information related to time and its history.
In this context, bitemporal databases can be our solution. These databases are designed to handle two types of time dimensions (hence the name): data validity time and transaction validity time. This dual temporal capability allows for more accurate and comprehensive data tracking, making bitemporal databases a powerful tool for a variety of applications, such as financial systems, healthcare or legal records.
How is it composed?
As we have already mentioned, the bitemporal database is a type of database that supports two temporal dimensions:
- Validity time: the period of time during which a fact is true in the real world.
- Transaction time: the period of time during which the fact was recorded in the database.
By incorporating both valid time and transaction time, bitemporal databases can provide a complete historical record of changes in the data.
This dual time tracking allows for a more nuanced and accurate representation of data over time, allowing users to see the data as it was at any point in the past, like a time machine of the values in the database.
The power of bitemporal modeling
Bitemporal modeling offers several key advantages:
- Historical accuracy: They maintain a complete history of data changes, enabling accurate historical analysis.
- Data integrity: Bitemporal databases ensure data integrity and consistency by tracking validity and transaction times.
- Complete audit trails: All data is rendered immutable, allowing comprehensive change tracking.
- Error correction: Errors can be corrected without losing the history of what was previously recorded.
- Regulatory compliance: Many industries require accurate historical records for regulatory compliance. Bitemporal databases can help meet these requirements.
Example Use Cases
Financial Systems
In financial systems, bitemporal databases can track the history of transactions and account balances. For example, if a bank needs to audit an account, it can use the bitemporal database to see the account balance at any given point in time (and changes in between), both when the transaction occurred and when it was recorded.
Healthcare
In healthcare, patient records need to be accurate and up-to-date. Bitemporal databases can track changes to patient information, such as diagnoses and treatments, ensuring that healthcare providers have access to the most accurate and complete information.
Education
Having a traceability of the students’ academic records allows to know how they have evolved in their studies and to be able to know if there have been adjustments in the evaluation of grades due to revisions or errors. At a legal level, a change in a grade can mean being able to enter the next level of education or not.
Legal Records
Legal records often require precise historical data. Bitemporal databases can track changes to legal documents, such as contracts and court rulings, providing a complete history of when changes were made and when they were valid.
Bitemporal Data Structure
A typical bitemporal table might include the following columns:
- Entity ID
- Attribute Value
- Valid Start Time
- Valid End Time
- Transaction Start Time
- Transaction End Time
Here’s an example of how this might look in a table:
| Entity ID | Attribute Value | Valid Start | Valid End | Transaction Start | Transaction End |
|---|---|---|---|---|---|
| 1 | Value A | 01-01-2024 | 15-03-2024 | 01-01-2024 | 15-03-2024 |
| 1 | Value B | 15-03-2024 | 31-12-9999 | 15-03-2024 | 31-12-9999 |
In this example, the entity’s attribute was initially recorded as “Value A” on January 1, 2024. On March 15, 2024, it was updated to “Value B”. The bitemporal structure allows us to see both the current value and the historical value.
And why the two columns of “Valid” and “Transaction”? To understand this better, let’s look at some examples.
Let’s look at an scenario, for example, where we have to keep track of disease diagnoses.
In this scenario, a patient was initially diagnosed with the flu on April 10th. However, on April 20th, the diagnosis was revised to pneumonia, with the valid time backdated to the original visit. This bitemporal structure allows healthcare providers to see both the initial diagnosis and the corrected diagnosis, which is crucial for understanding the patient’s treatment history.
| Patient ID | Diagnosis | Valid Start | Valid End | Transaction Start | Transaction End |
|---|---|---|---|---|---|
| P001 | Flu | 10-04-2024 | 15-04-2024 | 10-04-2024 | 20-04-2024 |
| P001 | Pneumonia | 10-04-2024 | 25-04-2024 | 20-04-2024 | 31-12-9999 |
Let’s look at a slightly more complex example.
Let’s imagine that we work in a financial institution and we start from a balance of 1000 on June 1, 2024. On the 5th, by mistake, the balance is updated to 1500. On the 7th, we realize the error, and it is that 500 have not been entered at once, but 200 have been entered on June 3rd and 300 on June 5th. This could be saved as:
| Account ID | Balance | Valid Start | Valid End | Transaction Start | Transaction End |
|---|---|---|---|---|---|
| 12345 | 1000 | 01-06-2024 | 05-06-2024 | 01-06-2024 | 07-06-2024 |
| 12345 | 1500 | 05-06-2024 | 31-12-9999 | 05-06-2024 | 07-06-2024 |
| 12345 | 1000 | 01-06-2024 | 03-06-2024 | 07-06-2024 | 31-12-9999 |
| 12345 | 1200 | 03-06-2024 | 05-06-2024 | 07-06-2024 | 31-12-9999 |
| 12345 | 1500 | 05-06-2024 | 31-12-9999 | 07-06-2024 | 31-12-9999 |
With this, if someone took a report on June 4th, this person should have 1200 in account, but only 1000 would appear, since the erroneous update was made on June 5th, but if on June 8th he made the report of how much money was in account on June 4th, now it would be correct, since the amount 1200 is the valid amount for the valid start-end columns and for the validity of the transaction start and end columns. In this way everything is controlled knowing the amounts that would appear on each date and if they are still valid or not.
It is clear that bitemporal databases offer a powerful solution for managing historical data accurately and precisely. By tracking both valid time and transaction time, they provide a complete and accurate historical record, making them invaluable for a variety of applications.