为多对多关系（产品的变体）的组合设计SQL模式

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

我希望这个标题有点帮助。我正在使用MySQL作为我的数据库

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

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

This is how I have my products/variations table set up at the moment:

这就是我现在设置我的产品/变体表的方法：

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

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

我用这个示例数据创建了一个SQLFiddle：http：//sqlfiddle.com/#！2/2264d/1

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.

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

This is where my problem arises: storing prices/SKU for each variation without adding a new table/column every time someone adds a product with a variant that did not exist before.

这就是我出现问题的地方：每次有人添加一个以前不存在的变体的产品时，不会添加新的表/列，而是为每个变体存储价格/ SKU。

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

每个变体可能具有相同的价格，但SKU对每个产品都是唯一的。例如，产品1有6种不同的组合（3种颜色* 2种材料），而产品2只有3种不同的组合（3种尺寸* 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 |
+------------+-----------------+-------+------+

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.

但必须有更好的，标准化的方式来表示这些数据。假设情况：我希望能够搜索低于10美元的蓝色产品。使用上面的数据库结构，如果不解析文本就不可能做到这一点，这是我想要避免的。

Any help/suggestions are appreciated =)

任何帮助/建议表示赞赏=）

6 个解决方案

#1

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

对您的问题应用规范化解决方案是给定的。在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);

#2

Part of your issues stem from a confusion between product and SKU.

您的部分问题源于产品与SKU之间的混淆。

When you sell, "XYZ pullover, size M, blue model", the latter corresponds to an SKU. It is marketed as an XYZ pullover (the product), which has a set of attributes (size and colors), each with their own set of potential values. And not all possible combinations of the latter might yield valid deliverables: you won't find absurdly thin and long jeans. SKUs, products, attributes, attribute values.

当您销售“XYZ套衫，尺寸M，蓝色模型”时，后者对应于SKU。它作为XYZ套衫（产品）销售，它具有一组属性（大小和颜色），每个属性都有自己的一组潜在值。并非后者的所有可能组合都可能产生有效的可交付成果：你不会发现荒谬的薄和长牛仔裤。 SKU，产品，属性，属性值。

And when a user wants a $10 blue pullover, he's actually looking for an SKU within a product category.

当用户想要10美元的蓝色套衫时，他实际上正在寻找产品类别中的SKU。

I hope the above clears up your confusion and where your problem and question stem from.

我希望上述内容可以解决您的困惑以及您的问题和问题源于何处。

In terms of schema, you want something like this:

在架构方面，你想要这样的东西：

---

products

• #product_id
• #product_id
• name
• 名称
• description
• 描述

Optionally, also add:

或者，还添加：

• price
• 价钱
• in_stock
• 有现货

This is a marketing related table. Nothing else. If anything outside of marketing uses a product in your application, you'll end up in a world of pain down the road.

这是营销相关表。没有其他的。如果市场营销之外的任何事情在您的应用程序中使用了某种产品，您将最终陷入痛苦的世界。

The price, if present, is a master price used to populate the field when it's null in SKUs. This makes price entry more user-friendly.

价格（如果存在）是用于在SKU中为空时填充字段的主要价格。这使得价格输入更加用户友好。

in_stock is a hopefully self-explanationary flag, ideally maintained by a trigger. It should be true if any SKU related to that product is in stock.

in_stock是一个有希望的自我解释的旗帜，理想情况下由触发器维持。如果与该产品相关的任何SKU有库存，则应该是真的。

---

product_attributes

• product_id
• PRODUCT_ID
• #attribute_id
• #attribute_id
• name
• 名称

product_attribute_values

• attribute_id
• attribute_id
• #value_id
• #value_id
• value
• 值

This just holds things like Color, Size, etc., along with their values like blue, red, S, M, L.

这只包含颜色，大小等等，以及它们的值，如蓝色，红色，S，M，L。

Note the product_id field: create a new set of attributes and values per product. Sizes change depending on the product. Sometimes it's S, M, L, etc.; other times, it'll be 38, 40, 42, and what not. Sometimes, Size is enough; other times, you need Width and Length. Blue might be a valid color for this product; another might offer Navy, Royal Blue, Teal and what not. Do NOT assume that there is any relationship between one product's attributes and those of another; the similarities, when they exist, are entirely cosmetic and coincidental.

请注意product_id字段：为每个产品创建一组新的属性和值。尺寸根据产品而变化。有时它是S，M，L等;其他时候，它将是38,40,42，什么不是。有时候，尺寸就足够了;其他时候，你需要宽度和长度。蓝色可能是该产品的有效颜色;另一个可能会提供海军，皇家蓝，蓝绿色等等。不要认为某个产品的属性与另一个产品的属性之间存在任何关系;它们存在时的相似之处完全是装饰性的和巧合的。

---

SKUs

• product_id
• PRODUCT_ID
• #sku_id
• #sku_id
• price
• 价钱

Optionally, add:

（可选）添加：

• name
• 名称
• barcode
• 条码
• stock
• 股票

This corresponds to the deliverables that get shipped.

这相当于发货的可交付成果。

It's actually the most important table underneath. This, rather than the product_id, is almost certainly what should get referenced in customer orders. It's also what should get referenced to for stock-keeping and so forth. (The only exception I've ever seen to the latter two points is when you sell something really generic. But even then, the better way to deal with this in my experience is to toss in an n-m relationship between interchangeable SKUs.)

它实际上是下面最重要的表格。这个而不是product_id几乎肯定是应该在客户订单中引用的内容。它也应该被引用到库存等等。 （我见过后两点的唯一例外是你卖的东西真的很通用。但即便如此，根据我的经验处理这个问题的更好方法是在可互换的SKU之间投入一个n-m的关系。）

The name field, if you add it, is primarily for convenience. If left null, use app-side code to make it correspond to the generic product's name, expanded if necessary with the relevant attribute names and values. Filling it allows to rephrase the latter generic name ("Levis' 501, W: 32, L: 32, Color: Dark Blue") with something more natural ("Levis' 501, 32x32, Dark Blue").

如果添加名称字段，则主要是为了方便起见。如果保留为null，则使用应用程序端代码使其与通用产品的名称相对应，必要时使用相关的属性名称和值进行扩展。填充它允许用更自然的东西（“Levis'501,32x32，深蓝色”）重新描述后者的通用名称（“Levis'501，W：32，L：32，颜色：深蓝色”）。

In case it matters, stock is better maintained using a trigger in the long run, with a double-entry bookkeeping schema in the background. This allows to distinguish between in stock and available for shipment today (which is the figure that you actually want here) vs in stock but already sold, among the multitudes of real-world scenarios that you'll encounter. Oh, and... it's occasionally a numeric, rather than an integer, if you ever need to sell anything measured in kilos or liters. If so, be sure to add an extra is_int flag, to avoid customers sending you orders for .1 laptops.

如果重要的是，从长远来看，使用触发器可以更好地维护库存，并在后台使用复式簿记架构。这样就可以区分现有的库存和今天可用的货物（这是您实际需要的数字）与库存但已售出的数量，这些都是您将遇到的众多真实场景​​。哦，而且......如果你需要出售以千克或升为单位的任何东西，它偶尔会是数字而不是整数。如果是这样，请务必添加额外的is_int标志，以避免客户向您发送.1笔记本电脑的订单。

---

product_variants

• product_id
• PRODUCT_ID
• #sku_id
• #sku_id
• #attribute_id
• #attribute_id
• value_id
• value_id

This links the deliverable's id with the corresponding attributes and values, for the sake of generating default names.

为了生成默认名称，这将可交付物的id与相应的属性和值链接起来。

The primary key is on (sku_id, attribute_id).

主键为on（sku_id，attribute_id）。

You might find the product_id field an aberrance. It is, unless you add foreign keys referencing:

您可能会发现product_id字段存在异常。它是，除非你添加外键引用：

• SKUs (product_id, sku_id)
• SKU（product_id，sku_id）
• product_attributes (product_id, attribute_id)
• product_attributes（product_id，attribute_id）
• product_attribute_values (attribute_id, value_id)
• product_attribute_values（attribute_id，value_id）

(Don't forget the extra unique indexes on the corresponding tuples if you decide to add these foreign keys.)

（如果您决定添加这些外键，请不要忘记相应元组上的额外唯一索引。）

---

Three additional remarks in conclusion.

结论还有三点意见。

Firstly, I'd like to stress once again that, in terms of flow, not all combinations of attributes and values yield a valid deliverable. Width might be 28-42 and length might be 28-42, but you probably won't see a seriously skinny 28x42 jeans. You're best off NOT automatically populating every possible variation of every product by default: add UI to enable/disable them as needed, make it checked by default, alongside name, barcode and price fields. (Name and price will usually be left blank; but one day, you'll need to organize a sale on blue pullovers only, on grounds that the color is discontinued, while you continue to sell the other options.)

首先，我想再次强调，就流量而言，并非所有属性和值的组合都能产生有效的可交付成果。宽度可能是28-42，长度可能是28-42，但你可能不会看到一个严重的28x42紧身牛仔裤。最好不要默认自动填充每个产品的每个可能的变体：添加UI以根据需要启用/禁用它们，默认选中它，以及名称，条形码和价格字段。 （名称和价格通常会留空;但有一天，您只需要在蓝色套头衫上进行销售，理由是颜色已经停止，而您继续销售其他选项。）

Secondly, keep in mind, if you ever need to additionally manage product options, that many actually are product attributes in disguise, and that those that aren't yield new SKUs that must also be taken into account when it comes to stock-keeping. A bigger HD option for a laptop, for instance, is really a variant of the same product (Normal vs Large HD size) that is masquerading as an option due to (very valid) UI considerations. In contrast, wrapping the laptop as a christmas gift is a genuine option that has references a completely separate SKU in bookkeeping terms (e.g. .8m of gift wrap) -- and, should you ever need to come up with average marginal costs, a fraction of staff time.

其次，请记住，如果您需要另外管理产品选项，那么许多实际上是伪装的产品属性，而那些不产生新SKU的产品属性在库存时也必须考虑到。例如，用于笔记本电脑的更大HD选项实际上是同一产品（正常与大型高清尺寸）的变体，由于（非常有效的）UI考虑因素而伪装成选项。相比之下，将笔记本电脑作为圣诞礼物包装是一个真正的选择，它在记账条款中引用了完全独立的SKU（例如.8m的礼品包装） - 并且，如果您需要提出平均边际成本，则需要一小部分工作人员的时间。

Lastly, you'll need to come up with an ordering method for your attributes, their values, and the subsequent variants. For this, the easiest is to toss in an extra position field in the attributes and values tables.

最后，您需要为您的属性，值以及后续变体提供一种排序方法。为此，最简单的方法是在属性和值表中添加一个额外的位置字段。

#3

I would use 4 tables:

我会用4个表：

generic_product: product_id, name, description 

e.g. 1, 'rug', 'a coffee rug' / 2, 'mug', 'a coffee mug'

例如1，'地毯'，'咖啡地毯'/ 2，'马克杯'，'咖啡杯'

generic_product_property: product_id, property_id, property_name 

e.g. 1, 10, 'color' / 1, 11, 'material'

例如1,10，'颜色'/ 1,11''材料'

sellable_product: sku, product_id, price 

e.g. 'A121', 1, 50.00 / 'A122', 1, 45.00

例如'A121'，1,50.00 /'A122'，1,45.00

sellable_product_property: sku, property_id, property_value 

e.g. 'A121', 10, 'red' / 'A121', 11, 'wool' / 'A122', 10, 'green' / 'A122', 11, 'wool'

例如'A121'，10，'red'/'A121'，11，'wool'/'A122'，10，'green'/'A122'，11，'wool'

This will allow your user to define any property for your sellable products he wants.

这将允许您的用户为他想要的可销售产品定义任何属性。

Your application will have to ensure with its business logic that sellable_products are described completely (check that for every applicable generic product property the sellable product property is defined).

您的应用程序必须确保其业务逻辑完全描述sellable_products（检查每个适用的通用产品属性是否定义了可销售产品属性）。

#4

This is similar to another question I saw a while back her on SO

这类似于我在一段时间内看到的另一个问题

Designing a database : Which is the better approach?

设计数据库：哪种方法更好？

If you take a look there you'll see that you are basically asking the same narrow (attribute based) vs. wide table question. I've used both depending on the scenario, but I'd be really careful the way you have it implemented right now. And the fact that there really isn't a good way to match those variants to the SKUs (at least not that I can think of) may force you to change your tables.

如果你看一下那里你会发现你基本上要求相同的狭窄（基于属性）和宽表问题。我根据场景使用了两者，但我现在非常小心你实现它的方式。事实上，没有一种方法可以将这些变体与SKU相匹配（至少不是我能想到的）可能会迫使你改变你的表格。

If you have so many different variants you also may want to look into a key-value database, or some other NoSQL solution.

如果您有许多不同的变体，您也可能需要查看键值数据库或其他一些NoSQL解决方案。

#5

In general terms, you're looking for what's called a grouper or a junk dimension. Basically it's just an row for every combination.@sahalMoidu's schema looks like it should give you what you are asking for.

一般来说，你正在寻找所谓的石斑鱼或垃圾维度。基本上它只是每一个组合的一行。@ sahalMoidu的架构看起来应该给你你想要的东西。

But before getting too hung up on normalization, you need to know if the db is there for storing data (transactional, etc) or for getting data out (dimensional, reporting, etc). Even if it is a transactional database, you have to ask yourself what you are trying to accomplish by normalization.

但是在对标准化过于依赖之前，您需要知道数据库是否存在用于存储数据（事务等）或用于获取数据（维度，报告等）。即使它是一个事务性数据库，您也必须问自己，您要通过规范化来完成什么。

#6

Sku is your primary key. You can setup foreign key relationships to the variants table with sku. Forget about productid entirely.

Sku是您的主要钥匙。您可以使用sku设置变量表的外键关系。完全忘掉productid。

Create table x (sku, price, description) primary key sku

创建表x（sku，价格，描述）主键sku