Temporal Table Column Versioning to Reduce Data Redundancy
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Temporal database systems face inefficiencies in storage due to unnecessary versioning of row changes, as they lack the ability to distinguish between significant and insignificant column value changes, leading to wasteful storage and performance issues.
Innovation Solution
Implementing a Data Definition Language (DDL) statement with 'VERSION LAST ONLY' and 'VERSION FIRST ONLY' constructs to manage row versioning, allowing users to specify which columns require versioning, thereby reducing data redundancy by updating existing rows instead of creating new versions for insignificant changes.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If row versioning is implemented for all columns in temporal tables, then complete historical tracking is achieved, but storage space is wasted and database performance deteriorates due to versioning insignificant changes
Solution Approach 1:
The patent segments the versioning requirement by column, allowing different versioning policies for different columns within the same table. The DDL statement syntax enables users to specify versioning control at the column level (e.g., VERSION ALWAYS, VERSION NEVER, VERSION CHANGE), thereby segmenting the monolithic versioning approach into granular, column-specific controls that reduce unnecessary storage of insignificant changes while maintaining historical tracking for important columns.
2Reliability
If row versioning is implemented for all columns in temporal tables, then complete historical tracking is achieved, but database performance deteriorates due to increased table size and processing overhead
Solution Approach 1:
By segmenting versioning controls to the column level, the patent reduces the overall number of versioned columns, thereby decreasing table size and the computational overhead associated with version management. This segmentation allows the database to maintain historical tracking for critical columns while excluding less important columns from versioning, thus preserving database performance.
3Loss of information
If highly volatile fields with low business value are versioned, then complete data history is maintained, but storage costs increase significantly
Solution Approach 1:
The patent applies local quality by assigning different versioning characteristics to different columns based on their business value and volatility. The DDL statement allows specifying VERSION NEVER for highly volatile fields with low business value, while other columns may have VERSION ALWAYS or VERSION CHANGE. This localized approach ensures that storage resources are allocated efficiently, maintaining data history completeness for important columns while eliminating unnecessary storage costs for insignificant volatile fields.
4Quantity of substance
If all columns are excluded from versioning to reduce storage, then storage efficiency improves, but the ability to track significant changes is lost
Solution Approach 1:
Rather than applying a uniform versioning policy to all columns, the patent uses local quality by allowing selective versioning at the column level. Users can specify which columns should be versioned (VERSION ALWAYS, VERSION CHANGE) and which should not (VERSION NEVER) based on their specific business requirements. This enables the database to achieve storage efficiency by excluding unnecessary columns from versioning while maintaining reliable change tracking for significant columns.
Data Source
AI summary
Control versioning of records in a temporal table is provided to reduce data redundancy. New Data Definition Language (DDL) syntax is provided to make individual columns within a table sensitive or insensitive to whether new row versions are generated when Database Manipulation Language (DML) statements operate on the table. The database parser and back-end data processors are configured to create the table with the user-defined versioning attributes and to manage versioning of the rows without requiring additional programming.