title: database design - categories and sub-categories
body: <p>I need to implement Categorization and Sub-Categorization on something which is a bit similar to golden pages.</p>
  
  <p>Assume I have the following table:</p>
  
  <h1>Category Table</h1>
  
  <pre><code>CategoryId, Title
  10, Home
  20, Business
  30, Hobbies
  </code></pre>
  
  <p>I have two options to code the sub-categorization.</p>
  
  <h1>OPTION 1 - Subcategory Id is unique within Category ONLY:</h1>
  
  <h1>Sub Category Table</h1>
  
  <pre><code>CategoryId, SubCategoryId, Title
  10, 100, Gardening
  10, 110, Kitchen
  10, 120, ...
  20, 100, Development
  20, 110, Marketing
  20, 120, ...
  30, 100, Soccer
  30, 110, Reading
  30, 120, ...
  </code></pre>
  
  <h1>OPTION 2 - Subcategory Id is unique OVERALL:</h1>
  
  <h1>Sub Category Table</h1>
  
  <pre><code>CategoryId, SubCategoryId, Title
  10, 100, Gardening
  10, 110, Kitchen
  10, 120, ...
  20, 130, Development
  20, 140, Marketing
  20, 150, ...
  30, 160, Soccer
  30, 170, Reading
  30, 180, ...
  </code></pre>
  
  <p>Option 2 sounds like it is easier to fetch rows from table
  For example: <code>SELECT BizTitle FROM tblBiz WHERE SubCatId = 170</code></p>
  
  <p>whereas using Option 1 I'd have to write something like this:</p>
  
  <pre><code>SELECT BizTitle FROM tblBiz WHERE CatId = 30 AND SubCatId = 170
  </code></pre>
  
  <p>i.e., containing an extra <code>AND</code></p>
  
  <p>However, Option 1 is easier to maintain manually (when I need to update and insert new subcategories etc. and it is more pleasant to the eye in my opinion.</p>
  
  <p>Any thoughts about it? Does Option 2 worth the trouble in terms of efficiency? Is there any design patters related with this common issue? </p>