Automated Query Plan for Many-to-Many Database Deduplication
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current solutions for avoiding double counting in database data accumulation, especially in the presence of many-to-many relationships, rely on manual and error-prone techniques, which are tedious and inefficient.
Innovation Solution
An automated method is implemented using a computer to receive a report definition, detect bridge tables defining many-to-many relationships, generate a query plan with layered sub-queries, and execute it to eliminate duplicated data, employing conditional selection operations to ensure accurate data accumulation.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If automated query generation is implemented to eliminate double counting, then productivity and accuracy improve, but device complexity increases
Solution Approach 1:
The query plan is segmented into multiple layered sub-queries, where each sub-query handles a specific aspect of data retrieval and aggregation. This segmentation allows the complex query to be broken down into manageable components that can be executed sequentially, improving both productivity and manageability despite the inherent complexity.
Solution Approach 2:
The system introduces an intermediary query plan generation mechanism that automatically creates optimized query structures between the user's report definition and the underlying database. This intermediary layer handles the complexity of many-to-many relationships and bridge tables, shielding users from complexity while maintaining high productivity.
2Ease of operation
If manual techniques are used to avoid double counting, then ease of operation is maintained, but reliability deteriorates due to errors
Solution Approach 1:
The system implements self-service automation where the query plan generation automatically detects bridge tables and many-to-many relationships, then constructs appropriate layered sub-queries without manual intervention. This eliminates human error while maintaining ease of operation, as users simply define their report requirements and the system handles the complex deduplication logic autonomously.
3Measurement precision
If layered sub-queries are generated to handle many-to-many relationships, then measurement precision improves, but loss of time increases due to complex processing
Solution Approach 1:
The system performs preliminary actions by pre-identifying bridge tables and many-to-many relationships in the database schema before query execution. This allows the query plan generator to pre-structure the layered sub-queries with optimal join and aggregation logic, ensuring measurement precision while minimizing execution time through advance preparation.
Solution Approach 2:
The system dynamically changes query parameters and execution strategies based on the detected database structure. When bridge tables and many-to-many relationships are identified, the system adjusts the query plan to use efficient aggregation methods and conditional selection operations, maintaining precision while optimizing execution time through parameter adaptation.
Data Source
AI summary
As disclosed herein a method, executed by a computer, for avoiding double counting in the presence of many-to-many relationships in database data includes receiving a report definition from a data management system, detecting a bridge table defining many-to-many relationships between at least two datasets, generating a query plan with layered sub-queries, and executing the query plan including the layered sub-queries to provide query results corresponding to the report definition. A query plan with layered sub-queries may include instructions to join, partition, normalize, group, and summarize tabular data. Additionally, a conditional selection operation may be used to detect and eliminate duplicated data from a field specified in the report definition. The resulting query plan may be executed as a single query language. A computer program product and an apparatus corresponding to the described method are also disclosed herein.