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

sql-server sql-server-2005 database-design tree-structure
roman m picture roman m · Sep 27, 2008 · Viewed 52.6k times · Source

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

Troels Arvin picture Troels Arvin · Sep 27, 2008

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.

Related questions

"Prevent saving changes that require the table to be re-created" negative effects

Preamble I was modifying a column in SQL Server 2008 today, changing the datatype from something like currency(18,0) to (19,2). I got the error "The changes you have made require the following tables to be dropped and re-created" from SQL Server. Before …

Read More
Date range overlapping check constraint

I've a simple table in sql server 2005 with 3 columns: DateStart, DateEnd and Value. I tried to set a "table check constraint" to avoid inserting overlapping records. For instance if in such table there is a record with DateStart = 2012-01-01 (…

Read More
Extra spaces being added at the tail in the column

When I am saving data into a table, extra spaces being added to the valued at the tail. I observed that as the column length is 5, if I am inserting a value of 3 char length, 2 extra spaces are being added. …

Read More