Tutorial :Storing website hierarchy in Sql Server 2008


I want to store website page hierarchy in a table.

What I would like to achieve is efficiently
1) resolve (last valid) item by path (e.g. "/blogs/programming/tags/asp.net,sql-server", "/blogs/programming/hello-world" )
2) get ancestor items for breadcrump
3) edit an item without updating the whole tree of children, grand children etc.

Because of the 3rd point I thought the table could be like

ITEM  id    type        slug           title               parentId  1     area        blogs          Blogs  2     blog        programming    Programming blog    1  3     tagsearch   tags                               2  4     post        hello-world    Hello World!        2  

Could I use Sql Server's hierarchyid type somehow (especially point 1, "/blogs/programming/tags" is the last valid item)?
Tree depth would usually be around 3-4.

What would be the best way to achieve all this?


The way you have done this seems fine, you can make use of CTE recursive functions to create the hierarchy for you

Something like

DECLARE @ITEM  TABLE(          id INT,          type VARCHAR(20),          slug VARCHAR(50),          title VARCHAR(50),          parentId  INT  )    INSERT INTO @ITEM SELECT 1,'area','blogs','Blogs', NULL  INSERT INTO @ITEM SELECT 2,'blog','programming','Programming blog',1   INSERT INTO @ITEM SELECT 3,'tagsearch','tags',',',2   INSERT INTO @ITEM SELECT 4,'post','hello-world','Hello World!',2     ;WITH Items AS (          SELECT  *,                  CAST('/' + slug + '/' AS VARCHAR(50)) PathVal          FROM    @ITEM          WHERE   parentId IS NULL          UNION ALL          SELECT  i.*,                  CAST(Items.PathVal + i.slug + '/' AS VARCHAR(50))          FROM    Items INNER JOIN                  @ITEM i ON i.parentId = Items.ID  )    SELECT  *  FROM    Items  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »