Temporal Relational Database History Management
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current relational database management systems (RDBMS) are inadequate for querying data across time, as they cannot accurately answer questions about past or future states due to changes in data values and schema, and existing solutions like audit logs and snapshots are inefficient or inaccurate.
Innovation Solution
A temporal relational database system that stores and manages temporal histories for every transaction, allowing the database to be reconstituted for any point in time, with valid, transaction, sequence, and event times, and supports queries across time without requiring multiple database copies or application updates.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If RDBMS systems store only current data values, then queries about current state are fast and simple, but queries about past or future states cannot be answered accurately
Solution Approach 1:
The patent segments the temporal dimension by introducing separate temporal tables (e.g., `temporal_history` table) to store historical data states, separating them from the current data storage structure. This allows the system to maintain simple current data storage while adding temporal querying capability through dedicated historical records.
Solution Approach 2:
The patent adds a temporal dimension to the traditional relational database model by introducing time stamps and validity periods alongside conventional data attributes. This transforms the database from a single-point-in-time structure to a multi-dimensional structure that accommodates past, present, and future states.
2Loss of information
If audit logs are used to track changes, then data history can be maintained, but querying requires recreating the database through enormous listing of audit logs
Solution Approach 1:
The patent performs preliminary organization of temporal data by pre-structuring historical information in dedicated temporal tables with proper indexing and temporal ordering. This preliminary structuring eliminates the need to scan through unorganized audit logs during queries, significantly improving retrieval efficiency.
Solution Approach 2:
The patent extracts temporal history information from potentially unstructured audit logs and relocates it to dedicated temporal storage structures. By separating historical data into organized temporal tables, the system can efficiently query specific time periods without processing the entire audit log.
3Reliability
If periodic snapshots of the database are taken, then schema changes can be tracked, but multiple copies of the database must be saved for every point in time
Solution Approach 1:
The patent extracts only the essential schema change information from complete database snapshots and stores it separately in temporal metadata tables. This allows schema versioning without requiring storage of complete database copies, reducing the quantity of data that must be preserved.
Solution Approach 2:
Instead of copying the entire database for each schema version, the patent creates lightweight copies of only the schema definition and metadata in temporal tables. These minimal copies suffice for tracking schema changes while avoiding the storage burden of full database replicas.
4Adaptability or versatility
If multiple copies of applications are maintained for schema versions, then queries across time are possible, but device complexity and maintenance burden increase
Solution Approach 1:
The patent introduces a temporal database layer as an intermediary between applications and the underlying data storage. This intermediary handles temporal query processing and schema versioning, allowing applications to query historical data without maintaining multiple application copies or versions.
Solution Approach 2:
The patent creates a universal temporal query interface that can access any schema version through a single standardized mechanism. This multi-functional temporal layer handles various query types (past, present, future) and schema versions through one unified system, eliminating the need for application-specific versions.
Data Source
AI summary
A temporal relational database includes a relational database framework that allows for all the capabilities of a standard relational database with the addition of the concept of time. Transactions, which can be modifications of attribute values or changes to the database schema, can be stored with temporal histories. Through the use of these temporal histories, the temporal database is able to seamlessly respond to queries for times that are in the past, present, or future. Furthermore, transactions can be entered into the temporal relational database that are not effective until some point in the future, thus allowing for seamless migration of the data and schema of a database. Applications that access data in a temporal database may retrieve a time appropriate schema from the temporal database. An exemplary use of a temporal database to manage roles and responsibilities within an organization is described.