Virtual Columns for Logical Data Mapping in Relational Databases
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Relational databases struggle to capture and present logical relationships between data elements, leading to inefficient and un-maintainable database designs, as users' intuitive views of data often differ from the physical database structure, hindering query composition and data interpretation.
Innovation Solution
A database abstraction model is created that defines logical fields and access methods to map data from underlying physical tables, allowing for virtual columns that remap data structures to conform to users' logical views, enabling abstract queries that decouple the query application from the physical database schema.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If data is stored in physical database tables according to relational database structure, then data can be stored and retrieved using standard relational techniques, but the physical database structure does not capture logical relationships between data elements, leading to inefficient query composition and data interpretation
Solution Approach 1:
The patent introduces a virtual column table as an intermediary layer between the physical database tables and the query application. This virtual column table contains virtual columns that represent logical groupings of data from multiple physical tables, allowing queries to be composed based on logical relationships without changing the physical database structure. The virtual column acts as a mediator that translates logical data views into physical database queries.
2Ease of operation
If the database schema is changed to better represent logical views, then query flexibility and maintainability improve, but the physical database structure becomes more complex and difficult to maintain
Solution Approach 1:
The patent segments the database into three distinct layers: physical database tables for data storage, a virtual column table for logical data representation, and query applications for data access. This segmentation allows each layer to be optimized independently - the physical layer maintains efficient storage and retrieval, while the virtual layer provides intuitive logical views without imposing complexity on the physical structure.
3Productivity
If the physical database schema is tightly bound to the query application, then data access is efficient, but changes to the database schema require corresponding changes to the query application, reducing maintainability
Solution Approach 1:
The virtual column table serves as a stable intermediary that shields the query application from physical database schema changes. When physical tables are modified, only the virtual column definitions need to be updated, not the query application itself. This maintains data access efficiency while providing flexibility for schema evolution.
4Ease of operation
If data from multiple physical tables is physically joined to create logical views, then query results match user expectations, but the database design becomes un-maintainable and inefficient
Solution Approach 1:
The patent creates a virtual copy of the joined data structure in the virtual column table, which references the underlying physical tables through defined relationships. This virtual copy provides the expected logical view to users without physically duplicating or permanently joining the tables, maintaining database normalization and efficiency while satisfying user query expectations.
Data Source
AI summary
Embodiments are provided for associating a virtual column with a target physical table. The data for the virtual column originates from a source physical table where the data physically resides. In this way, users can access the data in a manner consistent with the users' logical perspective of the data.