Database Schema Validation via Denormalized Views
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional database schema upgrade validation tools produce a high number of false positives due to differences in surrogate keys between upgraded and reference database schemas, requiring manual processing and reducing validation efficiency.
Innovation Solution
The method involves denormalizing the database by creating temporary views without surrogate keys, allowing for accurate comparison of entities and relations, thereby reducing false positives and automating the validation process.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If conventional validation tools compare database schemas directly, then validation coverage is complete, but false positives increase due to surrogate key differences
Solution Approach 1:
The patent extracts and removes surrogate keys from the database schema comparison process by creating denormalized views that exclude these synthetic identifiers. This allows the validation tool to compare only the meaningful data attributes, eliminating false positives caused by surrogate key differences while maintaining complete validation coverage of actual business data.
Solution Approach 2:
The patent introduces denormalized views as intermediary structures between the normalized database schema and the validation process. These views act as a mediator that transforms the schema representation to remove surrogate keys while preserving semantic relationships, enabling accurate comparison without direct exposure to the harmful surrogate key differences.
2Reliability
If manual processing of false positives is performed, then validation completeness is maintained, but productivity decreases
Solution Approach 1:
The patent enables the validation system to correct its own false positive issues automatically through denormalization. By structuring the comparison to ignore surrogate keys, the system self-corrects the validation accuracy without requiring manual intervention, thereby maintaining completeness while significantly improving productivity.
3Reliability
If database schema is highly normalized, then data integrity is improved, but validation complexity increases due to surrogate key mappings
Solution Approach 1:
The patent segments the validation process into two independent parts: first creating denormalized views that simplify the data structure by removing surrogate keys, then performing the comparison validation on these simplified views. This segmentation separates the complex normalized storage structure from the simple validation process, maintaining data integrity while reducing validation complexity.
Data Source
AI summary
Some embodiments of a system and a method to validate database schema upgrade using denormalization have been presented. For instance, a database deployment engine may upgrade a database from a first version to a second version. The database upgraded stores a set of entities. By applying denormalization to both the upgraded database and a reference database (which contains a newly installed second version of the database), a database validation engine may reduce false positives during validation of the database schema upgrade scripts usable by customers to upgrade their database schema to the latest version.