Query for updating a node in hierarchial tree structure advice speeddating


Hierarchies are sometimes difficult to store in SQL tables...things like trees, threaded forums, org charts and the like..it's usually even harder to retrieve the hierarchy once you do store it.Here's a method that's easy to understand and maintain, and gives you the full hierarchy (or any piece of it) very quickly and easily.You can find a person's boss easily, and you can find their coworkers by querying the Boss ID column. It would be great if it could join itself as many times as needed.The trouble begins when you want to list several levels of a hierarchy. Name Employee FROM Employees INNER JOIN Employees AS Boss ON Employees. This is called a recursive join, and though some database products support it (Oracle has the CONNECT BY syntax) SQL Server is not one of them.

For our purposes, hierarchical data is a collection of data where each item has a single parent and zero or more children (with the exception of the root item, which has no parent).

The upside is, it's easy and it's normalized: the data means exactly what it says and there's no duplication.

The downside is, you can't really retrieve an entire from the database, without doing lots of SQL queries.

Hierarchies may be expressed easily by switching to a graph database.

Alternatively, several resolutions exist for the relational model and are available as a workaround in some relational database management systems: When these solutions are not available or not feasible, another approach must be taken.

So, in essence, these approaches trade lots of queries at retrieval time, for lots of queries at update and insert/delete time, and often some additional programming complexity.

You must have an account to comment. Please register or login here!