METHODS OF MODELING HIERARCHICAL STRUCTURES IN RELATIONAL DATABASES

Abstract

The paper examines the main methods for representing hierarchical structures in relational databases and typical queries for these data structures. The methods reviewed include Adjacency List, Nested Sets, Closure Table, and Materialized Path. Each of these models has its own advantages and disadvantages depending on the types of operations being performed. Quantitative metrics for data retrieval times were obtained for data represented in the database using these methods. Based on these metrics, an analysis of the appropriateness of using these methods was conducted, depending on the characteristics of nesting and data volumes. Adjacency List is simple to implement and convenient for operations at the level of individual nodes, such as adding or deleting a leaf, but it is not optimal for complex queries involving retrieving subtrees or paths between nodes. Nested Sets demonstrate high efficiency in retrieving subtrees, but operations such as adding and deleting nodes are complex and resource-intensive. Closure Table offers high flexibility for queries involving ancestor and descendant relationships but requires significant resources to maintain data consistency during frequent updates. Materialized Path is efficient for queries involving retrieving paths between nodes and subtrees.