Getting Bitemporal With Your OLTP Database

The concept of a bitemporal database can seem a bit exotic and complex to be considered for a typical RDBMS schema model. While the transaction processing and query structures required to make this happen, with standard RDBMS, are more involved than a normal database model, it is a fairly straight forward design methodology to annotate every table in your RDBMS model with bitemporal semantics.

See the table below for an example for what the table structure might look like. The TT start/end columns are the transaction time dimension and the VT start/end columns are the validity time dimension. These four columns drive the basic schema model structure for a bitemporal database and enable powerful queries that can pivot and scan for data across two time dimensions without the need of a data warehouse or other complex analytics.

The table above looks straight forward, right? And it is. The bit of complexity comes with how to handle the actual data mutations (a change to a row) and insure that every row that is superseded by a a new TT and VT tuple in proper time semantics and that this is handled in a transactional consistent fashion to insure a continuous flow of tuple epochs (an epoch is a row at a particular TT/VT point in time) where the new epoch that supersedes the prior epochs properly terminates the TT and VT epochs with the start of the new TT and VT epochs.

The advantages of a bitemporal schema model are many. They include:

1. Immutable data structures which means all tuples preserve all changes across time.
2. Built-in audit trail functionality, since no changes are every overwritten.
3. The ability to write fairly simple queries to view data any point in time.
4. Easily compare any two points of time for changes.
5. The ability to find all changes across a time range.

Injecting bitemporal capabilities into your schema will allow tracking every change that happens within a table across two time dimension: transaction time (when the mutation happened) and validity time (the time range the mutation and current state of the row is valid).

Some databases such as Oracle, DB2 and PostgreSQL have specialized extensions to support bitemporal capabilities, but you don't really need these extensions - they only help with the DDL aspect of the design and not with the DML or query aspect. For the most part, these extensions are just syntacitc sugar that you can implement on your own in a more cross database fashion and even extend to support NoSQL databases as well.

Get started with turning your schema model into a bitemporal powered RDBMS. Contact Grand Logic to learn how we can help you build your next bitemporal database environment.