Referential Integrity Update Preprocessing Without Full Rollback
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing data update processes in enterprises face issues with referential integrity violations, leading to unreliable databases and potential production job failures or incorrect results, as referential integrity issues are often undetectable until the update is processed, necessitating costly and time-consuming rollbacks.
Innovation Solution
A system and method that utilizes a preprocessor to analyze update data against database schemas, categorizing it into three categories: guaranteed valid, guaranteed invalid, and uncertain, allowing only the valid data to be updated while flagging issues for further analysis, thus preventing rollbacks and ensuring database integrity.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If the entire update is rolled back when a referential integrity issue is detected, then database integrity is maintained, but processing time and productivity are significantly reduced
Solution Approach 1:
The patent segments the update data into multiple portions based on referential integrity validation results. The preprocessor divides the update data into a first portion (valid records) and a second portion (invalid records), allowing the system to process only the valid portion while skipping the invalid portion, thereby maintaining database integrity without rolling back the entire update operation.
Solution Approach 2:
The patent applies preliminary action by using a preprocessor to validate referential integrity before the actual update operation. The preprocessor performs ahead-of-time validation to identify valid and invalid data portions, enabling the system to prepare appropriate processing instructions in advance and avoid runtime failures that would require rollbacks.
2Productivity
If referential integrity validation is performed before update, then processing time is reduced by avoiding rollbacks, but system complexity increases
Solution Approach 1:
The patent introduces a preprocessor as an intermediary component between the update data source and the database system. This preprocessor performs referential integrity validation and categorizes data into valid and invalid portions, acting as a mediator that prepares the data before it reaches the main update processing system, thereby reducing complexity in the core update logic.
Solution Approach 2:
The patent extracts the referential integrity validation function into a separate preprocessor module. By taking out this validation logic from the main update processing system, the patent reduces the complexity of the core system while still achieving comprehensive validation through the dedicated preprocessor component.
3Productivity
If updates are allowed to process even with potential referential integrity issues, then productivity is maintained, but database reliability deteriorates
Solution Approach 1:
The patent segments update data into valid and invalid portions based on referential integrity checks. By identifying and separating invalid records (those with referential integrity issues) from valid records, the system can process the valid portion to maintain productivity while excluding the invalid portion to preserve database reliability.
Solution Approach 2:
The patent applies local quality by treating different portions of the update data differently based on their validation results. Valid portions are processed with full update operations, while invalid portions are handled with skip or error handling logic, allowing the system to maintain overall productivity while ensuring local data quality and consistency.
Data Source
AI summary
An import job associated with a data store update is inspected and schemas associated with target data tables that are to be updated with update data are analyzed. Referential integrity issues associated with foreign keys in the target tables are identified in the update data. The update data is broken into three portions, a first portion that is guaranteed to not have a referential integrity issue, a second portion this is known to have a referential integrity issue, and a third portion that cannot be determined at this stage of processing. The import job is modified to update the corresponding target tables with the first portion of the update data while the second and third portions are not updated to the target data base tables but a custom message is recorded in fields associated with the second and third portions that describes the issue for subsequent review/resolution.