Logical Table Mapping for Flexible Database Schema Evolution
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional databases face challenges in efficiently managing schema evolution due to the need for re-normalization and table repartitioning, which results in downtime and decreased query performance, especially when dealing with semi-structured data and multi-tenant databases.
Innovation Solution
A method is introduced that provides a logical view for logical tables, mapping them into a physical table with predefined constraints, allowing for efficient schema evolution with minimized data migration by maintaining associations between records and using sparse columns to optimize storage and query performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If conventional databases use re-normalization and table repartitioning for schema evolution, then schema flexibility is improved, but query performance deteriorates and downtime increases
Solution Approach 1:
The patent segments the database schema into multiple independent logical schemas that can be evolved separately. Each logical schema can be modified, added, or removed without affecting others, allowing schema evolution to proceed without blocking queries or requiring full table repartitioning.
Solution Approach 2:
The patent introduces an intermediary mapping layer between logical schemas and physical storage. This mapping layer allows schema changes to be translated into efficient physical operations, avoiding direct impact on query performance while maintaining schema flexibility.
2Adaptability or versatility
If conventional databases perform table repartitioning for schema evolution, then schema adaptability is improved, but time consumption increases due to data migration
Solution Approach 1:
The patent performs preliminary actions by pre-defining multiple logical schemas and their mappings to physical storage before schema evolution is needed. When schema evolution is required, the system can switch between pre-defined schemas or activate pre-prepared mappings, avoiding time-consuming data migration during critical periods.
Solution Approach 2:
The patent implements dynamic schema management where logical schemas and their mappings can be modified, added, or removed dynamically without requiring static table repartitioning. The mapping layer dynamically adapts to schema changes while maintaining efficient query execution.
3Adaptability or versatility
If key-value pairs are used for flexible schema, then schema flexibility is improved, but join operations increase excessively
Solution Approach 1:
The patent applies local quality by organizing data into logically separated schemas with specific structures optimized for their particular access patterns. Each logical schema maintains appropriate normalization and relationships locally, reducing the need for excessive joins while preserving overall schema flexibility.
Data Source
AI summary
The subject matter described herein relates to managing data with flexible schema. A method, computer storage medium, and system are provided for managing data with flexible schema. In one embodiment, the method comprises providing a logical view for logical tables of a database; and managing mappings between the logical tables and a physical table according to predefined mapping constraints, each of the logical tables mapped as a part of the physical table. The mapping constraints at least specify that (i) a logical column in the logical tables is mapped to at least one physical column in the physical table, and (ii) distinct logical columns in one of the logical tables are mapped to distinct physical columns in the physical table. As a result, schema evolution may be done with minimized data migration.