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
etc
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:
Pros:
simple to apply
simple to move a large subtree to a different parent
place is affordable
Needed Fields directly available in SQL
Cons:
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) :
Pros:
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
Cons:
- Placing / Upgrading can be quite costly, as you'll maybe need to update lots of nodes
Saving a path in each Node:
Pros:
Finding all parents is affordable
Retreaving an entire tree is affordable
Placing is affordable
Cons:
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: http://media.pragprog.com/game titles/bksqla/trees.pdf
Modified Preorder Tree Traversal
Section 2 from the Sitepoint article Storing Hierarchical Data inside a Database
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.