February 13, 2002 at 4:33 pm
I am looking for a good way to store heirarchical data. e.g. for Customer data I want to provide my users with a tree view of customer groups that they can customise, and store the path of each customer.
I have thought about using a pathing system, with a separator (e.g. "\") and filtering the data using string compoarison functions. This aint quite elegant.
I have thought about simply storing the parentid of each customer (and group), and again filtering the data this way.
I have thought about using XML and converting it to an edge table rowcursor and incorporating this into an update statement (also storing the ParentID). But can't figure out how to get the data back into XML format (without XSLT). Also worried about performance hit of moving around large XML docs.
Considering the data will be bound to a Treeview control, and used for reporting as well, anyone have any better suggestions from experience??
February 13, 2002 at 6:17 pm
So you'd load everything initially, then after that only load the ones they selected? Depending on the volume XML isn't the worst idea. Shaped recordsets work pretty well, I find it easier to "think" with them since they leverage my experience with ADO. I think the idea of storing the parentkey/key makes sense. Interesting problem.
Andy
February 13, 2002 at 7:03 pm
Yes, I had planned to use a 'load on demand' type approach. The number of levels I load initially would depend upon volume of data. Remainder loaded as nodes are selected.
I was wanting to adopt the same approach for many (unforeseen) future uses. I was planning on implementing user customisable 'Libraries' in all my database apps. Therefore I guess volume could be an issue in the future.
In this case I dont think shaped recordsets can be directly saved to db (as their schema could end up being be any format the user chose). This makes them unfavourable.
Just storing the ParentID makes it very difficult to run a query like of the logic "Select All Children from Branch A" as records may be grouped under many different Parents within A.
Still very interested in any feedback.
February 14, 2002 at 5:09 am
Maybe someone else will have better ideas. Two comments to add though. First, I've used a treeview control on a couple projects recently and it is fairly fast. I did end up doing a background load in VB using a timer (since multi threading not really an option) to keep the time down since I was loading from an object hierarchy. The other is that I think its a mistake to query the control - it's only a representation of the data. I prefer to query the underlying object (or db) and then repaint if needed.
Andy
February 14, 2002 at 8:01 pm
If you do not have a fixed number of levels, it is difficult to do this without recursion. At least in T-SQL.
One thing you could do is only populate come of the tree list and only poplate the rest when someone clicks on it.
Steve Jones
June 12, 2002 at 10:00 am
I have created a series of stored procs that get hierarchical data from a customer table. It is for mlm trees. Me, who i signed up, who they signed up, etc.
I dynamically walk down the tree with a proc, returning a temporary table of ids, then join to that table to get real informatino, like name, datesignedup, how much they made this month, etc.
The generation of the tree is very fast, and I am pleased with it. I have even written stored procs like these in informix for another client. but i digress.
The part that is the most time consuming for me is ordering them in a genealogical order, within the levels. Like my leftmost child, and his leftmost child, on and on, until i need to get the rightmost child, etc. Ordering it this way takes time, as I have to hit every node in the tree.
I am trying to find a different way than i am doing it to organize that data. I'd be happy to show anyone my code to generate the trees, if you think it'd help you out.
its essentially a customer with his own id, and he has a parent id. My code will cycle through the number of levels you want, getting each level of children that are linked to parents above.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply