title: Modeling Product Variants
body: <p>I've been trying to model product variants and thought that I might need to use EAV. I might have been able to do it without EAV, but I'm concerned that I might have missed something. Here's my design:</p>
  
  <p><img src="https://i.stack.imgur.com/YSOSk.png" alt="enter image description here"></p>
  
  <p>Here's what I am trying to represent:</p>
  
  <ol>
  <li>A <code>product</code> can have 0 or more <code>product variants</code> (e.g. a t-shirt product may have size and color variants).</li>
  <li>A <code>product variant</code> can have 1 or more <code>product variant options</code> (e.g. the size variant can be small, medium, large).</li>
  <li>An <code>SKU</code> is comprised of 1 or more <code>product variant options</code> (the <code>product_variant_option_combination</code> table would contain all the possible combinations of `product_variant_options. So, if there were 3 sizes and 3 colors, there would be 3 * 3 = 9 combinations -- and each combination would be given its own SKU and price).</li>
  <li>A <code>product</code> can have 1 or more <code>SKUs</code>.</li>
  </ol>
  
  <p>If the product doesn't have any variants, then just ignore <code>product_variants</code>, <code>product_variant_options</code>, and <code>product_variant_option_combinations</code>.</p>
  
  <p>Is this design sound? Will I end up having problems querying this? Will it scale? Is it normalized?</p>
  
  <p><strong>UPDATE 1</strong></p>
  
  <p>@Edper:</p>
  
  <blockquote>
    <p>If a product can have 0 or many (optional mode) product variants (e.g. size, color etc). Does it follow that a product variants can also have 0 or many products having that variant?</p>
  </blockquote>
  
  <p>I don't think so. It is possible that a product like a "t-shirt" may have a "size" variant and another product like "pants" may also have a "size" variant, but I think that's just only happenstance. There is no need to make "size" only appear as one record because "size" might have different context.</p>
  
  <p>The products I'm dealing with vary greatly and they are bound to have similarly named variants.</p>
  
  <p><strong>UPDATE 2:</strong></p>
  
  <p>Here's an example of how I see my data:</p>
  
  <p><img src="https://i.stack.imgur.com/nlRDY.png" alt="enter image description here"></p>
  
  <p>I've boxed the variant <code>Size</code> and its associated values. I want to make it clear that these are not considered to be duplicate data. The <code>Size</code> variant for the 3 products is just happenstance. There is no need to normalize this, I think. Each product can have 0 or more variants -- and they are unknown to me. I expect "duplicates" (though they aren't really duplicates as they always are in the context of a particular product -- so, Widget 1's "Size" variant is not the same as Widget 2's "Size" variant).</p>
  
  <p><strong>UPDATE 3:</strong></p>
  
  <p>I see now that, in my design, it is possible for a <code>product</code> to have multiple identical <code>product_variants</code>. I think that can be resolved by making <code>product_variants</code>.<code>product_id</code> and <code>product_variants</code>.<code>name</code> a composite key. This means that Widget 1 can only have "Size" variant once.</p>
  
  <p><code>product_variant_options</code>.<code>product_variant_id</code> <code>product_variant_options</code>.<code>name</code> would also need to be a composite key.</p>
  
  <p><strong>UPDATE 4:</strong></p>
  
  <p>By updating my <code>product_variant_option_combinations</code> to include <code>product_variant_id</code> (FK to <code>product_variants</code>.<code>id</code>) and enforcing a UNIQUE constraint with <code>product_variant_option_combinations</code>.<code>sku_id</code> and <code>product_variant_option_combinations</code>.<code>product_variant_id</code>, I think I was able to prevent the problem of having an SKU that is both "Small" and "Large". Is this right? </p>
  
  <p><img src="https://i.stack.imgur.com/txxAb.png" alt="enter image description here"></p>
  
  <pre><code>-- phpMyAdmin SQL Dump
  -- version 4.1.14
  -- http://www.phpmyadmin.net
  --
  -- Host: 127.0.0.1
  -- Generation Time: Jul 30, 2014 at 03:35 AM
  -- Server version: 5.6.17
  -- PHP Version: 5.5.12
  
  SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
  SET time_zone = "+00:00";
  
  
  /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
  /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
  /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
  /*!40101 SET NAMES utf8 */;
  
  --
  -- Database: `mydb`
  --
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `products`
  --
  
  CREATE TABLE IF NOT EXISTS `products` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(45) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
  
  --
  -- Dumping data for table `products`
  --
  
  INSERT INTO `products` (`id`, `name`) VALUES
  (1, 'Widget 1');
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `product_variants`
  --
  
  CREATE TABLE IF NOT EXISTS `product_variants` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `product_id` int(11) NOT NULL,
    `name` varchar(45) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `UNIQUE_product_id_name` (`product_id`,`name`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
  
  --
  -- Dumping data for table `product_variants`
  --
  
  INSERT INTO `product_variants` (`id`, `product_id`, `name`) VALUES
  (2, 1, 'Color'),
  (1, 1, 'Size');
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `product_variant_options`
  --
  
  CREATE TABLE IF NOT EXISTS `product_variant_options` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `product_variant_id` int(11) NOT NULL,
    `name` varchar(45) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `UNIQUE_product_variant_id_name` (`product_variant_id`,`name`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
  
  --
  -- Dumping data for table `product_variant_options`
  --
  
  INSERT INTO `product_variant_options` (`id`, `product_variant_id`, `name`) VALUES
  (2, 1, 'Large'),
  (1, 1, 'Small'),
  (4, 2, 'Black'),
  (3, 2, 'White');
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `skus`
  --
  
  CREATE TABLE IF NOT EXISTS `skus` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `product_id` int(11) NOT NULL,
    `sku` varchar(45) NOT NULL,
    `price` decimal(10,2) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `skus_product_id_products_id_idx` (`product_id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
  
  --
  -- Dumping data for table `skus`
  --
  
  INSERT INTO `skus` (`id`, `product_id`, `sku`, `price`) VALUES
  (1, 1, 'W1SSCW', '10.00'),
  (2, 1, 'W1SSCB', '10.00'),
  (3, 1, 'W1SLCW', '12.00'),
  (4, 1, 'W1SLCB', '15.00');
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `skus_product_variant_options`
  --
  
  CREATE TABLE IF NOT EXISTS `skus_product_variant_options` (
    `sku_id` int(11) NOT NULL,
    `product_variant_id` int(11) NOT NULL,
    `product_variant_options_id` int(11) NOT NULL,
    PRIMARY KEY (`sku_id`,`product_variant_options_id`,`product_variant_id`),
    UNIQUE KEY `UNIQUE_sku_id_product_variant_id` (`sku_id`,`product_variant_id`),
    KEY `spvo_product_variant_options_id_pro_idx` (`product_variant_options_id`),
    KEY `spvo_product_variant_id_product_var_idx` (`product_variant_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  --
  -- Dumping data for table `skus_product_variant_options`
  --
  
  INSERT INTO `skus_product_variant_options` (`sku_id`, `product_variant_id`, `product_variant_options_id`) VALUES
  (1, 1, 1),
  (2, 1, 1),
  (3, 1, 2),
  (4, 1, 2),
  (1, 2, 3),
  (3, 2, 3),
  (2, 2, 4),
  (4, 2, 4);
  
  --
  -- Constraints for dumped tables
  --
  
  --
  -- Constraints for table `product_variants`
  --
  ALTER TABLE `product_variants`
    ADD CONSTRAINT `product_variants_product_id_products_id` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  
  --
  -- Constraints for table `product_variant_options`
  --
  ALTER TABLE `product_variant_options`
    ADD CONSTRAINT `product_variant_options_product_variant_id_product_variants_id` FOREIGN KEY (`product_variant_id`) REFERENCES `product_variants` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  
  --
  -- Constraints for table `skus`
  --
  ALTER TABLE `skus`
    ADD CONSTRAINT `skus_product_id_products_id` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  
  --
  -- Constraints for table `skus_product_variant_options`
  --
  ALTER TABLE `skus_product_variant_options`
    ADD CONSTRAINT `skus_product_variant_options_sku_id_skus_id` FOREIGN KEY (`sku_id`) REFERENCES `skus` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
    ADD CONSTRAINT `spvo_product_variant_options_id_product_variant_options_id` FOREIGN KEY (`product_variant_options_id`) REFERENCES `product_variant_options` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
    ADD CONSTRAINT `spvo_product_variant_id_product_variants_id` FOREIGN KEY (`product_variant_id`) REFERENCES `product_variants` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  
  /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
  /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
  /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
  </code></pre>