Live Database Schema Tree Change via Intermediate Table
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database management systems face challenges in modifying or upgrading schema trees without downtime, especially when introducing a new node in a hierarchical model, as altering or deleting columns and tables can cause application servers to fail, and existing methods struggle with maintaining backward compatibility during live database schema changes.
Innovation Solution
A method for live database schema tree change involves creating an intermediate database table with a column referencing the parent table, updating the child table to reference the intermediate table, and overwriting parent table references with data from the intermediate table, allowing seamless query execution and backward compatibility during schema changes.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If database schema is modified by altering or deleting columns and tables, then schema structure is updated, but application server fails to address the database
Solution Approach 1:
The patent applies preliminary action by creating an intermediate table before modifying the original parent table. This intermediate table serves as a buffer that allows schema changes to be prepared in advance without disrupting the existing parent-child relationship. The child table is first updated to reference the intermediate table, ensuring that application servers continue to function normally during the transition.
Solution Approach 2:
The patent introduces an intermediate table as a mediator between the original parent table and the child table. This intermediate structure acts as a temporary bridge that maintains the parent-child relationship during schema migration. The child table references the intermediate table instead of the original parent table, allowing seamless transitions while maintaining application server availability.
2Adaptability or versatility
If database schema changes are performed, then new structure is implemented, but downtime occurs
Solution Approach 1:
The patent ensures continuity of useful action by maintaining the parent-child relationship throughout the schema change process. The intermediate table preserves the referential integrity, allowing the database to remain operational and accessible during the entire migration process. Application servers can continue to query and modify data without interruption.
Solution Approach 2:
The schema changes are performed in a predetermined sequence that minimizes downtime. The intermediate table is created and configured before any data migration occurs, and the final switch to the new structure is made atomically. This preliminary preparation ensures that the actual schema change moment causes no disruption to database operations.
3Adaptability or versatility
If intermediate table is created and data is migrated, then schema tree is updated, but data integrity must be maintained
Solution Approach 1:
The intermediate table serves as a data integrity safeguard during migration. All data references from child tables are redirected to the intermediate table, which maintains the original parent table's data structure and content. This intermediary layer ensures that no data is lost or corrupted during the transition, as the intermediate table faithfully replicates the original parent table's interface.
Solution Approach 2:
Data integrity is preserved through preliminary validation and copying. The intermediate table is populated with data from the original parent table before any child table references are updated. This preliminary data preparation ensures that all referential integrity constraints are satisfied before the migration is committed, preventing orphaned records or broken relationships.
Data Source
AI summary
A method for live database schema tree change, comprising: (a) providing a computerized database comprising: a parent database table and a child database table having a column configured to reference the parent database table; and (b) while the computerized database is live, operating a database management system (DBMS) in control of the computerized database, to: create an intermediate database table having a column configured to reference the parent database table; in the child database table: create a column configured to reference the intermediate database table, insert an identifier of the intermediate database table into all fields of the column configured to reference the intermediate database table, and overwrite all fields of the column configured to reference the parent database table with data from the fields of the column configured to reference the intermediate database table.