Menu
What are the ways that you use to model and retrieve hierarchical info in a database?
952
Document based database like MongoDB, and Redis are great for small scale, hierarchical data with a relatively small amount of children for each entry.
A database evaluates hierarchical queries in the following order: A join, if present, is evaluated first, whether the join is specified in the FROM clause or with WHERE clause predicates. The CONNECT BY condition is evaluated. Any remaining WHERE clause predicates are evaluated.
A hierarchical query is a type of SQL query that handles hierarchical model data. They are special cases of more general recursive fixpoint queries, which compute transitive closures. In standard SQL:1999 hierarchical queries are implemented by way of recursive common table expressions (CTEs).
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 :
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
160
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
