There is the:
, i.e. each records points to it's parent Which is a hard for read actions but very easy to maintain
And then there is a directory structure key
0001.0000.0000.0000 main branch 1
0001.0001.0000.0000 child of main branch one
etc
Which is super easy to read, but hard to maintain.
What is the golden way?
I'd say the "golden way" to store a hierarchical data structure is to use a hierarchical database. Such as, for instance, HDB. That's a relational database that handles trees quite well. If you want something more powerful, LDAP might do for you.
A SQL database is ill-suited to this abstract topology.
We are using the old-age method of referring to the parent using FK for storing hierarchical data and are almost happy. To load lots of data, we make an XML query and deserialise to objects.
On first blush, tree structures and RDBMs are a horrible fit. If I've ever seen a use case for Structured Storage, this is it.
I dont think it s hard to build a tree structure with a relational database.
However, an object oriented database would work much better for this purpose.
Using an object oriented database; parent has a set of child1 child1 has a set of child2 child2 has a set of child3 ... ...
In an object oriented database you can build this structure pretty easy.
In relational database, you will have to maintain foreign keys to parent.
parent
id
name
child1
parent_fk
id
name
child2
parent_fk
id
name
..
essentially, while you are building your tree structure you will have to join all these tables, or you can iterate them.
foreach(parent in parents){
foreach(child1 in parent.child1s)
foreach(child2 in child1.child2s)
...
Modified Preorder Tree Traversal
Section 2 of the Sitepoint article Storing Hierarchical Data in a Database
As always: There is now best solution. Each solution makes different thinks easier or harder. The right solution for you depends on which operation you will do most.
Naive Approach with parent-id:
Pros:
easy to implement
easy to move a big subtree to another parent
insert is cheap
Needed Fields directly accessible in SQL
Cons:
retrieving a whole tree is recursive and therefore expensive
finding all parents is expensive too ( SQL doesn't know recursions... )
Modified Preorder Tree Traversal ( saving a start- & end-point) :
Pros:
Retrieving a whole tree is easy and cheap
Finding all parents is cheap
Needed Fields directly accessible in SQL
Bonus: you're saving the order of childnodes within its parentnode too
Cons:
Saving a path in each Node:
Pros:
Finding all parents is cheap
Retreaving a whole tree is cheap
Inserting is cheap
Cons:
Moving a whole tree is expensive
Depending on the way you save the path, you won't be able to work with it directly in SQL, so you'll always need to fetch & parse it, if you want to change it.
I'd prefer one of the last two, depending on who often the data changes.