title: Database Modeling: Product with 1 or more categories
body: <p>I'm working on a database design that allows for a product to be in 1 or more categories. A category can be in a parent-child relationship. Here's what I have so far:</p>
  
  <pre><code>==================
  product
  ==================
  product_id
  name
  
  ==================
  category
  ==================
  category_id
  parent_category_id
  level
  name
  
  ==================
  product_category
  ==================
  product_id
  category_id (leaf node only)
  </code></pre>
  
  <p><strong>Questions</strong></p>
  
  <ol>
  <li>Does the design look good?</li>
  <li>Am I right to only be concerned with the leaf node in product_category->category_id? (I suppose I can work my way from the leaf node up to the root node with this information)</li>
  <li>Given a product, how would I get the category tree(s) that product belongs to?</li>
  <li>Given a category (any level in the category tree), how would I get the number of products categorized under it?</li>
  <li>Any other queries I need to look into?</li>
  </ol>