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.

Combustor

Possible duplicate of Closure Table INSERT statement including the level/distance column – Stabler

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

Jansson

Thanks for the answer! I would like to avoid graph databases and see how this would work in SQL without any special help (like you're saying you use, would love to see an example). Yeah I was wondering if the graph was highly connected with lots of cycles what would happen, is it even possible to have a runtime dynamic SQL table(s) to handle it, etc. – Combustor

oqgraph is 9 years old! I could find no newer version than 2009 – Dunn