MySQL 建模产品变体
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24923469/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Modeling Product Variants
提问by StackOverflowNewbie
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:
我一直在尝试对产品变体进行建模，并认为我可能需要使用 EAV。如果没有 EAV，我可能也能做到，但我担心我可能错过了一些东西。这是我的设计：
Here's what I am trying to represent:
这是我试图代表的内容：
- A
productcan have 0 or moreproduct variants(e.g. a t-shirt product may have size and color variants). - A
product variantcan have 1 or moreproduct variant options(e.g. the size variant can be small, medium, large). - An
SKUis comprised of 1 or moreproduct variant options(theproduct_variant_option_combinationtable 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). - A
productcan have 1 or moreSKUs.
- A
product可以有 0 个或多个product variants（例如，T 恤产品可能有尺寸和颜色变体）。 - A
product variant可以有 1 个或多个product variant options（例如，尺寸变体可以是小、中、大）。 - An
SKU由 1 个或多个组成product variant options（该product_variant_option_combination表将包含 `product_variant_options 的所有可能组合。因此，如果有 3 种尺寸和 3 种颜色，则将有 3 * 3 = 9 种组合——并且每个组合都有自己的SKU 和价格）。 - A
product可以有 1 个或多个SKUs。
If the product doesn't have any variants, then just ignore product_variants, product_variant_options, and product_variant_option_combinations.
如果产品没有任何变体，那么就忽略了product_variants，product_variant_options和product_variant_option_combinations。
Is this design sound? Will I end up having problems querying this? Will it scale? Is it normalized?
这个设计合理吗？我最终会在查询这个时遇到问题吗？它会扩展吗？正常化了吗？
UPDATE 1
更新 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?
如果一个产品可以有 0 个或多个（可选模式）产品变体（例如尺寸、颜色等）。产品变体是否也可以有 0 个或多个具有该变体的产品？
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.
我不这么认为。有可能像“T 恤”这样的产品可能有一个“尺寸”变体，而另一个像“裤子”这样的产品也可能有一个“尺寸”变体，但我认为这只是偶然。没有必要让“size”只作为一个记录出现，因为“size”可能有不同的上下文。
The products I'm dealing with vary greatly and they are bound to have similarly named variants.
我正在处理的产品差异很大，它们肯定会有类似命名的变体。
UPDATE 2:
更新 2：
Here's an example of how I see my data:
这是我如何查看数据的示例：
I've boxed the variant Sizeand its associated values. I want to make it clear that these are not considered to be duplicate data. The Sizevariant 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).
我已将变体Size及其相关值装箱。我想明确指出，这些不被视为重复数据。Size3 种产品的变体只是偶然。我认为没有必要对此进行规范化。每个产品可以有 0 个或多个变体——我不知道它们。我期待“重复”（尽管它们并不是真正的重复，因为它们总是在特定产品的上下文中——因此，Widget 1 的“Size”变体与 Widget 2 的“Size”变体不同）。
UPDATE 3:
更新 3：
I see now that, in my design, it is possible for a productto have multiple identical product_variants. I think that can be resolved by making product_variants.product_idand product_variants.namea composite key. This means that Widget 1 can only have "Size" variant once.
我现在看到，在我的设计中， aproduct可以有多个相同的product_variants. 我认为可以通过 make 解决product_variants。product_id和product_variants。name一个复合键。这意味着小部件 1 只能有一次“大小”变体。
product_variant_options.product_variant_idproduct_variant_options.namewould also need to be a composite key.
product_variant_options. product_variant_idproduct_variant_options. name还需要是一个复合键。
UPDATE 4:
更新 4：
By updating my product_variant_option_combinationsto include product_variant_id(FK to product_variants.id) and enforcing a UNIQUE constraint with product_variant_option_combinations.sku_idand 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?
通过更新 myproduct_variant_option_combinations以包含product_variant_id（FK 到product_variants. id）并使用product_variant_option_combinations. sku_id和product_variant_option_combinations。product_variant_id，我认为我能够防止同时拥有“小”和“大”SKU 的问题。这是正确的吗？
-- 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 */;
回答by DrabJay
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)
- 产品
- PK：product_id
- 英国：product_name
- 选项
- PK：option_id
- 英国：option_name
- OPTION_VALUES
- 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）
- 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
- 产品例如衬衫、套头衫、裤子
- 选项，例如尺寸、颜色、长度
- OPTION_VALUES 例如大小 - 小、中、大；颜色 - 红色、白色、蓝色
- PRODUCT_OPTIONS 例如衬衫 - 尺码、颜色；裤子 - 长度、颜色
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"
然后，您需要创建一个 n 维数组，维数等于产品的选项数。数组中的每个元素对应一个产品变体。每个产品总是至少有一个产品变体；因为总是有产品“原样”的伪选项
- PRODUCT_VARIANTS e.g Shirt 1, Shirt 2
- VARIANT_VALUES e.g Shirt 1: Small Red; Shirt 2: Small White
- PRODUCT_VARIANTS 例如衬衫 1、衬衫 2
- VARIANT_VALUES 例如衬衫 1：小红；衬衫2：小白
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.
您可能希望进行验证以确保未分配 SKU，除非已为与产品关联的所有选项指定了值。
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.
您的设计中似乎没有停止添加记录的条目 (product_variant_option_id: 2; sku_id 1) 以便 SKU W1SSCW 现在具有 Small 和 Large 选项。没有什么可以阻止记录的条目（product_variant_option_id: 7; sku_id: 1），因此 SKU W1SSCW 也有选项 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.
在我的设计中，您无法输入额外的 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 的有效选项。
EDIT: Design with no PRODUCT_OPTIONS table
编辑：没有 PRODUCT_OPTIONS 表的设计
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)
- 产品
- PK：product_id
- 英国：product_name
- 选项
- PK：product_id、option_id
- 英国：product_id、option_name
- OPTION_VALUES
- PK：product_id、option_id、value_id
- 英国：product_id、option_id、value_name
- FK: product-id, option_id REFERENCES OPTIONS (product_id, option_id)
- 产品_SKUS
- PK：product_id、sku_id
- 英国：sku_id
- FK：product_id 参考产品（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)
