title: Designing database for product variants
body: <p>I want to model product variants (not options or attributes, simply variants).</p>
  
  <p>So every single variant is a product itself. The general information which is valid for all variants of a product is stored in another table (e.g.: textual description). Ok, I think that's fine and needs no further changes.</p>
  
  <p>For the dependent options (e.g. color: red, size: small) I have created two variants.</p>
  
  <p><strong>Variant 1:</strong></p>
  
  <p><img src="https://i.stack.imgur.com/EqLFJ.jpg" alt="diagram 1"></p>
  
  <p>A Short description of the tables:</p>
  
  <ul>
  <li>option: stores all available options (color, size, material,...)</li>
  <li>value: stores all available values (red, blue, green, small, medium, large, iron, wood)</li>
  <li>option_value: stores all possible combinations of options and values (color: {red,blue,green}, size:{small, medium, large},...</li>
  <li>product_option_value now connects a product with its options (e.g.: color:red, size:small, product_id:1; color:blue, size:small, product_id:1)</li>
  </ul>
  
  <p>Ok, I think that would work very well - on the left hand the description of the metadata (which options, which values, which combinations) for building the UI - on the right hand the linkage to the products.</p>
  
  <p>But there is one problem... The possible combinations of options and values are described to build the GUI and its possible to validate it programmatically, but the database can't to the validation. </p>
  
  <p>Therefore I created <strong>variant 2:</strong> </p>
  
  <p><img src="https://i.stack.imgur.com/d3kO7.jpg" alt="diagram 2"></p>
  
  <p>Now I'm not sure if the second solution might be the better one. What do you think? Is there room for improvement?</p>