Hierarchical Data Querying with NODE Type
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional SQL-responsive database systems face usability and performance deficiencies when handling hierarchical data, particularly due to the limitations of the traditional relational model and the need for manual hierarchy encoding schemes without query engine support.
Innovation Solution
The introduction of a special-purpose data type called 'NODE' and extensions to the SQL query language, including functions and Data Description Language (DDL) and Data Manipulation Language (DML) constructs, enable efficient representation and manipulation of hierarchical data within a database system, allowing for the abstraction of node positions and enhanced query capabilities.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If hierarchical data is handled using traditional relational model and SQL statements, then the data can be stored in a database, but the handling becomes difficult and inefficient
Solution Approach 1:
The patent introduces a special-purpose NODE data type as an intermediary between the relational database model and hierarchical data structures. This NODE type encapsulates hierarchical positioning information (parent-child relationships, path information) within the relational table structure, allowing the database engine to efficiently handle hierarchical queries without requiring application-level processing logic.
2Adaptability or versatility
If custom hierarchy encoding schemes are implemented manually, then hierarchy representation is possible, but query engine support is lost
Solution Approach 1:
The NODE data type serves multiple functions simultaneously: it stores hierarchical positioning information, enables hierarchical queries through SQL functions, and maintains compatibility with the relational model. The patent provides a suite of SQL functions (HIERARCHY functions) that operate on NODE values, allowing the database query engine to automatically handle hierarchical operations without manual application logic.
3Reliability
If adjacency list model is used with RCTE and stored procedures, then hierarchical relationships can be represented, but usability and performance deficiencies occur
Solution Approach 1:
The patent replaces the mechanical approach of using Recursive Common Table Expressions (RCTE) and stored procedures with a declarative SQL function approach. Instead of requiring complex recursive query logic, the NODE data type with its associated HIERARCHY functions provides a simpler, more usable interface that leverages the database engine's native capabilities while maintaining reliable hierarchical relationship representation.
Data Source
AI summary
A system includes a stored table comprising a plurality of rows and at least one column, each of the plurality of rows associated with a node of a hierarchy, and one of the at least one columns implicity representing a position of a row's associated node within the hierarchy, wherein a Structured Query Language query is received and, response to the query, one or more values of the one of the at least one columns are determined and one or more properties of the hierarchy are determined based on the one or more values.