28.1. Adjacency List Model

Most 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. It is a recording of the edges in a “boxes and arrows” diagram, something like this simple table:

(child CHAR(2) NOT NULL,
 parent CHAR(2),  -- null is root
 PRIMARY KEY (child, parent));
child  parent
'A'   NULL
'B'   'A'
'C'   'A'
'D'   'C'
'E'   'C'
'F'   'C'

The queries for the leaf nodes and root are obvious. The root has a NULL parent, ...

Get Joe Celko's SQL for Smarties, 3rd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.