Valid Time, Transaction Time and Bitemporal Tables
One price correction stored three ways; only the bitemporal table answers all 4 questions as asked.
Video #265 ยท Data modelling
Steps in the video
- 0:00 One price correction, three tables. Only the bitemporal one gets all 4 questions right.
- 0:35 The idea
- 1:14 Setup
- 1:37 Three tables and three events
- 2:10 One axis at a time
- 2:38 Both axes
- 3:10 Asking a question
- 3:39 Run the rows
- 4:51 What this does not show
- 5:16 Mistakes to avoid
- 5:46 My take and next steps
- 6:08 Recap
Files
- prices.py (62 lines)
- run.py (48 lines)
Tested with
Python 3.12.15
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.
Sources
Learning IT by Example.