Steve,
I've found Celko's "nested sets" logic perfectly usable, and has the
advantage over most models of being able to "prune and graft" your tree
if you have the need. I agree with his comment that arrays make this
*very* easy, but they are by no means a requirement. An example is here:
http://www.intelligententerprise.com/001020/celko.jhtml
Ben
Wills, Steve wrote:
Almost hate to open this can of worms, Pandora's Box, whatever, but playing
around with one of Razzak's examples related to the DB Treeview Form Control, I
feel compelled. So, lemme' ask, what do y'all do w/in RBase when dealing with
hierarchical data, such as organizational charts for units, staff, biological
classification, etc?
For example:
UnitID UnitName ParentUnitID HasChildren
------ --------------- ------------ -----------
000000 Global NULL||000000 Y -- Either NULL or
UnitID=ParentUnitID would serve to indicate root node
110000 Europe 000000 Y
111000 Great Britain 110000 Y
111100 York 111000 N
120000 Germany 110000 Y
121000 Hesse 120000 Y
121100 Darmstadt 121000 N
122000 Bavaria 120000 Y
122100 Würzburg 122000 N
122200 München 122000 Y
122210 Bierhalle 122200 Y
122211 Augustiner 122210 N
122212 Englische 122210 N
200000 North America 000000 Y
220000 USA 200000 Y
221000 Tennessee 220000 Y
221100 Memphis 221000 Y
221110 Sports Arenas 221100 Y
221111 Autozone Park 221110 N
221112 Fedex Forum 221110 N
(NOTE: There is no implicit||explicit embedded intelligence in the ID values;
that would be a different can of worms.)
Given the above (rather arbitrary) hierarchy, I would like to be able to gather
data, including aggregating functions (COUNT, SUM, etc) starting at any
level/node and look either up or down from that starting point. Using the
example, I might want to know how many children roll-up to Bavaria or, from
Memphis, how many sports arenas are in the data. Of course, the real
usefulness would occur when I also used data associated with, say Bierhalle, as
in how many gallons (well, liters) of beer were sold or with Memphis, Sports
Arenas, how many seats are available.
I've been reading some Celko about this and even he - whether you
hate/love/don't care about Celko - says that his solution, sans
special/proprietary hierarchical operators, is functional but inelegant and not
terribly efficient. It also appears that some DBMS vendors have implemented
special hierarchical operators, such as WITH (DB2), CONNECT (Oracle), etc.
Having played with the DB Treeview control yesterday and having quickly mapped our own
organization table (in)to it, I thought, "This is really cool." Now, however,
I'm looking for a way to select a node and walk up/down from that point, grabbing and/or
aggregating data associated with the node and direction in order to display it on the
same form. This isn't yet an enhancement request, but given RBTI's implementation of
this control, it seems an obvious next step. I also suspect that it would take Razzak
and RTeam little time to do this in a fashion compliant with current SQL standards. I
also have no doubt that at least one among us would be EVER SO GRATEFUL ...
Thanks,
Steve in Memphis
J. Stephen Wills
Program Manager, Research Informatics
Office of the Vice Chancellor for Research
University of Tennessee Health Science Center
62 S. Dunlap, Suite 400
Memphis, TN 38163
Office: 901-448-2389
FAX : 901-448-7133
