title: MySQL - Best method to handle this hierarchical data?
body: <p>This is a followup to:<br>
  <a href="https://stackoverflow.com/questions/3073614/mysql-is-it-possible-to-get-all-sub-items-in-a-hierarchy">MySQL - Is it possible to get all sub-items in a hierarchy?</a></p>
  
  <p>I have an arbitrary-depth <strong>adjacency list model</strong> table (I am at the point that I <em>can</em> convert it into a <strong>nested set model</strong>.</p>
  
  <p>I read the MySQL data on how to use a nested set model, though it seemed to get increasingly complex and very complex to do basic functions such as inserting, updating and deleting.</p>
  
  <p>Another blog showing how to use a trigger system with the adjacency list model to keep a table of ancestors that relates each object to its ancestors.</p>
  
  <hr>
  
  <p>Right now I need to be able to return a list of all children of a given node, to change or delete them. This hierarchical structure won't be changing all the time once created, but there will be a mass amount of the hierarchical structures.</p>
  
  <p>The three methods I see are:</p>
  
  <ol>
  <li><p><strong>Created a Stored Procedure</strong> which would do a recursive query that returns all children.</p></li>
  <li><p><strong>Convert to Nested Set Model</strong> which would require to get into the complexities and possibly create a stored procedure to add, edit and delete in that.</p></li>
  <li><p><strong>Create the Ancestor Table</strong> described above on insert/delete triggers to handle all of the data.</p></li>
  </ol>
  
  <p>If there are other methods I'm not exploring, please let me know and I'll update this list.</p>