4
$\begingroup$

I have a table representing posts on a message board. Posts may or may not have parents.

What is the most common way to get all posts starting from a given post, or to find the root post of any given post?

I can think of using JOINs to join parents and children, but how can I know how many joins I need before I stop?

$\endgroup$
2
  • 2
    $\begingroup$ Can you share the table structure? What database are you using? Have you looked into recursive CTEs (common table expressions)? $\endgroup$ Commented Jul 27, 2015 at 10:44
  • $\begingroup$ You should post this on DBA or StackExchange. $\endgroup$
    – paparazzo
    Commented Nov 17, 2015 at 17:36

1 Answer 1

1
$\begingroup$

There are several models for holding hierarchical data in a relational database. There's a good summary here. In summary they are

  • Adjacency list: each row contains a foreign key to its parent. Fast for structure changes. Requires recursive processing to find the base ancestor.
  • Path enumeration: each node stores its ancestry. An update requires all children to be updated. For a known node reaching the root requires no further work.
  • Nested sets: additional columns hold the ancestry. The cost of structure changes depends on how big the moving subtree is. The root node is fast to find.
  • Closure table: store all ancestor-descendant pairs. Structure changes can be expensive. Root node expensive to identify.

From the given information a path enumeration sounds like your best option.

$\endgroup$
1
  • $\begingroup$ +1 Thanks for the link. I have been using adjacency list but did not know all the technical names of the others. $\endgroup$
    – Edmund
    Commented Nov 20, 2015 at 0:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.