title: Twitter-like database table structure
body: <p>I am trying to create a database that can be used like Twitter works. That is:</p>
  
  <ol>
  <li>Treestructure: Any node can have multiple childnodes.</li>
  <li>All nodes have a timestamp</li>
  </ol>
  
  <p>Criteria 1 and 2 suggests a table structure based on basic columns something like:</p>
  
  <pre><code>NodeID        (int)
  ParentNodeID  (int)
  UserID        (int)
  TS            (TimeStamp)
  MSG           (varchar)
  </code></pre>
  
  <p>When viewing any node (<strong>n</strong>) all parent nodes until and including root should be selected, that is easy using the <strong>ParentNodeID</strong> pointer. </p>
  
  <p><strong>Here comes the caveat:</strong> In addition to the parent nodes all child nodes from the current node (<strong>n</strong>) should also be selected in Chronological order (based on TS) from the table. All child nodes, no matter what child-branch, that belongs to the subtree where (<strong>n</strong>) is the root.</p>
  
  <p>How do I best (better) structure the table for such queries?  </p>