How do i store a directory / hierarchy / tree structure in the database? Namely MSSQL Server.
@olavk: Doesn't look like you've seen my own answer. The way i use is way better than recursive queries :)
p.p.s. This is the way to go!
8 Answers
There are many ways to store hierarchies in SQL databases. Which one to choose depends on which DBMS product you use, and how the data will be used. As you have used the MSSQL2005 tag, I think you should start considering the "Adjacency List" model; if you find that it doesn't perform well for your application, then have a look at Vadim Tropashko's comparison which highlights differences between models with a focus on multiple performance characteristics.
There also is the Nested-Set Model of Trees which has some advantages over the ParentID model. See http://www.evanpetersen.com/item/nested-sets.html and http://falsinsoft.blogspot.nl/2013/01/tree-in-sql-database-nested-set-model.html
This is more of a bookmark for me than a question, but it might help you too. I've used this article's approach to store a directory / tree structure in the database.
There are some useful code snippets in the article as well.
Hope this helps.
I'm not affiliated with that website in any way
Are you using SQL Server 2005? Recursive queries make querying hierarchical data much more elegant.
Edit: I do think materialized paths are a bit of a hack. The path contain non-normalized redundant data, and you have to use triggers or something to keep them updated. Eg. if a node changes parent, the whole subtree have to have their paths updated. And subtree queries have to use some ugly substring matching rather than an elegant and fast join.
Related