#265 Data modelling 6.6 min
Valid Time, Transaction Time and Bitemporal Tables
One price correction stored three ways; only the bitemporal table answers all 4 questions as asked.
Predict the four questions
Copy the code folder first. Read the QUESTIONS list in run.py and the three events in prices.py. For each of the four questions, predict what the valid only, txn only and both columns will say. Then run python run.py ask and mark where each simple table gives the wrong answer.
A hint
The bitemporal column is the only one that checks both the real-world day and the day it was known.
Add two more questions
Add the questions ("2026-02-15", "2026-03-05") and ("2026-02-15", "2026-03-04") to the end of QUESTIONS in run.py. Predict the bitemporal answers, then run python run.py ask. When I ran it, the bitemporal column said 11 for as known on 2026-03-05 and 12 for 2026-03-04, while the txn only column said 12 for both.
A hint
The correction is entered on 2026-03-05, and a row is believed from that day on.
Make the correction later
In prices.py, change the day of the third event from 2026-03-05 to 2026-03-20. Predict which of the four questions change, then run python run.py ask and python run.py rows. When I ran it, of the original four questions only the first changed in the both column, from 11 to 12, and the last bitemporal rows were recorded from 2026-03-20.
A hint
On 2026-03-10 the correction is not yet known.
These tasks are for you to try on your own machine. Nothing is checked or saved.
What this does not show
One made-up price and three events in SQLite, whole days only, no overlap constraint, and SQL:2011 known only from a summary.
Up next in Data modelling
More in Data modelling
- #110 Slowly Changing Dimensions in SQL: Type 1 vs Type 2
- #130 Define a Metric Once: Views and a Metrics Layer
- #250 Data Modelling Basics: Entities, Keys, Relationships
- #252 Normalisation 1NF to 3NF on a Messy Spreadsheet
- #253 Primary Keys: Natural, Surrogate and Hash Keys
- #254 Constraints That Catch Bad Data: PK, FK, CHECK
