Geek Answers Handbook

Updating graph data in SQL

I need to go through and update the graph in SQL.

To bring this into the future, I will give an example:

  • Each company may represent a different company in this subject.
  • Companies may represent each other, but not for the same entity.

This assumes tables such as:

companies(id) , representations(source_id, destination_id, subject) .

But the rule is that when my company no longer represents you on this topic, no company from my network can represent my company on the same issue.

I hope you understand what I mean.

So, with simple data like:

 C1: --sell--> C2 --pay--> C3 C2: --sell--> C3 --pay--> C1 C3: --mail--> C1 --sell--> C4 C4: --deliver-->C1

Now we delete the view (I should have called it a relation) C2--sell-->C3 we should get the following structure ( [X] - deleted):

 C1: --sell--> C2 --pay--> C3 C2: [X]--sell--> C3 --pay--> C1 C3: --mail--> C1 [X]--sell--> C4 C4: --deliver-->C1

So, the question is, how can I choose all the companies that are in the chain from mine for this subject?

I assume that a CTE recursive expression is the only way to do this.

NOTES:

  • A structure is not a tree; it is an ordered cyclic graph.
  • The graphical database is not an option right now (this is only a small part of the system)
  • Updates do not have to be immediate, and it’s normal for them to be consistent in the long run (from a few seconds to a few minutes).
+4
source share
1 answer

It looks like you need to support transitive chart close for each object. Check out the document on how to implement an incremental grading system (IES) with SQL seems to do the trick. The document provides extensive SQL examples for oriented acyclic, non-oriented, and arbitrary directed graphs.

It looks like the schedule is acyclic for each of your objects, which, fortunately, is the simplest case.

+3
source

More articles:


All Articles