Modeling Product Variants

Question

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:

Here's what I am trying to represent:

1. A product can have 0 or more product variants (e.g. a t-shirt product may have size and color variants).
2. A product variant can have 1 or more product variant options (e.g. the size variant can be small, medium, large).
3. An SKU is comprised of 1 or more product variant options (the product_variant_option_combination 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).
4. A product can have 1 or more SKUs.

If the product doesn't have any variants, then just ignore product_variants, product_variant_options, and product_variant_option_combinations.

Is this design sound? Will I end up having problems querying this? Will it scale? Is it normalized?

UPDATE 1

@Edper:

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?

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.

The products I'm dealing with vary greatly and they are bound to have similarly named variants.

UPDATE 2:

Here's an example of how I see my data:

I've boxed the variant Size and its associated values. I want to make it clear that these are not considered to be duplicate data. The Size 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).

UPDATE 3:

I see now that, in my design, it is possible for a product to have multiple identical product_variants. I think that can be resolved by making product_variants.product_id and product_variants.name a composite key. This means that Widget 1 can only have "Size" variant once.

product_variant_options.product_variant_id product_variant_options.name would also need to be a composite key.

UPDATE 4:

By updating my product_variant_option_combinations to include product_variant_id (FK to product_variants.id) and enforcing a UNIQUE constraint with product_variant_option_combinations.sku_id and product_variant_option_combinations.product_variant_id, I think I was able to prevent the problem of having an SKU that is both "Small" and "Large". Is this right?

-- 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 */;

Solution

You could have a design like:

 +---------------+     +-------------------+
 | PRODUCTS      |-----< PRODUCT_VARIANTS  |
 +---------------+     +-------------------+
 | #product_id   |     | #product_id       |
 |  product_name |     | #variant_id       |
 +---------------+     |  sku_id           |
         |             +-------------------+
         |                       |
+--------^--------+     +--------^--------+
| PRODUCT_OPTIONS |-----< VARIANT_VALUES  |
+-----------------+     +-----------------+
| #product_id     |     | #product_id     |
| #option_id      |     | #variant_id     |
+--------v--------+     | #option_id      |
         |              |  value_id       |
+-----------------+     +--------v--------+
| OPTIONS         |              |
+-----------------+              |
| #option_id      |              |
|  option_name    |              |
+-----------------+              |
         |                       |
 +-------^-------+               |
 | OPTION_VALUES |---------------+
 +---------------+
 | #option_id    |
 | #value_id     |
 |  value_name   |
 +---------------+

With the Primary, Unique and Foreign Keys:

• PRODUCTS
  • PK: product_id
  • UK: product_name
• OPTIONS
  • PK: option_id
  • UK: option_name
• OPTION_VALUES
  • PK: option_id, value_id
  • UK: option_id, value_name
  • FK: option_id REFERENCES OPTIONS (option_id)
• PRODUCT_OPTIONS
  • PK: product_id, option_id
  • FK: product_id REFERENCES PRODUCTS (product_id)
  • FK: option_id REFERENCES OPTIONS (option_id)
• PRODUCT_VARIANTS
  • PK: product_id, variant_id
  • UK: sku_id
  • FK: product_id REFERENCES PRODUCTS (product_id)
• VARIANT_VALUES
  • PK: product_id, variant_id, option_id
  • FK: product_id, variant_id REFERENCES PRODUCT_VARIANTS (product_id, variant_id)
  • FK: product_id, option_id REFERENCES PRODUCT_OPTIONS (product_id, option_id)
  • FK: option_id, value_id REFERENCES OPTION_VALUES (option_id, value_Id)

You have:

• PRODUCTS e.g. Shirt, Jumper, Trousers
• OPTIONS e.g. Size, Colour, Length
• OPTION_VALUES e.g Size - Small, Medium, Large; Colour - Red, White, Blue
• PRODUCT_OPTIONS e.g. Shirt - Size, Colour; Trousers - Length, Colour

You then need to create a n-dimensional array, with the number of dimensions equalling the number of options for the product. Each element in the array corresponds to a product variant. There will always be at least one product variant for each product; as there is always the pseudo option of the product "as-is"

• PRODUCT_VARIANTS e.g Shirt 1, Shirt 2
• VARIANT_VALUES e.g Shirt 1: Small Red; Shirt 2: Small White

You may wish to have validation to ensure a SKU is not assigned unless values have been specified for all options associated with a product.

Based on the spreadsheet of how you see your data you could enter data in your tables as follows:

PRODUCTS
========
id  name
--- --------
1   Widget 1
2   Widget 2
3   Widget 3
 
PRODUCT_VARIANTS
================
id  product_id name
--- ---------- ------
1   1          Size   (Widget 1)
2   1          Color  (Widget 1)
3   2          Size   (Widget 2)
4   3          Class  (Widget 3)
5   3          Size   (Widget 3)
 
PRODUCT_VARIANT_OPTIONS
=======================
id  product_variant_id name
--- ------------------ -------------
1   1                  Small         (Widget 1; Size)
2   1                  Large         (Widget 1; Size)
3   2                  White         (Widget 1; Color)
4   2                  Black         (Widget 1; Color)
5   3                  Small         (Widget 2; Size)
6   3                  Medium        (Widget 2; Size)
7   4                  Amateur       (Widget 3; Class)
8   4                  Professional  (Widget 3; Class)
9   5                  Medium        (Widget 3; Size)
10  5                  Large         (Widget 3; Size)
 
SKUS
====
id  product_id sku    price
--- ---------- ------ -----
1   1          W1SSCW    10 (Widget 1)
2   1          W1SSCB    10 (Widget 1)
3   1          W1SLCW    12 (Widget 1)
4   1          W1SLCB    15 (Widget 1)
5   2          W2SS     100 (Widget 2)
6   2          W2SM     100 (Widget 2)
7   3          W3CASM    50 (Widget 3)
8   3          W3CASL    50 (Widget 3)
9   3          W3CPSM   150 (Widget 3)
10  3          W3CPSL   160 (Widget 3)
 
PRODUCT_VARIANT_OPTION_COMBINATIONS
===================================
product_variant_option_id sku_id
------------------------- ------
1                         1      (W1SSCW; Size; Small)
3                         1      (W1SSCW; Color; White)
1                         2      (W1SSCB; Size; Small)
4                         2      (W1SSCB; Color; Black)
2                         3      (W1SLCW; Size; Large)
3                         3      (W1SLCW;  Color; White)
2                         4      (W1SLCB; Size; Large)
4                         4      (W1SLCB; Color; Black)
5                         5      (W2SS; Size; Small)
6                         6      (W2SM; Size; Medium)
7                         7      (W3CASM; Class; Amateur)
9                         7      (W3CASM; Size; Medium)
7                         8      (W3CASL; Class; Amateur)
10                        8      (W3CASL; Size; Large)
8                         9      (W3CPSM; Class; Professional)
9                         9      (W3CPSM; Size; Medium)
8                         10     (W3CPSL; Class; Professional)
10                        10     (W3CPSL; Size; Large)

There seems to be nothing in your design from stopping the addition of the entry of the record (product_variant_option_id: 2; sku_id 1) so that SKU W1SSCW has now both the options of Small and Large. There is nothing to stop the entry of the record (product_variant_option_id: 7; sku_id: 1) so that SKU W1SSCW also has the option Amateur.

Based on the spreadsheet of how you see your data you could enter data in my tables as follows:

PRODUCTS
========
product_id product_name
---------- ------------
1          Widget 1
2          Widget 2
3          Widget 3
 
OPTIONS
=======
option_id option_name
--------- -----------
1         Size SL
2         Color
3         Size SM
4         Class
5         Size ML
 
OPTION_VALUES
=============
option_id value_id value_name
--------- -------- ------------
1         1        Small        (Size SL)
1         2        Large        (Size SL)
2         1        White        (Color)
2         2        Black        (Color)
3         1        Small        (Size SM)
3         2        Medium       (Size SM)
4         1        Amateur      (Class)
4         2        Professional (Class)
5         1        Medium       (Size ML)
5         2        Large        (Size ML)
 
PRODUCT_OPTIONS
===============
product_id option_id
---------- ---------
1          1         (Widget 1; Size SL)
1          2         (Widget 1; Color)
2          3         (Widget 2; Size SM)
3          4         (Widget 3; Class)
3          5         (Widget 4; Size ML)
 
PRODUCT_VARIANTS
================
product_id variant_id sku_id
---------- ---------- ------
1          1          W1SSCW (Widget 1)
1          2          W1SSCB (Widget 1)
1          3          W1SLCW (Widget 1)
1          4          W1SLCB (Widget 1)
2          1          W2SS   (Widget 2)
2          2          W2SM   (Widget 2)
3          1          W3CASM (Widget 3)
3          2          W3CASL (Widget 3)
3          3          W3CPSM (Widget 3)
3          4          W3CPSL (Widget 3)
 
VARIANT_VALUES
==============
product_id variant_id option_id value_id
---------- ---------- --------- --------
1          1          1         1        (W1SSCW; Size SL; Small)
1          1          2         1        (W1SSCW; Color; White)
1          2          1         1        (W1SSCB; Size SL; Small)
1          2          2         2        (W1SSCB; Color; Black)
1          3          1         2        (W1SLCW; Size SL; Large)
1          3          2         1        (W1SLCW; Color; White)
1          4          1         2        (W1SLCB; Size SL; Large)
1          4          2         2        (W1SLCB; Color; Black)
2          1          3         1        (W2SS; Size SM; Small)
2          2          3         2        (W2SM; Size SM; Medium)
3          1          4         1        (W3CASM; Class; Amateur)
3          1          5         1        (W3CASM; Size ML; Medium)
3          2          4         1        (W3CASL; Class; Amateur)
3          2          5         2        (W3CASL; Size ML; Large)
3          3          4         2        (W3CPSM; Class; Professional)
3          3          5         1        (W3CPSM; Size ML; Medium)
3          4          4         2        (W3CPSL; Class; Professional)
3          4          5         2        (W3CPSL; Size ML; Large)

In my design you could not enter the additional VARIANT_VALUES record (product_id: 1; variant_id: 1; option_id: 1; value_id: 2) - so that SKU W1SSCW has now both the options of Small and Large - due to the primary key on VARIANT_VALUES and the existing VARIANT_VALUES record (product_id: 1; variant_id: 1; option_id: 1; value_id: 1). In my design you could not enter the VARIANT_VALUES record (product_id: 1; variant_id: 1; option_id: 4; value_id: 1) - so that SKU W1SSCW also has the option Amateur - due to the foreign key referencing PRODUCT_OPTIONS and the lack of a record in this table of (product_id: 1; option_id: 4) indicating that Class is a valid option for product Widget 1.

EDIT: Design with no PRODUCT_OPTIONS table

You could have a design like:

+---------------+     +---------------+
| PRODUCTS      |-----< PRODUCT_SKUS  |
+---------------+     +---------------+
| #product_id   |     | #product_id   |
|  product_name |     | #sku_id       |
+---------------+     |  sku          |
        |             |  price        |
        |             +---------------+
        |                     |
+-------^-------+      +------^------+
| OPTIONS       |------< SKU_VALUES  |
+---------------+      +-------------+
| #product_id   |      | #product_id |
| #option_id    |      | #sku_id     |
|  option_name  |      | #option_id  |
+---------------+      |  value_id   |
        |              +------v------+
+-------^-------+             |
| OPTION_VALUES |-------------+
+---------------+
| #product_id   |
| #option_id    |
| #value_id     |
|  value_name   |
+---------------+

With the Primary, Unique and Foreign Keys:

• PRODUCTS
  • PK: product_id
  • UK: product_name
• OPTIONS
  • PK: product_id, option_id
  • UK: product_id, option_name
• OPTION_VALUES
  • PK: product_id, option_id, value_id
  • UK: product_id, option_id, value_name
  • FK: product-id, option_id REFERENCES OPTIONS (product_id, option_id)
• PRODUCT_SKUS
  • PK: product_id, sku_id
  • UK: sku_id
  • FK: product_id REFERENCES PRODUCTS (product_id)
• SKU_VALUES
  • PK: product_id, sku_id, option_id
  • FK: product_id, sku_id REFERENCES PRODUCT_SKUS (product_id, sku_id)
  • FK: product_id, option_id REFERENCES OPTIONS (product_id, option_id)
  • FK: product_id, option_id, value_id REFERENCES OPTION_VALUES (product_id, option_id, value_id)

Based on the spreadsheet of how you see your data you could enter data in these tables as follows:

PRODUCTS
========
product_id product_name
---------- ------------
1          Widget 1
2          Widget 2
3          Widget 3
 
OPTIONS
=======
product_id option_id option_name
---------- --------- -----------
1          1         Size        (Widget 1)
1          2         Color       (Widget 1)
2          1         Size        (Widget 2)
3          1         Class       (Widget 3)
3          2         Size        (Widget 3)
 
OPTION_VALUES
=============
product_id option_id value_id value_name
---------- --------- -------- ------------
1          1         1        Small        (Widget1; Size)
1          1         2        Large        (Widget1; Size)
1          2         1        White        (Widget1; Color)
1          2         2        Black        (Widget1; Color)
2          1         1        Small        (Widget2; Size)
2          1         2        Medium       (Widget2; Size)
3          1         1        Amateur      (Widget3; Class)
3          1         2        Professional (Widget3; Class)
3          2         1        Medium       (Widget3; Size)
3          2         2        Large        (Widget3; Size)
 
PRODUCT_SKUS
============
product_id sku_id sku
---------- ------ ------
1          1      W1SSCW (Widget 1)
1          2      W1SSCB (Widget 1)
1          3      W1SLCW (Widget 1)
1          4      W1SLCB (Widget 1)
2          1      W2SS   (Widget 2)
2          2      W2SM   (Widget 2)
3          1      W3CASM (Widget 3)
3          2      W3CASL (Widget 3)
3          3      W3CPSM (Widget 3)
3          4      W3CPSL (Widget 3)
 
SKU_VALUES
==========
product_id sku_id option_id value_id
---------- ------ --------- --------
1          1      1         1        (W1SSCW; Size; Small)
1          1      2         1        (W1SSCW; Color; White)
1          2      1         1        (W1SSCB; Size; Small)
1          2      2         2        (W1SSCB; Color; Black)
1          3      1         2        (W1SLCW; Size; Large)
1          3      2         1        (W1SLCW; Color; White)
1          4      1         2        (W1SLCB; Size; Large)
1          4      2         2        (W1SLCB; Color; Black)
2          1      1         1        (W2SS; Size; Small)
2          2      1         2        (W2SM; Size; Medium)
3          1      1         1        (W3CASM; Class; Amateur)
3          1      2         1        (W3CASM; Size; Medium)
3          2      1         1        (W3CASL; Class; Amateur)
3          2      2         2        (W3CASL; Size; Large)
3          3      1         2        (W3CPSM; Class; Professional)
3          3      2         1        (W3CPSM; Size; Medium)
3          4      1         2        (W3CPSL; Class; Professional)
3          4      2         2        (W3CPSL; Size; Large)

Comments

[Edper]

If there are not that many similar variants from different products then your design is good. But again when a user choose a variant, would it come from pre-filled ComboBox for example or it comes from a reference table that is let's say bound to a ComboBox? If it is the latter then why is the reference table not shown in your design? But again your design is pretty good already.

[StackOverflowNewbie]

Users will not be "choosing" a variant. Instead, they will be "specifying" a variant (and they can specify whatever they want).

[StackOverflowNewbie]

I'm still trying to digest your solution. Does this approach address any particular problems with my design? If so, what? Does my design fail somehow?

[DrabJay]

As far as I see it a SKU is an attribute of a product variant. A product variant is a particular combination of product option values. You appear to have linked the SKU to a a singular product option value e.g Shirt Red, instead of a combination of values e.g Shirt Small Red. My design splits what the possible options are from which option combinations are actually available i.e. a Shirt Small Blue may not actually be available.

[StackOverflowNewbie]

Can you please identify the PKs of the tables? For example, is PRODUCT_VARIANT.variant_id the table's PK? What about OPTION_VALUES.value_id? Is that the PK? I'm still trying to study your design to see if what you're saying about the SKU make sense to me.

[StackOverflowNewbie]

"You appear to have linked the SKU to a a singular product option value e.g Shirt Red, instead of a combination of values e.g Shirt Small Red." <-- Did I? My skus table has a product_id FK. And product_variant_option_combinations is an itemized list of all product_variant_options for a given skus.

[DrabJay]

The PKs are the # attributes, so there are some compound primary keys such as for PRODUCT_VARIANTS the PK is product_id, variant_id. This means there is some additional validation in my design compared with yours e.g. in my design you can only enter VARIANT_VALUES for valid PRODUCT_OPTIONS, whereas in yours any PRODUCT_VARIANT_OPTION may be selected for any product. Also, you can only enter one OPTION_VALUE per PRODUCT_VARIANT i.e. only one size, whereas in your you could have a variant that is both Small and Large.

[StackOverflowNewbie]

1.) What table does #variant_id refer to? 2.) "whereas in yours any PRODUCT_VARIANT_OPTION may be selected for any product" <-- I don't see how this is possible since my products table is not directly connected to product_variant_options. 3.) "whereas in your you could have a variant that is both Small and Large." <-- isn't that what I want? If there is a "Size" variant, then I want to possibly have a "Small Size" and "Large Size"? I'm still a bit confused. Sorry.

[StackOverflowNewbie]

I see now what you mean by, "whereas in your you could have a variant that is both Small and Large." My design definitely fails. I'm still studying your design. I do not want OPTIONS.option_name to be a unique key. It is perfectly OK for "Size" to appear multiple times (because "Size" for Widget 1 is not the same "Size" for Widget 2). If I remove the UK constraint, will there be problems?

[StackOverflowNewbie]

In your design, where would the price go? Would it be in PRODUCT_VARIANTS? Also, how do I handle the "as in" case you mentioned? Not all products will have variants (as a matter of fact, probably majority will not have variants). I'm a bit lost with variant_id as it doesn't seem to relate to a table???

[DrabJay]

You could remove the UK on OPTIONS; however, you should ensure that users can still distinguish between the Size option that has values Small/Medium and the Size option that has values Medium/Large. Price should be a attribute of the PRODUCT_VARIANTS table in my design. variant_id is just a number to distinguish between the different variants of a particular product and does not relate to another table, you may wish to rename it to something like variant_number. For "as-is" product Widget 4 you could add a PRODUCT_VARIANT (product_id: 4; variant_id: 1; sku_id: W4) with no VARIANT_VALUES.

[StackOverflowNewbie]

Still trying to understand your design. Thank you for your patience. Is the PRODUCT_OPTIONS table necessary? All it seems to do is relate the PRODUCTS and OPTIONS table. Couldn't the same be achieved by just defining an OPTIONS.product_id FK to PRODUCTS.product_id?

[StackOverflowNewbie]

Isn't the only problem with my design is that product_variant_option_combination doesn't prevent a variant from being both Size = Small and Size = Large? Couldn't I somehow modify my table to prevent that?

[StackOverflowNewbie]

Please see my UPDATE 4. I modified product_variant_option_combination and think I may have resolved the problem wherein an SKU can both be Small and Large. Thoughts?

[DrabJay]

The PRODUCT_OPTIONS table is a 'normalisation' if more than one product can have the same option; it is not necessary if this is not the case where you could use your suggestion is OK. Update 4 will stop a SKU having a Size of both Small and Large. However, you could incorrectly add a record in SKUS_PRODUCT_VARIANT_OPTIONS indicating that SKU W2SS (Widget 2) has a Color of Professional. Due to the compound primary keys in my design, in which different tables keys use the same attributes, this entry would not be possible.

[StackOverflowNewbie]

I don't think the normalization of PRODUCT_OPTIONS is necessary in my case. Again, if Widget 1 has a "Size" option and Widget 2 also has a "Size" option, I consider those to be different. Widget 1 needs to be able to add, edit, and delete its options without affecting Widget 2. Widget 1's "Size" is not the same as Widget 2's "Size."

[StackOverflowNewbie]

What happens to your design when PRODUCT_OPTIONS table is removed? (Particularly FK: product_id, option_id REFERENCES PRODUCT_OPTIONS (product_id, option_id)? Is there a solution that doesn't require variant_id?

[DrabJay]

I have added a design without the PRODUCT_OPTIONS table, and renamed variant_id to be sku_id.

[StackOverflowNewbie]

Is OPTIONS.option_id not supposed to be an auto-incrementing value? My problem with variant_id (which you renamed with sku_id is that it's not a FK to something, which is why I was asking if there is a solution that doesn't need such a "magic number" -- but maybe it is indeed needed). Now, in the latest design, I see yet another strange column like variant_id (which is the addition of this OPTIONS.option_id column). Thoughts?

[DrabJay]

OPTIONS.option_id may be an auto-incrementing value but it does not need to be. The compound primary keys are required in order to enforce the required integrity constraints, primarily that records in each table are kept in product 'groups'.

[Igor De Oliveira Sá]

That was an awesome topic and answer. I'd like to understand how could a sql query be able to find a product typed at search box like: "widget 1 small white" ?

[Aarón Gutiérrez]

@DrabJay If a user selects Widget 1 (Size - Small) and (Color - White), based on the second database model, how would I get the sku value?, I’m kind of lost there...

[Nishant Saini]

@DrabJay : it's not working for saving product variation . Like one product can multiple variatians . In that case how it will save the data

[Nishant Saini]

@StackOverflowNewbie ; it's not working for saving product variation . Like one product can multiple variatians . In that case how it will save the data

[Omar Abdelhady]

so can you help me understand this design here, as I can't seem to understand what is the idea of having every foreign key as a primary key here

[klido]

@DrabJay In this design is there any way to prevent 2 identical variants? For example having a (t-shirt with size: small, color: blue). And then another variant with the same values (size:small and color:blue).