[thelist] XML or SQL?

[Joshua Olson]

----- Original Message ----- 
From: "Gary McPherson" <lists at ingenyus.net>
Sent: Wednesday, January 28, 2004 11:45 PM


> 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

Gary,

Your SQL solution is very elegant and I applaud your very logical approach
to solving the problem.  If you are looking for efficiency using the native
tools provided by the database then you have most likely found the best
solution in regards to system simplicity (which is always great in the long
run) and understandability.

But, if you are looking for a solution that will provide optimal results (in
terms of time necessary for execution) I fear that you probably won't find
your solution in the database.  I'd suggest that you hire a c/c++ programmer
to build you a custom application that does this search using an internal
structure of pointers to form a non-directed cyclic graph.  The application
should load itself from the database (using the structure you outlined with
nodes and edges in separate tables) and should also accept real-time updates
to the graph.  You could tie the calls to the update into the trigger
actions on the table so that any updates to the table would automatically
update the internal representation of this supporting program.

When you want to get the siblings at distant x you'd call this program and
ask it to return the list of node PK's.  It would execute very quickly since
the entire graph would be stored in memory and the structure would optimally
support the types of query we are asking of it.  If you hid the call behind
a sp you could make it completely transparent to the end user within the web
page.

Sorry if that was too much detail, but honestly, the database is probably
not the optimal solution for this problem, though it may serve as part of
the solution, as outlined above.

<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com/service_areas/
706.210.0168