In this talk we'll quickly look through, first unitemporal and then bitemporal database versioning and auditing mechanism that allows active querying of the audit logs.
01/02 2 2000 01/02 01/03 3 3000 01/03 ∞ Unitemporal Our latest “open” record The valid timeline can be thought as the “business” timeline as in what happened in the real world
1 1000 01/01 01/02 2 2000 01/02 01/03 3 3000 01/03 ∞ SELECT * FROM money WHERE ‘01/02’ BETWEEN valid_from AND valid_to; To query this row we filter on the valid timeline
transact_from transact_to 1 1000 01/01 01/02 01/01 ∞ 2 2000 01/02 01/03 01/02 ∞ 3 3000 01/03 ∞ 01/03 ∞ VALID TIMELINE == TRANSACT TIMELINE in “normal” cases Let’s add a second timeline, the transact or “system” timeline. This is what the IT systems see of the world. On inserts they always use NOW() for transact_from on closing out records they always use NOW() for transact_to.
1000 01/01 01/02 01/01 01/04 2 2000 01/02 01/03 01/02 01/04 3 3000 01/03 ∞ 01/03 01/04 4 500 01/01 01/02 01/04 ∞ 5 600 01/02 01/03 01/04 ∞ 6 700 01/03 ∞ 01/04 ∞ Insert new records with the amended data. These will be considered the open records “as of” 01/04
understand the concept. Lots of databases support temporal columns/auditing out of the box: MSSQL, Oracle, DB2, Teradata, Postgres SQL:2011 standard has defined features for temporal support There are other application level solutions as well: Hibernate Envers, event sourcing, CQRS, etc. Home-rolling bitemporality can become complicated and should be considered carefully.