[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