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 ite开发者_如何学Pythonm)?

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