Joe Celko's SQL for Smarties, 3rd Edition

30.1. Basic Graph Characteristics

The following code is from John Gilson. This code uses an adjacency list model of the graph, with nodes in a separate table. This is the most common method for modeling graphs in SQL.

CREATE TABLE Nodes
(node_id INTEGER NOT NULL PRIMARY KEY);

CREATE TABLE AdjacencyListGraph
(begin_node_id INTEGER NOT NULL REFERENCES Nodes (node_id),
 end_node_id INTEGER NOT NULL REFERENCES Nodes (node_id),
 PRIMARY KEY (begin_node_id, end_node_id),
 CHECK (begin_node_id <> end_node_id));

It is also possible to load an acyclic directed graph into a nested set model by splitting the nodes.

CREATE TABLE NestedSetsGraph (node_id INTEGER NOT NULL REFERENCES Nodes (node_id), lft INTEGER NOT NULL CHECK (lft >= 1) PRIMARY KEY, rgt INTEGER ...