Temporal Entities

(under construction)

EDennis.AspNetCore.Base introduces support for "temporal entities." In this context, temporal entities refer to entities for whom historical records are created and retrieved through entity framework operations. Temporal entities do not meet the SQL2011 standard for temporal databases and they do not provide performance benefits over built-in temporal features of enterprise RDBMS's; however, they do offer some advantages over the pure RDBMS handling of historical records.

Background

Oracle, IBM, and SQL Server have had support for temporal databases for many years. Both IBM and Oracle provide support for system period tables, application period tables, and bitemporal tables, while SQL Server provides support for system period tables only.

System Period Tables

System period tables have records whose valid time periods reflect when UPDATE and DELETE RDMBS operations take place. Prior to temporal database support, database developers often used UPDATE and DELETE (and sometime INSERT) triggers to produce system period records in history tables. Triggers were effective, but they required some additional development work. Also, in order to create "as-of" queries (queries that return records as of a particular point in time), developers had to write more complex queries, involving unions over current and historical tables. With built-in support for system period tables, the major RDBMS's make it relatively easy to setup automatic auditing of UPDATE and DELETE operations. In addition, "as-of" (and similar) queries can be written just by adding a few keywords to SQL.

Application Period Tables

Application period tables have records whose valid time periods are controlled by the applications and possibly the users themselves. The business case for application period tables is fairly straightforward. SQL UPDATE and DELETE operations typically do not occur at precisely the point in time when a fact (e.g., a person's address or a record's status) has changed. Often, there is a lag in time between the fact change and the update or delete of the record. Indeed, sometimes there database operations even precede an associated fact change (e.g., when a new rate takes effect). For those kinds of situations, system periods may not be sufficient. Oracle and IBM provide support for application periods in order to allow applications/users the ability to control the effective date ranges for records, when needed. These application periods can be queried with as of (and similar) expressions in the same way that system periods can be queried. Importantly SQL Server doesn't provide the same kind of support for application periods, the developer can create and manage application periods as regular columns.

Bitemporal Tables

Oracle and IBM have support for bitemporal tables -- tables whose records have both system periods and application periods. Indeed, Oracle has support for multi-temporal tables, where any number of periods (addressing a variety of business issues) can be added to records.

Temporal Entities

Temporal entities were designed to provide an application-controlled alternative to system period tables with limited support for as of queries. More specifically, temporal entities address the following business needs:

• The ability to control when historical records are created to prevent bloat in history records when individual records are progressively built/saved.
• The ability to perform as of queries in a natural way using Linq and Entity Framework operations.

(more to come...)