binary tree mapping database query

Question

I got to develop a user chain as shown in the figure. At level zero it has one member, level one two, level two will have 4, level 3 has 8 members..like wise 9th level will have 512 members and it is final stage.

In the programmatic view, to interrelate a member's position with his right & left wing to a level of x, running 2^x queries is pathetic. (for 9th level, 2^9 queries will hit db)

Is there any other way to reduce no of hits to db? How can we better map the relation between the members?

Answer 1

construct a tree table and zone table. Tree table will contain parent, child, side as columns. Zone table contains userid, zone (into which all zones it should be displayed, 9 in this case) and length (length from the top most parent node) as columns. so in zone table 1st level node will have one entry, 2nd row node will get two entry, similarly 9th will have nine entries.

Now when a node has to be displayed, to retrieve all its below nodes, write a query like select * from zone where userid=xx now map this result with tree table to decide which node comes under which node and which side.

Answer 2

An excellent titorial for Storing Hierarchical Data in a Database.

Also, HERE is a similar question, with a few solid solutions.