3 Types of Temporal Tables in Teradata
What is a Temporal Database Definition - Temporal – the ability to store all states of a given set of data (a database row), as part of the query select a point in time to reference the data.
- What was this account balance (share price, inventory level, asset value, etc) on this data?
- What data went into the calculation on 11/3/07, and what adjustments were made in 2008?
- On this historic data, what was the service level (contract status, customer value, insurance policy coverage) for said customer?
Three Types of Temporal Tables
1. Valid Time tables
When a fact is true in the modeled reality. User specified times.
2. Transaction time Tables
When a fact is stored in the database. System maintained time, no user control.
3. Bitemporal Tables
Both Transaction Time and Valid Time
User defined Time
User can add time period columns, and take advantage of the added temporal operators Database does not enforce any rules on user defined time columns.
Provides list of members who were reported as covered on JAN 15,2000 in the Feb 1 2000 NCQA report, with names as accurate as our best data shows today.
Temporal Update - Bi Temporal Table
Current Valid time, Current transaction time Query jeans(125,102) are sold today (2005-08-30)