how to design database for binary tree?

Question

I'm doing a project in the field of multilevel marketing on .Net and SQL server. In the database it should save like a binary tree. How should i design a database?

Solution 1

SQL Server 2008 has a built-in data-type called hierarchyid to store hierarchical information. Here are some pointers.

• http://blogs.msdn.com/manisblog/archive/2007/08/17/sql-server-2008-hierarchyid.aspx

• http://technet.microsoft.com/en-us/library/bb677290.aspx

And of course you can do this as mentioned by arsenmkrt in databases other than sqlserver2008.

Solution 2

id | parentid | name
---------------------
 1 | null     | node1
 2 | 1        | node2
 3 | 1        | node3

Solution 3

This has been asked and answered before.

Here's a pretty decent tutorial which explains why adjacency model proposed by arsenmkrt is less than ideal.

Comments

[Dave]

Exercise: Write a query that returns all decedents from node1.

[Arsen Mkrtchyan]

you mean this? select * From tab where parentid = (select id from table where name = 'node1')

[ChssPly76]

That's hilarious :-) However, you probably should have pointed out a better alternative, like using nested sets model.

[Arsen Mkrtchyan]

+1 I didn't think about that before ;)

[user3012759]

@ChssPly76 well, that's easy no? select * from tab where name != 'node1' ;) but seriously how does this answer have 3 upvotes?