title: Best database data structure for a many to many tree, based on item availability
body: <p>I'm currently developping a Website based on MySQL, but switching to another database is not a problem if necessary (like for CTE or so).</p>
  
  <p>I'm looking for the best database data structure (and if possible some SQL snippets) to handle my current project, which is as follow:</p>
  
  <ul>
  <li>A product can be made of other products (recursively), so products have to be designed in a tree-oriented architecture.</li>
  <li>A product may be used for the fabrication of many products. That is where something like the Nested Set architecture won't work, because a node can be a children and a parent of many nodes, which does not seem possible using NestedSet.</li>
  <li>A product has a quantity available for each day, but only if it is a "leaf" (it is not made from another product). Otherwise its quantity will depend on the quantity of its direct children (and so on, until the recursion reaches the leaves). So products may be linked to a table "availability", which contains "product_id", "date" and "quantity". </li>
  </ul>
  
  <p>The "tree" structure of the products is quite stable, so the speed of Update/Insert/Delete queries does not really matters.</p>
  
  <p>The final goal is to be able to retrieve in a SELECT query as fast as possible, the list of all the products that are available between two dates specified.</p>
  
  <p>This means that:</p>
  
  <ul>
  <li>1) If the product is not made of another one, it is available if quantity > 0 for each day between the two dates.</li>
  <li>2) If the product is made of other ones, it is avalailable only if (1) is true for all its children. If the direct children are not "leaf", they will not have quantity, so a recursion for (2) will happened until it reaches the final leaves.</li>
  </ul>
  
  <p>By the way, in a real case scenario, my products will never have more than 5 level of depth. Maybe it would be a good (but very ugly) idea to store all the parent ID's in separate columns.</p>