SQL - How to store and navigate hierarchies?

sql sql-server oracle database-design hierarchy
realcals picture realcals · Sep 2, 2008 · Viewed 25.8k times · Source

What are the ways that you use to model and retrieve hierarchical info in a database?

Answer

markus picture markus · Sep 19, 2008

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

Related questions

How do I list all the columns in a table?

For the various popular database systems, how do you list all the columns in a table?

Read More
Is there a combination of "LIKE" and "IN" in SQL?

In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question. Further, I often use conditions like WHERE something …

Read More
Can a foreign key be NULL and/or duplicate?

Please clarify two things for me: Can a Foreign key be NULL? Can a Foreign key be duplicate? As fair as I know, NULL shouldn't be used in foreign keys, but in some application of mine I'm able to input …

Read More