How to store directory / hierarchy / tree structure in the database?

Question

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!

Answer 1

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

Answer 2

Are you using Ms SQL 2005? Recursive queries in 2005 makes queryring 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.

Answer 3

The typical way is a table with a foreign key (e.g. "ParentId") onto itself.

Answer 4

There also is the Nested-Set Model of Trees which has some advantages over the ParentID model. See http://www.dbmsmag.com/9603d06.html

Answer 5

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.

Answer 6

If using Sql Server 2008 is an option: maybe you should check out new hierarchyid data type.