Lee,

> 1. Would anyone care to provide a simple example or pseudo
> code showing the basic logic of such queries? For instance,
> off the top of my head I would probably need a query that
> tested if a particular tournament's OID was already present
> in it's parent's tournaments hirearchy, etc so that I don't
> add a tournament farther from root using a tournament that
> was created closer to root.

I think you could use the new CTE (Common Table Expressions) syntax and do this in a single SQL statement, but I don't have much experience with them.

So, I will suggest the old/tried-and-true recursive stored procedure approach:


CREATE PROCEDURE GET_CHILD_Tourneys( Parent_TourneyID)
RETURNS (CHILD_TourneyID)
AS
BEGIN
FOR
SELECT OID
FROM Tourneys
WHERE ParentOID = :Parent_TourneyID
INTO
:CHILD_TourneyID
DO
BEGIN
-- Return this Tourney
Suspend;

-- Get the child of this Tourney
FOR
SELECT CHILD_TourneyID
FROM GET_CHILD_Tourneys( :CHILD_TourneyID)
INTO
:CHILD_TourneyID
DO
BEGIN

Suspend;
END

END
END

> 2. What about performance with queries like these? I imagine
> that a recursive Store Procedure could be hard put to handle
> very complex trees but I can't imagine going more than 5
> levels deep at any node but I guess that could be a lot as
> well if some nodes hold many peer nodes.

The performance should be fine regardless of the number of levels


Sean