以下是我要代表的内容：
>产品可以有0个或更多的产品变体(例如,T恤产品可能具有尺寸和颜色变体).
>产品变体可以具有1个或多个产品变体选项(例如,尺寸变体可以是小的,中等的,大的).
>一个SKU由一个或多个产品变体选项组成(product_variant_option_combination表将包含`product_variant_options的所有可能的组合,因此,如果有3种尺寸和3种颜色,则会有3 * 3 = 9组合,而每种组合将被给予自己的SKU和价格).
>产品可以有一个或多个SKU.
如果产品没有任何变体,那么只需忽略product_variants,product_variant_options和product_variant_option_combination.
这个设计是否有声？查询这个问题会不会有问题？会扩大吗是否正常化？
更新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?
我不这么认为可能像“T恤”这样的产品可能具有“尺寸”变体,而另一种产品如“裤子”也可能具有“尺寸”变体,但我认为只是偶然的事情.没有必要使“大小”只显示为一个记录,因为“大小”可能有不同的上下文.
我正在处理的产品差别很大,而且他们必须有类似的命名变体.
更新2：
以下是我如何查看我的数据的示例：
我已经设置了变体大小及其关联的值.我想说明这些不被认为是重复的数据. 3种产品的尺寸变体只是偶然的.我认为没有必要规范化这一点.每个产品都可以有0个或更多个变体 – 而且它们对我来说是未知的.我希望“重复”(虽然它们不是真正的重复,因为它们总是在特定产品的上下文中)所以,Widget 1的“Size”变体与Widget 2的“Size”变体不同.
更新3：
我现在看到,在我的设计中,产品有可能具有多个相同的product_variants.我认为可以通过将product_variants.product_id和product_variants.name作为复合键来解决.这意味着小部件1只能有一个“大小”变体.
product_variant_options.product_variant_id product_variant_options.name也需要是一个复合键.
更新4：
通过更新我的product_variant_option_combination以将product_variant_id(FK转换为product_variants.id)并且使用product_variant_option_combinations.sku_id和product_variant_option_combinations.product_variant_id强制执行UNIQUE约束,我想我能够防止出现“小”和“大”的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,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,'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,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`,'Large'),'Small'),(4,2,'Black'),(3,'White'); -- -------------------------------------------------------- -- -- Table structure for table `skus` -- CREATE TABLE IF NOT EXISTS `skus` ( `id` int(11) NOT NULL AUTO_INCREMENT,`sku` varchar(45) NOT NULL,`price` decimal(10,2) NOT NULL,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`,`sku`,`price`) VALUES (1,'W1SSCW','10.00'),(2,'W1SSCB','W1SLCW','12.00'),'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_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`,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_options_id`) VALUES (1,1),2),3),4),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 |
+---------------+
使用主要,唯一和外键：
>产品
> PK：product_id
> UK：product_name
>选项
> PK：option_id
> UK：option_name
> OPTION_VALUES
> PK：option_id,value_id
> UK：option_id,value_name
> FK：option_id REFERENCES选项(option_id)
> PRODUCT_OPTIONS
> PK：product_id,option_id
> FK：product_id参考产品(product_id)
> FK：option_id REFERENCES选项(option_id)
> PRODUCT_VARIANTS
> PK：product_id,variant_id
> UK：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)
你有：
>产品,例如衬衫,跳线,裤子
>选项尺寸,颜色,长度
> OPTION_VALUES,例如Size – Small,Medium,Large;颜色 – 红色,白色,蓝色
> PRODUCT_OPTIONS例如衬衫 – 尺码,颜色;裤子 – 长度,颜色
然后,您需要创建一个n维数组,其维数等于产品选项数.数组中的每个元素对应于产品变体.每个产品将至少有一个产品变体;因为产品“原样”总是存在伪选项
> PRODUCT_VARIANTS,如衬衫1,衬衫2
> VARIANT_VALUES例如衬衫1：小红色;衬衫2：小白
您可能希望进行验证,以确保除非为与产品相关的所有选项指定了值,否则不会分配SKU.
根据您如何查看数据的电子表格,您可以在表格中输入数据,如下所示：
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.
根据您看到数据的电子表格,您可以在我的表格中输入数据如下：
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是产品小部件1的有效选项.
编辑：没有PRODUCT_OPTIONS表的设计
你可以有一个设计如：
+---------------+ +---------------+
| 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 |
+---------------+
使用主要,唯一和外键：
>产品
> PK：product_id
> UK：product_name
>选项
> PK：product_id,option_id
> UK：product_id,option_name
> OPTION_VALUES
> PK：product_id,option_id,value_id
> UK：product_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参考产品(product_id)
> SKU_VALUES
> PK：product_id,sku_id,sku_id REFERENCES PRODUCT_SKUS(product_id,sku_id)
> FK：product_id,option_id)
> FK：product_id,value_id REFERENCES OPTION_VALUES(product_id,value_id)
根据您如何查看数据的电子表格,您可以在以下表格中输入数据：
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)
