Database Bridge Schema for Online Data Migration
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current maintenance procedures in software systems, such as database upgrades, often require downtime and involve sequential modifications that lead to longer runtime and increased memory consumption, as they cannot perform incompatible structural changes like key modifications or data transformations online without data loss or redundancy.
Innovation Solution
The implementation of a data migration method that uses a bridge schema with shadow fields and database triggers to perform structural changes in a single step, allowing for online transformations and reducing downtime by processing table structure changes in parallel, hidden from application use, and executing migration logic within the same database transaction.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If traditional sequential maintenance procedures are used, then data integrity is maintained, but downtime increases and runtime is extended
Solution Approach 1:
The patent segments the schema into multiple versions (start schema, target schema, and bridge schema) that can coexist. The bridge schema acts as an intermediary layer that allows the application to continue operating while the underlying data structure is being transformed, thereby enabling online maintenance without downtime while maintaining data integrity through controlled access paths.
Solution Approach 2:
The bridge schema serves as an intermediary layer between the application and the underlying start schema. It contains shadow fields that mirror the structure of the target schema and uses database triggers to automatically transform data during reads and writes. This mediator enables the application to interact with the new schema structure online without requiring downtime, while the triggers ensure data integrity by performing transformations within the same transaction.
2Adaptability or versatility
If sequential table modifications are performed, then structural changes are applied, but runtime increases and memory consumption increases
Solution Approach 1:
The patent creates shadow fields in the bridge schema beforehand that mirror the target schema structure. These shadow fields are prepared in advance with the new data types and structures, so when the actual data transformation is needed, it can be performed online through triggers rather than requiring sequential offline processing steps, thereby reducing runtime.
Solution Approach 2:
The bridge schema with triggers enables continuous data transformation during application operation. As the application reads and writes data, the triggers automatically perform the transformation from start schema to target schema structure in real-time, eliminating the need to stop the application for batch processing, thus maintaining productivity while applying structural changes.
3Loss of time
If online transformations are attempted without bridge schema, then downtime is reduced, but data loss or redundancy occurs
Solution Approach 1:
The bridge schema with shadow fields acts as a safe intermediary that prevents direct manipulation of the start schema. All data access goes through the bridge schema, which uses triggers to ensure that transformations are performed atomically within transactions. This intermediary layer guarantees that data integrity is maintained during online transformations by preventing partial or inconsistent state changes.
Solution Approach 2:
The database triggers in the bridge schema provide automatic feedback mechanisms that monitor and control data transformations. When data is read or written, the triggers automatically perform the necessary transformations and validate the results, ensuring that data integrity is maintained. This feedback loop prevents data loss or redundancy by ensuring that all transformations are completed successfully before committing changes.
4Adaptability or versatility
If multiple sequential modifications are performed, then comprehensive structural changes are achieved, but memory consumption increases
Solution Approach 1:
The patent merges multiple structural modification operations into a single online transformation process. Instead of performing separate sequential modifications that would each require loading and processing data independently, the bridge schema with triggers combines all transformations into a unified mechanism that operates continuously during application execution, thereby reducing overall memory consumption.
Solution Approach 2:
The bridge schema with triggers enables self-service data transformation where the system automatically performs the conversion from start schema to target schema structure as data is accessed. This eliminates the need for separate batch processing operations that would require additional memory resources, as the transformation happens incrementally during normal application operation using existing data buffers.
Data Source
AI summary
Implementations include actions of initiating a procedure on an application that interacts with a database system having a start schema, through which a first version of the application interacts with tables, providing a bridge schema including table views to each table of the start schema, switching a first version of the application to interact through the bridge schema, creating a shadow field in a table, the shadow field corresponding to a field of the table that is to undergo a change during an upgrade from the first version to a second version, providing a trigger in the start schema, the trigger executing a transformation between the field and the shadow field during the upgrade, modifying the table in the start schema to a target structure to change a parameter of the shadow field or the field of the table, and switching the second version to interact through the start schema.