There's the "put a FK for your parent" method, i.e. each records indicates it's parent.
The industry hard for read actions but super easy to keep.

After which there's a "directory structure key" method:

0001.0000.0000.0000 primary branch 1

0001.0001.0000.0000 child of primary branch one


That is super readable, but tough to maintain.
What's the golden way?

Of course: There's now best answer. Each solution makes different thinks simpler or harder. The best solution for you personally is dependent which operation you is going to do most.

Naive Approach with parent-id:


  • simple to apply

  • simple to move a large subtree to a different parent

  • place is affordable

  • Needed Fields directly available in SQL


  • locating an entire tree is recursive and for that reason costly

  • finding all parents is costly too ( SQL does not know recursions... )

Modified Preorder Tree Traversal ( saving a start- &lifier finish-point) :


  • Locating an entire tree is simple and cheap

  • Finding all parents is affordable

  • Needed Fields directly available in SQL

  • Bonus: you are saving an order of childnodes within its parentnode too


  • Placing / Upgrading can be quite costly, as you'll maybe need to update lots of nodes

Saving a path in each Node:


  • Finding all parents is affordable

  • Retreaving an entire tree is affordable

  • Placing is affordable


  • Moving an entire tree is costly

  • With respect to the way it will save you the road, you will not have the ability to use it directly in SQL, so you'll always have to fetch &lifier parse it, if you wish to change it out.

I'd prefer among the latter, based on who frequently the information changes.

See also: titles/bksqla/trees.pdf

Modified Preorder Tree Traversal

Section 2 from the Sitepoint article Storing Hierarchical Data inside a Database

alt text

On first blush, tree structures and RDBMs really are a horrible fit. If I have seen a use situation for Structured Storage, here it is.

I'd the "golden way" to keep a hierarchical data structure is by using a hierarchical database. For example, for example, HDB. This is a relational database that handles trees very well. If you would like some thing effective, LDAP might do for you personally.

A SQL database is ill-suitable for this abstract topology.

We're while using old-age approach to mentioning towards the parent using FK for storing hierarchical data and therefore are almost happy. To load plenty of data, we make an XML query and deserialise to things.