tree - Represent a product taxonomy in database -

i have represent complex product taxonomy having parent , child relationship in database. can guide me on doing this??

for e.g. root may 'all product' may @ second level have 'computers' , 'furniture' tree??

here simple tree example using sql server 2005:

--go through nested table supervisor - user table , display chain declare @contacts table (id varchar(6), first_name varchar(10), reports_to_id varchar(6)) insert @contacts values ('1','jerome', null )  -- tree follows: insert @contacts values ('2','joe'   ,'1')     --                      1-jerome insert @contacts values ('3','paul'  ,'2')     --                     /        \ insert @contacts values ('4','jack'  ,'3')     --              2-joe           9-bill insert @contacts values ('5','daniel','3')     --            /       \              \ insert @contacts values ('6','david' ,'2')     --     3-paul          6-david       10-sam insert @contacts values ('7','ian'   ,'6')     --    /      \            /    \ insert @contacts values ('8','helen' ,'6')     -- 4-jack  5-daniel   7-ian    8-helen insert @contacts values ('9','bill ' ,'1')     -- insert @contacts values ('10','sam'  ,'9')     --  declare @root_id  char(4)  --get 2 , below set @root_id=2 print '@root_id='+coalesce(''''+@root_id+'''','null') ;with stafftree (     select          c.id, c.first_name, c.reports_to_id, c.reports_to_id manager_id, cc.first_name manager_first_name, 1 levelof         @contacts                  c             left outer join @contacts  cc on c.reports_to_id=cc.id         c.id=@root_id or (@root_id null , c.reports_to_id null)     union         select              s.id, s.first_name, s.reports_to_id, t.id, t.first_name, t.levelof+1         stafftree            t             inner join @contacts  s on t.id=s.reports_to_id     s.reports_to_id=@root_id or @root_id null or t.levelof>1 ) select * stafftree 

output:

@root_id='2   ' id     first_name reports_to_id manager_id manager_first_name levelof ------ ---------- ------------- ---------- ------------------ ----------- 2      joe        1             1          jerome             1 3      paul       2             2          joe                2 6      david      2             2          joe                2 7      ian        6             6          david              3 8      helen      6             6          david              3 4      jack       3             3          paul               3 5      daniel     3             3          paul               3  (7 row(s) affected) 

change @root_id different output:

@root_id=null id     first_name reports_to_id manager_id manager_first_name levelof ------ ---------- ------------- ---------- ------------------ ----------- 1      jerome     null          null       null               1 2      joe        1             1          jerome             2 9      bill       1             1          jerome             2 10     sam        9             9          bill               3 3      paul       2             2          joe                3 6      david      2             2          joe                3 7      ian        6             6          david              4 8      helen      6             6          david              4 4      jack       3             3          paul               4 5      daniel     3             3          paul               4  (10 row(s) affected)