数据库 – 建模产品变体

我一直在试图模拟产品变体,并认为我可能需要使用EAV.我可能没有EAV可以做到,但我担心我可能错过了一些事情.这是我的设计：

以下是我要代表的内容：

>产品可以有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)

数据库 – 建模产品变体

解决方法

猜你在找的MsSQL相关文章