title: Database design for products with multiple categories and multiple sub-features
body: <p>Suppose you are designing a searchable database for a webstore, and have a situation like the following:</p>
  
  <hr>
  
  <p>Each <code>product</code> will have multiple, different <code>categories</code>.</p>
  
  <p>For example, consider a MARKER as a <code>product</code>.</p>
  
  <p>E.g.: The same MARKER might have as its <code>category</code> all three of the following:</p>
  
  <ul>
  <li>TOY</li>
  <li>SCHOOL-STATIONERY</li>
  <li>ART-ACCESSORY</li>
  </ul>
  
  <p>Now, each <code>category</code> has a corresponding set of any number of parameters (let's call them <code>features</code>) that apply only to that category.</p>
  
  <p>Thus, for each of its three <code>product-category</code> representations, the MARKER above will have multiple, different <code>product-feature-names</code> and respective <code>product-feature-values</code>.</p>
  
  <p>E.g.: We might have these category-based <code>feature-names</code> and product-specific <code>feature-values</code>:</p>
  
  <ul>
  <li>PRODUCT-NAME::: CATEGORY-NAME::: <strong>Feature1-name</strong> = <em>Feature1-value</em>, etc.</li>
  <li>MARKER::: TOY::: <strong>AgeGroup</strong> = <em>6-12</em>, <strong>Toxic</strong> = <em>No</em>, <strong>ChristmasSpecial</strong> = <em>Yes</em></li>
  <li>MARKER::: SCHOOL-STATIONERY::: <strong>BulkAvailability</strong> = <em>No</em></li>
  <li>MARKER::: ART-ACCESSORY::: <strong>Use</strong> = <em>Fine-drawing</em>, <strong>CompatibleSurface</strong> = <em>All</em></li>
  </ul>
  
  <hr>
  
  <p><strong>What would be the best / most-optimal design for this type of situation?</strong></p>
  
  <hr>
  
  <p>My thought is to use three tables, but I don't know if this is the most efficient for retrieving data later (perhaps this should be done in two tables, or even just one?):</p>
  
  <blockquote>
    <p><em>PRODUCT-TABLE</em></p>
    
    <p>id, product_name</p>
    
    <p><em>CATEGORY-TABLE</em></p>
    
    <p>id, fk_product_id, fk_category_name</p>
    
    <p><em>FEATURE-TABLE</em></p>
    
    <p>id, fk_category_id, feature_name, feature_value</p>
  </blockquote>