Simplist is an adjacency list. Store the parent ID along with each record,
also store the "full path" in a text string, such as 0001/0004/0007/0002
(the second node of the seventh node of the 4th node of the 1st node of the
tree). I use a function to generate a number for the string (below)
because each "path node" has to be the same length in order to perform a
correct "select" of the tree order. You also need a "sibling number" if you
want to order your nodes in an arbitrary way.
ALTER FUNCTION dbo.func_Get_Pa dded_Number8
(
@number INTEGER
)
RETURNS VARCHAR(8)
AS
BEGIN
DECLARE @Value VARCHAR(8)
DECLARE @Length INTEGER
/*
Convert the number and get its string length
*/
SET @Value = CONVERT ( VARCHAR ( 8 ), @number )
SET @Length = LEN ( @Value )
/*
If the length is less than 8, pad it
*/
IF LEN ( @Value ) < 8
BEGIN
SET @Value = REPLACE ( SPACE ( 8 - @Length ), ' ', '0' ) + @Value
END
RETURN @Value
END
Adding a new node is simple given the parent, you just find the highest
sibling number of the parent and increment it then insert a node, building
the path string by taking the parent and adding "/00n" (where n is the
sibling number). Listing all of the nodes in the tree structure can be done
easily like this:
SELECT dbo.Adjacency.I D, (a unique ID)
dbo.Adjacency.I D_Parent, (the unique ID of the parent of
this node)
dbo.Adjacency.I D_Index, (the sibling number of this child)
LEN(dbo.Adjacen cy.Path) / 9 AS Depth, (the depth of the
node, useful for building your tree structure afterwards)
FROM dbo.Adjacency
ORDER BY dbo.Adjacency.P ath
Hope this helps some.
<im************ *******@yahoo.c om> wrote in message
news:8b******** *************** *@posting.googl e.com...
We need to present hierarchical data on a web page, the same way the
tree view shows files in Windows Explorer. Here's the catch: that
tree view needs to be bound to a SQL Server database. How can this be
done?
