title: Database Model for Product Customization Options
body: <p>I'm in the process of designing an e-commerce where various items will have different customization options per product and am thinking of the best way to structure my database. For example, I may want to sell something in red or white as well as in different sizes but another piece may only be sold in green or red and not have a size option. I have a bunch of naive approaches I've outlined below but wonder if anyone has any ideas. I'd like to keep this relational unless there's a strong reason to move to NoSQL.</p>
  
  <ol>
  <li><strong>Option 1:</strong> Come up with a separate table for each type of option. For example, a table that has a list of all possible colors, another table that references this table with a subset of colors and is also associated with a product. There will then be another table to deal with the colors selected for the actual product that was added to the cart.</li>
  <li><strong>Option 2:</strong> Since the number of options per product will be minimal, why not just have a set of columns that contain values for each possible option. This would basically be the product table having a text column called "colors" that would then just list the color options available. I personally don't like this one since it's too limiting - what if I want to associate an image with each color or change pricing depending on the option?</li>
  <li><strong>Option 3:</strong> A combination of the previous two where each product will have a set of columns that will reference the available customization options. For example, each product will have a "color options" column that, if not null, will be available for that product and point to a well structured set of options.</li>
  </ol>