Semantic Table Identification via Domain Matching
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Identifying semantic relationships between database tables is challenging due to the lack of technical information in database schemas, making it difficult to retrieve relevant data for compliance with regulations and data privacy requirements, especially when data is stored across multiple systems with different purposes and structures.
Innovation Solution
The method involves analyzing database tables for semantic identifiers, such as domains, to determine semantically related tables, even if they lack technical relationships like foreign keys, and recursively identifying matching domains across multiple tables to establish hierarchical relationships, allowing for more comprehensive and accurate data retrieval.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of information
If traditional database schemas with foreign keys and technical relationships are used, then data retrieval is straightforward for explicitly defined relationships, but semantic relationships between tables lacking technical connections cannot be identified
Solution Approach 1:
The system performs preliminary analysis by extracting and storing semantic information (domains, data elements, descriptions) from database tables in advance. This preprocessing allows the system to quickly identify semantically related tables during query operations without performing complex analysis at query time, thus resolving the contradiction between capturing semantic relationships and maintaining system complexity.
2Measurement precision
If comprehensive semantic analysis is performed across all database tables, then all semantically related tables can be identified, but the analysis time and computational resources increase significantly
Solution Approach 1:
The system applies local quality by analyzing only the specific portions of database tables that are relevant to the query. When a semantic identifier is found in one table, the system locally analyzes only those tables containing matching domains, rather than performing a comprehensive analysis of the entire database. This selective approach maintains high identification accuracy while significantly reducing analysis time.
Solution Approach 2:
The system implements a hierarchical recursive analysis where the analysis of one table leads to the analysis of related tables, which in turn may lead to further related tables. This nested structure allows the system to progressively expand the analysis scope only as needed, starting from the initial query table and following semantic relationships outward, thus balancing completeness with efficiency.
3Adaptability or versatility
If recursive analysis is performed to identify indirect semantic relationships between tables, then comprehensive semantic networks can be built, but loops and infinite recursion may occur
Solution Approach 1:
The system implements feedback mechanisms by tracking which tables have already been analyzed and adding them to a set of visited tables. Before analyzing a table, the system checks whether it has already been visited, and if so, skips the analysis. This feedback loop prevents infinite recursion and ensures analysis stability while still comprehensively identifying all unique semantically related tables through recursive analysis.
Data Source
AI summary
Techniques and solutions are described for determining elements of different database tables have a semantic relationship, and optionally executing a value query using such tables. Tables, including those lacking a technical relationship (such as having a foreign key relationship or association) can be semantically related if an element of the table has the same semantic identifier, such as a domain, as an element of another table. The semantic relationship can exist even if other information, such as field names associated with a respective semantic identifier, are different between the two tables. A source table can be semantically related to one or more other tables, including indirectly based on semantic relationships between two or more of such other tables. Semantic table information can be recursively analyzed, including in a hierarchical manner, to identify a set of semantically related tables. The analysis can be constrained, such as to avoid loops or limit result size or running time.