Chapter 28 - Trees and Hierarchies in SQL

Abstract

A tree is a special kind of directed graph. Graphs are data structures that are made up of nodes connected by edges. There are several ways to define a tree: it is a graph with no cycles and it is a graph where all nodes except the root have indegree one and the root has indegree zero. Another defining property is that a path can be found from the root to any other node in the tree by following the edges in their natural direction. Most Structured Query Language (SQL) databases use the adjacency list model for two reasons. The first reason is that Dr. Codd came up with it in the early days of the relational model, and nobody thought about it after that. The second reason is that the adjacency list is a way of “faking” pointer chains—the traditional programming method in procedural languages for handling trees. Because SQL is a set-oriented language, the nested set model is a better model for the approach discussed in this chapter.