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?

score 4 · Answer 1 · answered Jul 15 '09 at 05:56

4

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

answered Jul 15 '09 at 05:56

Arsen Mkrtchyan

48,710
31
147
181

4

Exercise: Write a query that returns all decedents from node1. – Tom Lokhorst Jul 15 '09 at 06:03
you mean this? select * From tab where parentid = (select id from table where name = 'node1') – Arsen Mkrtchyan Jul 15 '09 at 06:07
That's hilarious :-) However, you probably should have pointed out a better alternative, like using nested sets model. – ChssPly76 Jul 15 '09 at 06:08
4

To **Arsenmkrt** - no, that would be direct descendants. You were asked for all descendants :) – ChssPly76 Jul 15 '09 at 06:09
@ChssPly76 well, that's easy no? select * from tab where name != 'node1' ;) but seriously how does this answer have 3 upvotes? – user3012759 Nov 30 '16 at 10:51

score 4 · Answer 2 · edited May 23 '17 at 11:53

4

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.

edited May 23 '17 at 11:53

Community

1
1

answered Jul 15 '09 at 06:18

ChssPly76

97,371
24
203
192

+1 I didn't think about that before ;) – Arsen Mkrtchyan Jul 15 '09 at 06:23

score 3 · Accepted Answer · edited May 23 '17 at 12:33

3

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

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

edited May 23 '17 at 12:33

Community

1
1

answered Jul 15 '09 at 06:07

this. __curious_geek

41,911
22
111
137

how to design database for binary tree?

3 Answers3