I have to store blog style comments in a database. Each comment can have a parent one.
I am doing it with a column called "parentComment" that has a null value when it is a top level comment or have a value if it is a response to another comment.
What is the most efficient way to retrieve a comment and all its childs?
A quite common technique is to also have a (duplicate, indirectly) relation to the "root" of the tree, which means you can select the full tree in one neat select. Othewise it quickly gets dirty.