title: Designing a SQL schema for a combination of many-to-many relationship (variations of products)
body: <p>I hope the title is somewhat helpful. I'm using MySQL as my database</p>
  
  <p>I am building a database of products and am not sure how to handle storing prices/SKU of variations of a product. A product may have unlimited variations, and each variation combination has its own price/SKU/etc..</p>
  
  <p>This is how I have my products/variations table set up at the moment:</p>
  
  <pre><code>PRODUCTS
  +--------------------------+
  | id | name | description  |
  +----+------+--------------+
  | 1  | rug  | a cool rug   |
  | 2  | cup  | a coffee cup |
  +----+------+--------------+
  
  PRODUCT_VARIANTS
  +----+------------+----------+-----------+
  | id | product_id | variant  | value     |
  +----+------------+----------+-----------+
  | 1  | 1          | color    | red       |
  | 2  | 1          | color    | blue      |
  | 3  | 1          | color    | green     |
  | 4  | 1          | material | wool      |
  | 5  | 1          | material | polyester |
  | 6  | 2          | size     | small     |
  | 7  | 2          | size     | medium    |
  | 8  | 2          | size     | large     |
  +----+------------+----------+-----------+
  
  (`products.id` is a foreign key of `product_variants.product_id`)
  </code></pre>
  
  <p>I've created an SQLFiddle with this sample data: <a href="http://sqlfiddle.com/#!2/2264d/1" rel="noreferrer">http://sqlfiddle.com/#!2/2264d/1</a></p>
  
  <p>The user is allowed to enter any variation name (<code>product_variants.variant</code>) and can assign any value to it (<code>product_variants.value</code>). <strong>There should not be a limit the amount of variations/values a user may enter.</strong></p>
  
  <p><strong>This is where my problem arises: storing prices/SKU for each variation without adding a new table/column every time someone adds a product with a variant that did not exist before.</strong> </p>
  
  <p>Each variant may have the same price but the SKU is unique to each product. 
  For example Product <code>1</code> has 6 different combinations (3 colors * 2 materials) and Product <code>2</code> only has 3 different combination (3 sizes * 1). </p>
  
  <p>I've thought about storing the combinations as a text, i.e:</p>
  
  <pre><code>+------------+-----------------+-------+------+
  | product_id | combination     | price | SKU  |
  +------------+-----------------+-------+------+
  | 1          | red-wool        | 50.00 | A121 |
  | 1          | red-polyester   | 50.00 | A122 |
  | 1          | blue-wool       | 50.00 | A123 |
  | 1          | blue-polyester  | 50.00 | A124 |
  | 1          | green-wool      | 50.00 | A125 |
  | 1          | green-polyester | 50.00 | A125 |
  | 2          | small           | 4.00  | CD12 |
  | 2          | medium          | 4.00  | CD13 |
  | 2          | large           | 3.50  | CD14 |
  +------------+-----------------+-------+------+
  </code></pre>
  
  <p>But there must be a better, normalized, way of representing this data.  Hypothetical situation: I want to be able to search for a blue product that is less than $10. With the above database structure it is not possible to do without parsing the text and that is something I want to avoid.</p>
  
  <p>Any help/suggestions are appreciated =)</p>