Database Join Candidate Identification via Casting Similarity
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems are inefficient in identifying and guiding users to formulate queries with semantically meaningful joins, leading to computational waste, misleading results, and increased resource usage due to inadequate identification of join candidates, especially when schema relationships are not described.
Innovation Solution
A method and system that identify join candidates by assigning casting similarity indexes based on data type compatibility, presenting them to users, and allowing data queries to obtain tabular data efficiently, using a multi-pass algorithm that considers column metadata and data similarity.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If existing database analytic tools are used, then data queries can be performed, but they are inefficient and require substantial configuration and training
Solution Approach 1:
The system automatically identifies join candidates by analyzing data types and casting similarity between columns of different tables. The multi-pass algorithm autonomously determines potential joins without requiring user configuration or training, making the system self-serve the complexity of join identification while maintaining high query efficiency
Solution Approach 2:
The system performs preliminary analysis of column metadata and data types before query execution to pre-identify join candidates. This preliminary action of casting similarity assessment and join candidate presentation occurs before the actual query runs, reducing the complexity burden on users during query formulation
2Adaptability or versatility
If join candidates are identified without considering data type compatibility, then more join possibilities are found, but computational resources are wasted on semantically meaningless joins
Solution Approach 1:
The system changes the parameter of data type compatibility assessment by introducing casting similarity indexes that evaluate the convertibility between data types of different columns. This parameter change enables the system to filter join candidates based on semantic meaningfulness, reducing computational waste on incompatible type joins while maintaining versatile join identification capabilities
Solution Approach 2:
The system replaces mechanical brute-force join testing with a semantic-based filtering mechanism using casting similarity assessment. Instead of mechanically trying all possible joins and filtering later, the system uses data type compatibility analysis to pre-filter meaningful joins, substituting computational brute-force with semantic understanding
3Measurement precision
If a multi-pass algorithm is used to identify join candidates, then accuracy of join identification improves, but processing time increases
Solution Approach 1:
The join identification process is segmented into multiple passes: first pass analyzes column metadata and data types, second pass performs casting similarity assessment, and third pass validates join candidates. This segmentation allows the system to progressively refine accuracy while managing processing time by stopping early for clearly incompatible types and focusing computation only where needed
Data Source
AI summary
Identifying join candidate includes identifying pairs of columns within a data source. Each pair includes a first column in a first table of the data source and a second column of a second table of the data source. For each pair, a casting similarity index is assigned from a predefined scale that includes a first casting similarity level indicative of a very low casting similarity level, where the casting similarity index is determined based on an extent to which data values from the first column are convertable to match a data type of the second column within the each pair, and where the very low casting similarity level is assigned to the each pair in a case that the first column has a boolean type and the second column has a float type. The join candidate is presented on a device of a user for selection by the user.