0

enter image description here

Above is how my database table structure looks like. B refers to A, C refers to A, D refers to B and so on. Now, I am running an offline job, that drops all constraints on these tables and deletes a few records by evaluating them in my java application. So for instance, delete from A WHERE id > 20, will be translated into a bunch of selects.

  • SELECT B.ID, A.ID FROM B,A WHERE B.FKEY = A.ID
  • SELECT C.ID, A.ID FROM C,A WHERE C.FKEY = A.ID
  • SELECT D.ID, A.ID FROM D,B,A WHERE B.FKEY = A.ID AND D.FKEY = B.ID
  • ... so on..
  • .. finally.. SELECT H.ID, A.ID FROM A,B,D,F,H WHERE B.FKEY = A.ID AND D.FKEY = B.ID AND F.FKEY = D.ID AND H.FKEY = F.ID UNION SELECT H.ID, A.ID FROM A,C,E,G,H WHERE C.FKEY = A.ID AND E.FKEY = C.ID AND G.FKEY = E.ID AND H.FKEY = G.ID

Retrieving that data and verfifying for A.ID > 20 in code and then deleting what are not required is how my application performs delete (I know it might sound a little crazy, but this is how it is supposed to work).

Now the question I have here is this... How do I make this generic? My challenge is to identify tables like "H" that have multiple inheritance (or say that form cycles in the ER graph). I've tried looking into graph theory, but it is pretty confusing - way too much information for me to process.

In short, I would like to identify cycles in the ER graph and construct UNION queries for such tables.

Jay
  • 2,394
  • 11
  • 54
  • 98
  • There isn't really a cycle in your directed graph. There is more than one path from H to A. Is this the condition you want to detect: if there is more than one path? – Eduardo Jan 14 '13 at 09:14
  • Yes, I would like to identify nodes that have more than one path from a given node. – Jay Jan 14 '13 at 10:48
  • 25 Views - No answers? Did my question make sense, guys? – Jay Jan 14 '13 at 15:30
  • Well, given that you need to determine all paths between two nodes of your graph, you can easily search for that. Here is an example is SO itself: http://stackoverflow.com/questions/58306/graph-algorithm-to-find-all-connections-between-two-arbitrary-vertices – Eduardo Jan 14 '13 at 16:30
  • @Eduardo I tried executing that example. It printed nothing - returns empty on getAdjacentNodes. – Jay Jan 14 '13 at 20:27

0 Answers0