4

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?

blue-dino
  • 383
  • 2
  • 3
  • 11

1 Answers1

1

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.

Michael Green
  • 158
  • 1
  • 7