建模产品变体

2021-11-20 00:00:00 database mysql relational-database database-design entity-attribute-value

我一直在尝试对产品变体进行建模，并认为我可能需要使用 EAV.如果没有 EAV，我可能也能做到，但我担心我可能错过了一些东西.这是我的设计:

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:

这就是我想表达的:

  1. 一个产品可以有0个或多个产品变体(例如T恤产品可能有尺寸和颜色变体).
  2. 一个产品变体可以有1个或多个产品变体选项(例如，尺寸变体可以是小、中、大).
  3. 一个 SKU 由 1 个或多个 product variant options 组成(product_variant_option_combination 表将包含 `product_variant_options 的所有可能组合.所以，如果有 3 种尺寸和 3 种颜色，则会有 3 * 3 = 9 种组合——每个组合都有自己的 SKU 和价格).
  4. 一个产品可以有1个或多个SKU.
  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.

如果产品没有任何变体，则忽略product_variants、product_variant_options 和product_variant_option_combinations.

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?

更新 1

@Edper:

如果一个产品可以有 0 个或多个(可选模式)产品变体(例如尺寸、颜色等).产品变体是否也可以有 0 个或多个具有该变体的产品?

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?

我不这么认为.像T 恤"这样的产品可能有一个尺寸"变体，而另一个像裤子"这样的产品也可能有一个尺寸"变体，但我认为这只是偶然.没有必要让size"只出现一个记录，因为size"可能有不同的上下文.

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.

更新 2:

以下是我如何查看数据的示例:

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

我已将变体 Size 及其相关值装箱.我想明确指出，这些不被视为重复数据.3 种产品的 Size 变体只是偶然.我认为没有必要对此进行规范化.每个产品可以有 0 个或多个变体——我不知道它们.我期待重复"(尽管它们并不是真正的重复，因为它们总是在特定产品的上下文中——因此，小部件 1 的尺寸"变体与小部件 2 的尺寸"变体不同).

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).

更新 3:

我现在看到，在我的设计中，一个 product 可能有多个相同的 product_variants.我认为这可以通过使 product_variants.product_id 和 product_variants.name 成为组合键来解决.这意味着小部件 1 只能有一次大小"变体.

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 也需要是一个复合键.

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

更新 4:

通过更新我的 product_variant_option_combinations 以包含 product_variant_id(FK 到 product_variants.id)并强制执行 UNIQUE 约束使用 product_variant_option_combinations.sku_id 和 product_variant_option_combinations.product_variant_id，我想我能够防止出现 SKU 的问题即小"和大".这是正确的吗?

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

推荐答案

你可以有这样的设计:

 +---------------+     +-------------------+
 | 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:

  • 产品
    • PK:product_id
    • 英国:product_name
    • PK:option_id
    • 英国:option_name
    • PK:option_id、value_id
    • 英国:option_id、value_name
    • FK:option_id 参考选项(option_id)
    • PK:product_id、option_id
    • FK:product_id 参考产品 (product_id)
    • FK:option_id 参考选项(option_id)
    • PK:product_id、variant_id
    • 英国:sku_id
    • FK:product_id 参考产品 (product_id)
    • 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)

    你有:

    • 产品例如衬衫、套头衫、裤子
    • 选项例如尺寸、颜色、长度
    • OPTION_VALUES 例如尺寸 - 小、中、大；颜色 - 红色、白色、蓝色
    • PRODUCT_OPTIONS 例如衬衫 - 尺寸、颜色；裤子 - 长度、颜色

    然后您需要创建一个 n 维数组，维数等于产品的选项数.数组中的每个元素对应一个产品变体.每个产品总是至少有一个产品变体；因为总是有产品原样"的伪选项

    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，例如衬衫 1、衬衫 2
    • VARIANT_VALUES 例如衬衫 1:小红；衬衫 2:小白

    除非已为与产品关联的所有选项指定了值，否则您可能希望进行验证以确保不会分配 SKU.

    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)

    在您的设计中似乎没有停止添加记录的条目 (product_variant_option_id: 2; sku_id 1) 以便 SKU W1SSCW 现在具有 Small 和 Large 选项.没有什么可以阻止记录的条目(product_variant_option_id: 7; sku_id: 1)这样SKU W1SSCW也有选项Amateur.

    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)

    在我的设计中，您无法输入额外的 VARIANT_VALUES 记录(product_id:1；variant_id:1；option_id:1；value_id:2) - 因此 SKU W1SSCW 现在具有 Small 和 Large 两个选项 - 由于主要VARIANT_VALUES 和现有 VARIANT_VALUES 记录上的键(product_id:1；variant_id:1；option_id:1；value_id:1).在我的设计中，您无法输入 VARIANT_VALUES 记录 (product_id: 1; variant_id: 1; option_id: 4; value_id: 1) - 因此 SKU W1SSCW 也有选项 Amateur - 由于外键引用 PRODUCT_OPTIONS 并且缺少此表中记录的 (product_id: 1; option_id: 4) 表明 Class 是产品 Widget 1 的有效选项.

    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.

    编辑:没有 PRODUCT_OPTIONS 表的设计

    EDIT: Design with no PRODUCT_OPTIONS table

    你可以有这样的设计:

    +---------------+     +---------------+
| 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:

    • 产品
      • PK:product_id
      • 英国:product_name
      • PK:product_id、option_id
      • 英国:product_id、option_name
      • PK:product_id、option_id、value_id
      • 英国:product_id、option_id、value_name
      • FK:product-id, option_id REFERENCES OPTIONS (product_id, option_id)
      • PK:product_id、sku_id
      • 英国:sku_id
      • FK:product_id 参考产品 (product_id)
      • PK:product_id、sku_id、option_id
      • FK:product_id, sku_id REFERENCES PRODUCT_SKUS (product_id, sku_id)
      • FK:product_id、option_id 参考选项(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)

相关文章