Canonical Bi-Temporal Schema for Unified Data Management
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current systems for managing temporal data in relational databases are complex and inefficient, particularly in handling bi-temporal data, as they require multiple table schemas and separate datasets for different temporal dimensions, leading to errors and increased maintenance costs.
Innovation Solution
The Asserted Versioning Framework (AVF) uses a single canonical bi-temporal schema for all temporal tables, allowing for the management of both valid time and transaction time in a unified manner, eliminating the need for separate datasets and simplifying data modeling by expressing temporal requirements as metadata.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If multiple table schemas and separate datasets are used for different temporal dimensions, then temporal data can be managed according to different temporal dimensions, but system complexity and maintenance costs increase
Solution Approach 1:
The patent combines multiple temporal dimensions (valid time and transaction time) into a single unified table schema. Instead of maintaining separate datasets for different temporal dimensions, the invention integrates them into one table with columns that represent both temporal dimensions, thereby reducing system complexity while preserving the ability to manage temporal data across multiple dimensions.
Solution Approach 2:
The unified table schema serves multiple functions by simultaneously managing both valid time and transaction time data. The single table structure is designed to handle various temporal querying needs without requiring separate specialized datasets, making the system more versatile while reducing complexity.
2Adaptability or versatility
If multiple table schemas are used for bi-temporal data, then temporal requirements can be met, but data integrity errors increase and maintenance costs rise
Solution Approach 1:
By merging temporal data into a single unified table schema, the patent reduces the opportunities for data integrity errors that arise from maintaining multiple separate schemas. The unified structure ensures consistent temporal relationships are maintained across both valid time and transaction time dimensions, improving reliability while still meeting temporal requirements.
3Adaptability or versatility
If separate datasets are maintained for different temporal dimensions, then temporal data can be tracked independently, but querying efficiency decreases and latency increases
Solution Approach 1:
The patent merges temporal data from different dimensions into a single table, which improves querying efficiency by eliminating the need to join multiple separate datasets. The unified structure allows queries to access both valid time and transaction time information in a single operation, reducing latency while maintaining the ability to track temporal dimensions independently through dedicated columns.
Data Source
AI summary
Computer programs embodied in computer-readable media that can use canonical schemas to persist data from non-temporal tables, effective-time tables, assertion-time tables, and bitemporal tables, and that can enforce temporal integrity constraints on those tables, are provided. In one embodiment, the canonical schemas are used by database tables. In another embodiment, they are used by the physical files which persist data from those tables. Temporal metadata is used to express temporal requirements. Thus, uni-temporal, bitemporal, and temporally-enabled non-temporal tables can be generated without altering existing data models or designing temporal features into new data models. Support is also provided for managing temporal data that exists in future assertion time, and for using episodes to enforce temporal referential integrity.