Composite Join Querying Across Relational Database Tables
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Relational databases face challenges in efficiently querying and indexing structured data, particularly when dealing with complex queries across multiple tables, as they lack the simplicity of modern search engines and impose significant constraints on users, leading to inefficient resource usage and increased index sizes due to denormalization or post-processing approaches.
Innovation Solution
A computer system executes a user-supplied query across multiple relational database tables linked by a common foreign key using a composite join method, where any query term can be matched by any table, and logical operations are performed on join keys using bitwise arithmetic to efficiently combine results.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If denormalization is used to join tables at index time, then query capability across multiple tables is improved, but index size increases by orders of magnitude
Solution Approach 1:
The patent divides the index into separate table-specific indexes rather than creating a single denormalized index. Each table maintains its own index structure, and the join mechanism segments the query processing into independent table queries followed by result combination, avoiding the exponential growth of index size while preserving multi-table query capability
Solution Approach 2:
The patent introduces an intermediary join mechanism that connects separate table indexes without merging them. This intermediary layer processes join conditions and combines results from independent table queries, enabling multi-table searches without the need for denormalized index structures
2Adaptability or versatility
If post-processing approach is used to extract and winnow data, then query flexibility is improved, but computational resources are inefficiently used
Solution Approach 1:
The patent performs preliminary filtering at the index level by evaluating join conditions during the query execution process rather than extracting all data and filtering later. This preliminary action reduces the volume of data that requires post-processing, thereby improving computational efficiency while maintaining query flexibility
Solution Approach 2:
The patent extracts only the necessary portions of data from each table index that satisfy the join conditions, rather than extracting entire tables. This partial action approach minimizes unnecessary data processing and resource consumption while preserving the ability to handle flexible query requirements
3Reliability
If complex SQL queries are used to enforce data normalization, then data integrity is improved, but ease of operation deteriorates
Solution Approach 1:
The patent creates a simplified query interface that copies the essential functionality of complex SQL joins without requiring users to write complex SQL statements. This interface layer maintains data integrity through the underlying normalized structure while providing simple, intuitive query syntax that resembles web search engines
Data Source
AI summary
A computer system executes a user-supplied query across multiple relational database tables linked by a common key. A new query form, called a composite join, takes a user-supplied query including one or more terms optionally joined by logical operations, an identification of two or more tables and an identification of a common join key shared across the tables. The composite join applies the query across the tables such that any of the query terms can be matched by any of the tables. A query is performed across all tables for each query term and any join keys associated with matching records from the tables are identified in a set of join keys associated with the respective term. The logical operations are then performed on the resulting sets of join keys to combine the results of the term queries according to the user query.