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?