设计SQL模式以结合多对多关系（产品变体） [英] Designing a SQL schema for a combination of many-to-many relationship (variations of products)

问题描述

我希望标题会有所帮助。我将MySQL用作数据库

I hope the title is somewhat helpful. I'm using MySQL as my database

我正在建立产品数据库，不确定如何处理产品价格/ SKU的存储。产品可能有无限的变化，并且每个变化组合都有自己的价格/ SKU /等。

I am building a database of products and am not sure how to handle storing prices/SKU of variations of a product. A product may have unlimited variations, and each variation combination has its own price/SKU/etc..

这就是我在时刻：

PRODUCTS
+--------------------------+
| id | name | description  |
+----+------+--------------+
| 1  | rug  | a cool rug   |
| 2  | cup  | a coffee cup |
+----+------+--------------+

PRODUCT_VARIANTS
+----+------------+----------+-----------+
| id | product_id | variant  | value     |
+----+------------+----------+-----------+
| 1  | 1          | color    | red       |
| 2  | 1          | color    | blue      |
| 3  | 1          | color    | green     |
| 4  | 1          | material | wool      |
| 5  | 1          | material | polyester |
| 6  | 2          | size     | small     |
| 7  | 2          | size     | medium    |
| 8  | 2          | size     | large     |
+----+------------+----------+-----------+

(`products.id` is a foreign key of `product_variants.product_id`)

我使用以下示例数据创建了一个SQLFiddle： http://sqlfiddle.com/#!2/ 2264d / 1

I've created an SQLFiddle with this sample data: http://sqlfiddle.com/#!2/2264d/1

允许用户输入任何变体名称（ product_variants.variant ）和可以为其分配任何值（ product_variants.value ）。 用户输入的变化/值的数量应该没有限制。

The user is allowed to enter any variation name (product_variants.variant) and can assign any value to it (product_variants.value). There should not be a limit the amount of variations/values a user may enter.

这是我遇到的问题：存储每个变体的价格/ SKU，而不是每次有人添加具有以前不存在的变体的产品时都不会添加新表/列。

每个变体可能价格相同，但每种产品的SKU都是唯一的。
例如，产品 1 有6种不同的组合（3种颜色* 2种材料），而产品 2 仅具有3种不同的组合（3种尺寸* 1）。

Each variant may have the same price but the SKU is unique to each product. For example Product 1 has 6 different combinations (3 colors * 2 materials) and Product 2 only has 3 different combination (3 sizes * 1).

我已经考虑过将组合存储为文本，即：

I've thought about storing the combinations as a text, i.e:

+------------+-----------------+-------+------+
| product_id | combination     | price | SKU  |
+------------+-----------------+-------+------+
| 1          | red-wool        | 50.00 | A121 |
| 1          | red-polyester   | 50.00 | A122 |
| 1          | blue-wool       | 50.00 | A123 |
| 1          | blue-polyester  | 50.00 | A124 |
| 1          | green-wool      | 50.00 | A125 |
| 1          | green-polyester | 50.00 | A125 |
| 2          | small           | 4.00  | CD12 |
| 2          | medium          | 4.00  | CD13 |
| 2          | large           | 3.50  | CD14 |
+------------+-----------------+-------+------+

但是必须有一种更好的，标准化的表示此数据的方法。假设情况：我希望能够搜索价格低于10美元的蓝色产品。有了上述数据库结构，就不可能不分析文本而已，这是我要避免的事情。

But there must be a better, normalized, way of representing this data. Hypothetical situation: I want to be able to search for a blue product that is less than $10. With the above database structure it is not possible to do without parsing the text and that is something I want to avoid.

任何帮助/建议都值得赞赏=）

Any help/suggestions are appreciated =)

推荐答案

将归一化应用于您的问题，解决方案如给定。运行并在Fiddle上查看它

Applying normalization to your problem the solution is as given. Run and see it on Fiddle

Fiddle

CREATE TABLE products 
    (
     product_id int auto_increment primary key, 
     name varchar(20), 
     description varchar(30)

    );

INSERT INTO products
(name, description)
VALUES
('Rug', 'A cool rug'  ),
('Cup', 'A coffee cup');

create table variants (variant_id int auto_increment primary key,
                       variant varchar(50)
                       );
insert into variants (variant)
values ('color'),('material'),('size') ;   
create table variant_value(value_id int auto_increment primary key, 
                           variant_id int ,
                           value varchar(50)
                           );

insert into variant_value (variant_id,value)
values (1 ,'red'),(1 ,'blue'),(1 ,'green'),
        (2 ,'wool'),(2 ,'polyester'),
        (3 ,'small'),(3 ,'medium'),(3 ,'large');



create table product_Variants( product_Variants_id int  auto_increment primary key,
                            product_id int,
                            productVariantName varchar(50),
                            sku varchar(50),
                            price float
                            );




create table product_details(product_detail_id int auto_increment primary key,
                             product_Variants_id int,

                             value_id int
                             );

insert into product_Variants(product_id,productVariantName,sku,price)
values (1,'red-wool' ,'a121',50);

insert into product_details(product_Variants_id , value_id)
values( 1,1),(1,4);

insert into product_Variants(product_id,productVariantName,sku,price)
values (1,'red-polyester' ,'a122',50);

insert into product_details(product_Variants_id , value_id)
values( 2,1),(2,5);

这篇关于设计SQL模式以结合多对多关系（产品变体）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！