Machine Learning Primary Foreign Key Relationship Determination
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Determining primary key-foreign key relationships in large databases is a time-consuming and processor-intensive process, often resulting in inaccurate manual determinations due to the vast number of column pairs that need to be evaluated, and existing methods rely on single features or rule-based approaches that are limited in accuracy and require human intervention.
Innovation Solution
A machine learning-based system that combines multiple classification algorithms to intelligently determine primary key-foreign key relationships by splitting training data to avoid skewing and using computed features, reducing the need for manual intervention and improving accuracy.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If a brute force method is used to evaluate all column pairs to find primary key-foreign key relationships, then completeness of relationship detection is improved, but computation time and processing resources are excessively consumed
Solution Approach 1:
The patent segments the evaluation process into two stages: first applying inclusion dependency testing to filter out obviously unrelated column pairs, then applying machine learning only to the reduced subset of candidate pairs. This segmentation reduces the computational burden while maintaining detection completeness.
Solution Approach 2:
The patent performs preliminary filtering using inclusion dependency checks before the main machine learning evaluation. This preliminary action eliminates clearly non-matching column pairs early in the process, reducing the number of pairs that require intensive machine learning analysis.
2Ease of manufacture
If conventional rule-based approaches are used to determine primary key-foreign key relationships, then ease of implementation is improved, but accuracy is limited due to reliance on predefined rules
Solution Approach 1:
The patent replaces conventional rule-based mechanical approaches with a machine learning system that automatically learns patterns from data. This substitution enables the system to capture complex, non-obvious relationships that predefined rules cannot detect, significantly improving accuracy.
Solution Approach 2:
The patent transforms the relationship determination problem from a rule-matching task into a classification problem with multiple features. By changing the approach parameters to include statistical measures, data distribution analysis, and multiple classification algorithms, the system achieves superior accuracy.
3Device complexity
If a single feature such as column name is used to determine primary key-foreign key relationships, then simplicity of the method is improved, but accuracy deteriorates because the single feature may not satisfy primary key properties
Solution Approach 1:
The patent combines multiple features (column names, data types, statistical properties, distribution patterns) into a composite feature vector for each column pair. This composite approach allows the machine learning system to evaluate relationships from multiple perspectives simultaneously, dramatically improving identification accuracy.
Solution Approach 2:
The patent transitions from single-feature analysis to multi-dimensional feature space analysis. By introducing additional dimensions such as data distribution statistics, null value patterns, and referential integrity measures, the system achieves more accurate relationship determination.
4Productivity
If pruning criteria and ranking functions are used to reduce the number of column pairs to evaluate, then computation time is reduced, but accuracy is limited by the programmer-defined constraints
Solution Approach 1:
The patent introduces inclusion dependency testing as an intermediary filtering step between the exhaustive search and machine learning evaluation. This intermediary efficiently eliminates obviously unrelated column pairs while preserving all potential relationships, achieving both efficiency and accuracy.
Solution Approach 2:
The patent performs preliminary filtering using multiple criteria including inclusion dependency, data type compatibility, and basic statistical measures before applying the computationally intensive machine learning algorithms. This preliminary action maintains accuracy while significantly improving evaluation efficiency.
Data Source
Figure 1A
Figure 1B
Figure 2
AI summary
A method and a machine learning relationship determination system (MLRDS) for determining primary key-foreign key (PK-FK) relationships among data in tables of a target database through machine learning (ML) are provided. The MLRDS selects columns of the tables in the target database and identifies inclusion dependency (ID) pairs from the selected columns. The MLRDS receives training data and validation data from a source database, computes PK-FK features for the inclusion dependency pairs, the training data, and the validation data, and generates trained ML models and validated ML models using the PK-FK features. The MLRDS determines an optimum algorithm decision threshold for a selected machine learning classification algorithm (MLCA), using which the MLRDS determines a resultant on whether the inclusion dependency pair is a PK-FK pair or a non-PK-FK pair. The MLRDS performs majority voting on the resultant for multiple MLCAs to confirm the PK-FK relationships between the inclusion dependency pairs.