There is the "put a FK to your parent" method, 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" method:
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 are the other ways and their cons/pros?
As always: there is no best solution. Each solution makes different things 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
Retrieving 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.
Closure table
Pros:
Easy to implement
Finding all parents is cheap
Inserting is cheap
Retrieving whole trees is cheap
Cons:
Needs an additional table
Takes up a lot of space compared to other approaches
Moving a subtree is expensive