Data Modeling: Implementing Bi-Temporal Access to Data.

The ability to track changes in data over time has long been a goal in the relational database world. We have even written special temporal databases with a special version of SQL to give us that ability. The rest of us have used history tables of one form or another to hold the past. But this separates current data from past data and can make analysis using both somewhat awkward. So it's handy to know that full bi-temporal capabilities can be brought to a standard RDBMS system where we can access current and past data with the same query, from the same sources. "Bi-temporal" means there are two ways to look back at past data. "Transaction time" means we look at the data as it was stored in the database on a particular date and time. "Valid time" means we look back at the data that was in effect in the real world on a particular date and time, even though there could have been some delay in updating the database. It would be helpful but not necessary to also attend the "Data Integrity: the Row Spanning Dependency antipattern" session. We will briefly examine this common mistake used in most time-sensitive implementations, but there is enough useful information that it has its own session for a more complete examination.