Storing website hierarchy in Sql Server 2008
- by Mika Kolari
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?