[thelist] XML or SQL?

Gary McPherson lists at ingenyus.net
Wed Jan 28 22:45:45 CST 2004


Hey all - I'm looking at the architecture for a new project that's come 
my way, but I'm unsure of the best way to store the data representation. 
Unlike most applications I've dealt with, data would not be navigated in 
a linear manner, but more in a "web"-like fashion. It's hard for me to 
vocalise, so I've put up a small example image at 
http://www.ingenyus.net/experiments/data.gif

 From that, you'll see that any node can be directly connected to one or 
more other nodes. When adding a node, the ID of it's first sibling node 
will be provided and further direct siblings can be added later. 
Essentially, I need to be able to search on connected nodes by 
specifying their distance from a given node. So if I had a search length 
of 1, and I was looking up matches for node A, I would find nodes B, C, 
D and E. Increasing the length to 2 would then return all nodes within 
two hops; B, C, D, E, F, G, H, I, J, K, L, M and S

Using SQL, I could have a table [Node], and a lookup table of node IDs 
and their direct sibling node IDs, e.g.

+----------+  +----------+
| Node     |  | Siblings |
+----------+  +----------+
| NodeID   |  | Node1ID  |
| NodeName |  | Node2ID  |
| MoreData |  +----------+
+----------+

I could then run a recursive query along the lines of: (pseudo-code)
CREATE TempTable (...)
/* 1st hop */ INSERT INTO TempTable SELECT Node2ID FROM Siblings WHERE 
Node1ID = A
/* 2nd hop */ INSERT INTO TempTable SELECT Node2ID FROM Siblings WHERE 
Node1ID IN TempTable AND Node2ID NOT IN TempTable
...
...
/* nth hop */ INSERT INTO TempTable SELECT Node2ID FROM Siblings WHERE 
Node1ID IN TempTable AND Node2ID NOT IN TempTable

While I know it's doable, I'm less certain of it's efficiency, 
especially considering the number of nodes the system will have to deal 
with (millions). I'm sure this sort of thing is what XML is well-suited 
for, but I'm still not sure how I would go about querying the data store 
in a similar fashion. Is anyone able to detail an effective way to do 
this using XML, or should I just stick with an SQL solution?

Many thanks,

Gary




More information about the thelist mailing list