Relational Database Hierarchized Data Filtering via Transitive Closure
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Relational databases lack the ability to efficiently manage and filter hierarchized data across multiple levels using standard SQL queries, leading to performance degradation and complexity due to the need for multiple queries and complex algorithms.
Innovation Solution
A relational database installation that represents hierarchized data in a dedicated table with each record containing its dependencies across all levels, allowing for filtering using internal database algorithms and SQL queries, leveraging mechanisms like indexes and stored procedures for optimized performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If conventional relational database approach is used to store hierarchized data, then data storage is simple, but filtering performance degrades and multiple queries are required
Solution Approach 1:
The patent pre-calculates and stores the transitive closure of the hierarchy table during data insertion or modification operations. This preliminary action ensures that when filtering queries are executed, the database engine can directly utilize the pre-computed transitive closure data without performing complex recursive operations at query time, thereby significantly improving filtering performance and reducing query execution time.
2Ease of operation
If specialized algorithms are used for filtering hierarchized data, then filtering capability is improved, but device complexity increases
Solution Approach 1:
The patent enables the database engine to perform filtering operations autonomously by utilizing the transitive closure data structure that is already stored in the database. The filtering capability is improved because the database engine can directly query the pre-computed transitive closure without requiring external specialized algorithms. This self-service approach maintains simplicity by leveraging existing database functionalities rather than introducing complex external processing systems.
3Loss of information
If multiple queries are multiplied to retrieve hierarchized data, then data completeness is improved, but productivity decreases
Solution Approach 1:
The patent merges multiple hierarchical levels of data into a single transitive closure table that contains all ancestor-descendant relationships. This allows a single SQL query to retrieve complete hierarchized data across all levels by simply querying the transitive closure table, eliminating the need to multiply multiple queries. Data completeness is maintained while query efficiency is significantly improved.
4Measurement precision
If conventional data model is used without transitive closure, then storage simplicity is maintained, but measurement precision of hierarchical relationships deteriorates
Solution Approach 1:
The patent segments the hierarchical relationship data into two distinct components: the original simple parent-child relationship table and the pre-computed transitive closure table. This segmentation allows the database to maintain both storage simplicity (through the original table) and measurement precision (through the transitive closure table that explicitly stores all ancestral relationships). The complexity is distributed and managed separately, making the system both precise and maintainable.
Data Source
AI summary
An installation for managing a database, the database having a database filter to provide the data sought in response to a query defining the filter and at least one data hierarchy of at least three hierarchical levels. The installation includes an interrogator to provide at least one query defining a filter to select data sought by using the database filter. The installation includes a representation of a hierarchy table that includes, for each hierarchised datum, the data on which it is dependent, regardless of the number of hierarchical levels separating them, the data being contained in a separate record of the table which also contains the hierarchised datum. During an interrogation on the basis of a query pertaining to hierarchised data, the database filter provides a response to the query formulated on the basis of the hierarchy table without resorting to external interrogation tools.