Database Archive Manager Historical Schema Query Engine
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In cloud-based multi-tenant database systems, archiving records while managing schema drift and maintaining access to historical data fields is challenging due to changes in field definitions over time, which affects data integrity and query efficiency.
Innovation Solution
The system employs an archive manager and query engine that maintain a historical schema of object fields, allowing records to be archived into a second storage device with a unified view, enabling access to current and non-current fields through a unified query processing mechanism, even after field modifications or deletions.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of energy
If records are archived into a second storage device to separate current and historical data, then storage cost efficiency is improved, but data integrity and access to historical field values deteriorate due to schema drift
Solution Approach 1:
The system performs preliminary actions by creating a historical schema snapshot before archiving records. This snapshot captures the exact field definitions and data types at the time of archiving, ensuring that historical field values can be accessed later even if the current schema has drifted. The archive manager proactively preserves schema information alongside the archived data, preventing data integrity issues.
Solution Approach 2:
The historical schema acts as an intermediary between the archived records and the current system. When querying archived data, the system uses the historical schema to interpret and access field values correctly, bridging the gap between the archived data format and the current schema expectations. This mediator ensures accurate data retrieval despite schema changes over time.
2Productivity
If a unified view is implemented to access both current and archived records, then query efficiency is improved, but system complexity increases due to schema management
Solution Approach 1:
The system segments schema management into distinct components: the current schema for active data and the historical schema for archived data. The archive manager handles historical schema creation and maintenance separately from the query engine that uses both schemas. This segmentation allows each component to be optimized independently, reducing overall system complexity while enabling unified querying.
Solution Approach 2:
The system creates a copy of the schema at the time of archiving, storing it as historical schema information alongside the archived records. This copying approach allows the system to preserve the original field definitions without modifying the current schema structure. The query engine can then reference these copied schema definitions to access archived data uniformly, simplifying the unified view implementation.
3Adaptability or versatility
If field definitions are modified over time to adapt to changing requirements, then system adaptability is improved, but data consistency and historical data access worsen
Solution Approach 1:
Before allowing schema modifications, the system performs a preliminary action of capturing the current schema state and storing it as a historical snapshot. This ensures that even as the schema evolves to meet changing requirements, the original field definitions are preserved. The archive manager maintains this chronological record of schema versions, preventing loss of historical field value context.
Solution Approach 2:
The system implements dynamic schema management where the schema can evolve over time through controlled modifications. Each modification is tracked and versioned, allowing the schema to adapt to new requirements while maintaining a history of previous states. The query engine dynamically selects the appropriate schema version based on whether the data is current or archived, ensuring data consistency throughout the evolution process.
Data Source
AI summary
A method is provided for processing a query. In some embodiments, the method includes creating a historical schema defining an ordered set of fields based on a current schema of an object and a field modification of the current schema. The method includes inserting, based on the field modification, a new field at an end of the historical schema and archiving records into a second storage device. The method includes receiving a query, which includes a command specifying a subset of fields and which indicates a non-current field. The method includes searching in the second storage device for archive records having a second schema including the subset of fields and the non-current field. The first and second schemas follow the field order in accordance with the historical schema. The method further includes accessing the subset of fields and the non-current field in accordance with the command.