History-Enabled Database Tables for Temporal Querying
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems face limitations in maintaining a history of changes in tables without relying on redo logs, which can be storage-constrained and require extensive reading, making it difficult to reconstruct past states efficiently.
Innovation Solution
Implementing history-enabled tables with a start time column and a period column, along with a view that unions current and history rows, allows for temporal queries without depending on redo logs, enabling efficient tracking of past versions and periods of table changes.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If redo logs are used to maintain table history, then past states can be reconstructed, but storage space is consumed and reconstruction requires extensive reading
Solution Approach 1:
The patent segments table history into two distinct storage locations: the original table structure and a separate history table. When a row is updated or deleted, the original row is copied to the history table with timestamp information, while the original table reflects only current state. This segmentation allows the system to maintain complete history without bloating the original table structure.
Solution Approach 2:
The patent implements copying by creating historical copies of rows when changes occur. Instead of maintaining continuous logs of all changes (redo logs), the system copies the before-state of modified rows into a history table with metadata including start_time and period columns. This copying approach preserves historical information while consuming minimal storage compared to full redo log maintenance.
2Reliability
If redo logs are used to track changes, then error correction is possible, but the system requires extensive reading to reconstruct past states
Solution Approach 1:
The patent applies preliminary action by pre-computing and storing historical row states in the history table with their respective time periods. When a query requests past state information, the system can immediately retrieve pre-prepared historical data from the history table rather than reconstructing it by reading through extensive redo logs. This preliminary preparation of historical data dramatically reduces query response time.
Solution Approach 2:
By copying historical row states into a dedicated history table with metadata (start_time, period, end_time columns), the system enables direct querying of past states without extensive reading operations. The copy includes all necessary information to reconstruct any past table state, making error correction and temporal queries efficient.
3Loss of information
If traditional history tracking methods are used, then change history is maintained, but the interface requires advanced SQL expertise to use
Solution Approach 1:
The patent implements a universal interface through the enable_history() function that works with any database table regardless of its structure or content. This single function handles the complexity of history table creation, trigger setup, and data synchronization automatically. Users can track history for any table using the same simple function call, making the system universally applicable and easy to use without requiring advanced SQL expertise.
Solution Approach 2:
The system provides self-service through automatic trigger-based maintenance. When enable_history() is called on a table, it automatically creates the history table structure, sets up insertion and update triggers, and manages the copying of historical data. The triggers automatically fire on row changes to maintain the history table, eliminating the need for users to manually manage history tracking complexity.
4Loss of information
If manual history table management is implemented, then past versions can be tracked, but the device complexity increases
Solution Approach 1:
The system reduces complexity through self-service automation. The enable_history() function automatically performs all necessary setup tasks: creating the history table with proper schema, establishing insertion triggers to capture new rows, setting up update triggers to copy modified rows with timestamps, and configuring the period column calculations. This automated self-service approach eliminates the need for manual history table management while maintaining complete version tracking.
Solution Approach 2:
The patent introduces an intermediary layer in the form of automatic triggers that mediate between the original table and the history table. These triggers intercept row insertion and update operations, automatically copy necessary data to the history table with appropriate timestamps, and then allow the original operation to proceed. This intermediary mechanism handles all the complexity of history management transparently, shielding users from complex manual management tasks.
Data Source
AI summary
Techniques for history enabling a table in a database system so that past versions of rows of the history-enabled table are available for temporal querying. The table is history enabled by adding a start time column to the table and creating a history table for the history-enabled table. The start time field's value in a row of the history-enabled table indicates when the contents of the row last changed. The rows of the history table are copies of rows of the history-enabled table that have been deleted or updated. The rows include end time fields whose values indicate when the row was updated or deleted. A history table's row thus indicates a period in which the history table's row was in the history-enabled table. Temporal queries are performed on a view which is the union of the history-enabled table and the history table.