5

I'm trying to determine if it is possible to easily model a directed cyclic graph with a closure table (and/or possibly other helper tables) in SQL. For example, suppose I have this directed graph (all pointing down):

enter image description here I'm having trouble modeling this with a closure table.

We would get this table:

  • (ancestor, descendant, path-length)
  • (1, 1, 0)
  • (2, 2, 0)
  • (3, 3, 0)
  • (4, 4, 0)
  • (2, 4, 1)
  • (3, 4, 1)
  • (1, 4, 2)

A closure table breaks down when removing the edge between 1 and 2.

DELETE FROM closure WHERE descendant IN 
(SELECT descendant FROM closure WHERE ancestor=2);

DELETE FROM closure WHERE descendant=2 AND ancestor=1;

The first delete query removes paths between 1 and 4, and 3 and 4, which shouldn't be deleted

I can't find a solution to this with a closure table, and it get's further complicated if 4 were to point to 1. (becoming cyclic).

I haven't been able to find much written on this subject. I'd appreciate any input regarding how to implement this type of graph in SQL, or if SQL is simply not a good choice for this type of graph.

matt.hallman
  • 141
  • 1
  • 6

1 Answers1

4

I've done cyclic graphs in a closure table before. It's much more expensive to delete edges but it can be done.

First of all you can forget about path-length. What's the path-length of a cycle? Infinity? Drop that column.

When you remove an edge (parent, child) from the graph you have to consider the possibility that there are alternate paths from parent's ancestors to child's children. So before deleting the edge save all of the parent's ancestor's children - these are the potential alternate paths. Then after you've deleted the edge re-add parent's ancestor's children to the closure table, excluding duplicate rows.

Roy Paterson
  • 863
  • 9
  • 17