Tutorial :Storing website hierarchy in Sql Server 2008



Question:

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?


Solution:1

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
Previous
Next Post »