Schema Mapping for Multi-Schema Data Views
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In enterprise systems with multiple ERP systems, conventional schema mapping fails to generate data views effectively when separate data systems are consolidated into a single database, leading to limitations in data modeling and accessibility.
Innovation Solution
An enhanced schema mapping approach is implemented, using a schema mapping module that performs schema-level, data view-level, and database table-level mappings to correctly associate data models with physical database schemas, ensuring that data views can be generated from data view definitions referencing tables across multiple schemas.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If separate ERP systems are consolidated into a single database system, then data operations are consolidated and security is improved, but data modeling capability is compromised and data views cannot be generated effectively
Solution Approach 1:
The patent segments the schema mapping process into three distinct levels: authoring schema level, data view level, and physical schema level. Each level handles specific mapping relationships, allowing the system to preserve data modeling capability while working with consolidated physical schemas. The authoring schema maintains the original data model structure, the data view level defines the required views, and the physical schema level adapts to the consolidated database structure.
Solution Approach 2:
The patent introduces an intermediary schema mapping layer that sits between the authoring schema and the physical schema. This intermediary layer includes data view definitions and schema mapping relationships that translate between the original data model structure and the consolidated physical schema, enabling data views to be generated without directly accessing or modifying the physical schema structure.
2Device complexity
If conventional schema mapping is used after consolidation, then system complexity is reduced, but data views cannot be generated when tables reference multiple schemas
Solution Approach 1:
The schema mapping is divided into multiple hierarchical levels: authoring schema definitions, data view definitions, and physical schema mappings. Each level handles a specific aspect of the mapping, allowing the system to manage cross-schema references systematically without overwhelming complexity.
Solution Approach 2:
The patent adds a new dimension to schema mapping by introducing the data view level as an intermediate layer between authoring and physical schemas. This third dimension enables the system to handle complex cross-schema references by defining views at the data view level that can reference tables from multiple physical schemas without requiring direct complex mapping at the physical level.
3Ease of operation
If authoring schema references are directly mapped to physical schemas, then mapping simplicity is maintained, but data views fail to generate when tables are distributed across multiple schemas
Solution Approach 1:
The patent introduces data view definitions as an intermediary layer between authoring schema references and physical schema mappings. The data view level captures the logical structure and relationships needed for data views, while the schema mapping level handles the physical distribution across schemas. This intermediary layer preserves mapping simplicity at the authoring level while ensuring reliable data view generation by handling cross-schema references at the data view level.
Solution Approach 2:
The mapping process is segmented into distinct levels with specific responsibilities: authoring schema level for simple reference definitions, data view level for defining views that may span multiple schemas, and physical schema level for actual table locations. This segmentation maintains simplicity at each level while collectively solving the cross-schema reference problem.
Data Source
AI summary
Schema mapping is provided at several levels of granularity to generate views from a data model. The schema mapping allows for the database tables referenced among the data views contained in the data model to come from a collection of several physical schemas, even though the data model may comprise a single authoring schema.