SQL – How to store and navigate hierarchies?

by

I like the Modified Preorder Tree Traversal Algorithm. This technique makes it very easy to query the tree.

But here is a list of links about the topic which I copied from the Zend Framework (PHP) contributors webpage (posted there by Posted by Laurent Melmoux at Jun 05, 2007 15:52).

Many of the links are language agnostic:

There is 2 main representations and algorithms to represent hierarchical structures with databases :

  • nested set also known as modified preorder tree traversal algorithm
  • adjacency list model

It’s well explained here:

Here are some more links that I’ve collected:

adjacency list model

nested set

Graphes

Classes :

Nested Sets DB Tree Adodb

Visitation Model ADOdb

PEAR::DB_NestedSet

PEAR::Tree

nstrees

More Related Contents:

  1. how to create this table in sql server?
  2. Is there a combination of “LIKE” and “IN” in SQL?
  3. What is the purpose of system table master..spt_values and what are the meanings of its values?
  4. How to create multiple one to one’s
  5. Can a foreign key be NULL and/or duplicate?
  6. Multiplication aggregate operator in SQL
  7. Stored Procedure with optional “WHERE” parameters
  8. Why (and how) to split column using master..spt_values?
  9. Should I design a table with a primary key of varchar or int?
  10. Composite Primary key vs additional “ID” column?
  11. Deferrable Constraints in SQL Server
  12. Benefits Of Using SQL Ordinal Position Notation?
  13. Can I set ignore_dup_key on for a primary key?
  14. Should I use SQL_Variant data type?
  15. How do I specify date literal when writing SQL query from SQL Server that is linked to Oracle?
  16. Unordered results in SQL
  17. update columns values with column of another table based on condition [duplicate]
  18. What does ON [PRIMARY] mean?
  19. How do I list all the columns in a table?
  20. Why & When should I use SPARSE COLUMN? (SQL SERVER 2008)
  21. Is there a severe performance hit for using Foreign Keys in SQL Server?
  22. What’s the correct name for an “association table” (a many-to-many relationship) [closed]
  23. Backup SQL Schema Only?
  24. Connecting to Oracle Database using Sql Server Integration Services
  25. Single or multiple databases
  26. can we have a foreign key which is not a primary key in any other table?
  27. Is there an Oracle equivalent to SQL Server’s OUTPUT INSERTED.*?
  28. Simple way to transpose columns and rows in SQL?
  29. GROUP BY with MAX(DATE) [duplicate]
  30. Update with Join query in Oracle

Leave a Comment