Menu
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!
900
The standard method of storing hierarchical data is simple parent-child relationship. Each record in the database includes a —parent id—, and a recursive query through the records build the children, siblings, and levels of the tree.
Graph database are ideal for complex hierarchical data.
Database type Unless you're aiming at huge amounts of data I suggest you go with any of the SQL databases that you know best (MSSQL, MySQL, Oracle). But if your database will contain enormous number of hierarchy nodes then flirting with a specialised graph-oriented database may be a better option.
Select the data, adding a column that converts the Level data into a text value that is easy to understand. This query also orders the result by the hierarchyid data type. SELECT CAST(Level AS nvarchar(100)) AS [Converted Level], * FROM SimpleDemo ORDER BY Level; Here is the result set.
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.
85
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
