Closure table equivalent for graph structures in SQL

Question

This question How to store tree structure in sql? lead to the idea of a Closure table for storing trees that is optimal in many ways.

The question is is there something along these lines for graph structures in SQL. I saw this paper which seems to outline a graph index structure but it's a bit over my head. Wondering if there is a sort of way to create a few auxiliary tables to handle common queries on graph data in SQL.

Answer 1

I did the presentation you linked to, and I've been asked about implementing general graphs with a similar method, but I've never gotten around to it.

Certainly there are problems with the technique if you have cyclic graphs, unless you can unambiguously identify a "starting node." Because otherwise if you start with any node in a cycle, you'd want to be able to traverse the whole cycle in the graph.

It might be easier in SQL using a recursive CTE, but I most often use MySQL which doesn't support CTE syntax until version 8.0. And if you do have recursive CTE capability, you'd be better off using that instead of a closure table, because you have less chance for data anomalies.

Another option is to explore a specialized graph database. For MySQL/MariaDB, there's a community storage engine that optimizes for tree and graph queries: https://openquery.com.au/products/graph-engine