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

将规范化应用于您的问题,解决方案如下所示。跑过去看看 SQL 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
);

INSERT INTO product_variants
    (product_id, productvariantname, sku, price)
VALUES
    (1, 'red-wool', 'a121', 50),
    (1, 'red-polyester', 'a122', 50);

-- ========================================

CREATE TABLE product_details (
    product_detail_id   int AUTO_INCREMENT PRIMARY KEY,
    product_variants_id int,
    value_id            int
);

INSERT INTO product_details
    (product_variants_id, value_id)
VALUES
    (1, 1),
    (1, 4),
    (2, 1),
    (2, 5);

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

当您销售“XYZ套头衫,M码,蓝色型号”时,后者对应于SKU。它以XYZ套头衫(产品)的形式销售,它有一套属性(尺寸和颜色),每个属性都有自己的潜在价值。并不是后者的所有可能组合都能产生有效的交付成果:你不会发现又细又长的牛仔裤。SKU、产品、属性、属性值。

当用户想要一件10美元的蓝色套头衫时,他实际上是在寻找一个产品类别中的SKU。

我希望以上能澄清你的困惑,以及你的问题和疑问来自哪里。

在模式方面,您需要这样的东西:

产品

• #产品id
• 名称
• 描述

也可以选择添加:

• 价格
• 进入锁定状态

这是一个 市场营销 相关表格。没有别的。如果 任何东西 在营销之外,在应用程序中使用产品,你最终会陷入一个痛苦的世界。

价格(如果存在)是在SKU中为空时用于填充字段的主价格。这使得价格输入更加人性化。

in_stock是一个有望自我解释的标志,最好由触发器来维护。如果 任何 与该产品相关的SKU有库存。

产品属性

• 产品id
• #属性id
• 名称

产品属性值

• 属性id
• #值_id
• 价值

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

请注意product_id字段: 为每个产品创建一组新的属性和值 。尺寸因产品而异。有时是s、M、L等。;其他时候会是38、40、42等等。有时,尺寸就足够了;其他时候,您需要“宽度”和“长度”。蓝色可能是该产品的有效颜色;另一个可能会提供海军蓝、宝蓝、天蓝等等。不要认为一种产品的属性和另一种产品之间存在任何关系;这些相似之处,如果存在的话,完全是表面上的巧合。

库存单位

• 产品id
• #库存单位id
• 价格

(可选)添加:

• 名称
• 条形码
• 股票

这与发货的可交付成果相对应。

它实际上是下面最重要的桌子。 这 ,而不是product_id,几乎可以肯定是客户订单中应该引用的内容。这也是库存管理等方面应该参考的内容。(对于后两点,我见过的唯一例外是当你销售真正通用的东西时。但即使如此,根据我的经验,处理这一问题的更好方法是在可互换的SKU之间建立n-m关系。)

如果添加名称字段,主要是为了方便起见。如果保留为null,则使用应用程序端代码使其与通用产品的名称相对应,必要时使用相关的属性名称和值进行扩展。填充它可以将后一个通用名称(“Levis’501,W:32,L:32,颜色:深蓝”)改为更自然的名称(“Leves’501,32x32,深蓝”)。

如果重要的话,从长远来看,使用触发器可以更好地维护股票,并在后台使用复式记账模式。这可以在您将遇到的众多现实场景中区分库存和今天可供发货(这是您在这里实际想要的数字)与库存但已经售出。哦,还有。。。如果你需要出售任何以公斤或升为单位的东西,它偶尔会是一个数字,而不是整数。如果是这样的话,一定要添加一个额外的is_int标志,以避免客户向您发送.1笔记本电脑的订单。

乘积变量

• 产品id
• #库存单位id
• #属性id
• 值_id

这将 可交付的 的id以及相应的属性和值,以便生成默认名称。

主键位于(sku_id,attribute_id)上。

您可能会发现product_id字段是一个异常。除非添加外键引用:

• sku(product_id,sku_id)
• 产品属性(product_id,attribute_id)
• product_attribute_values(属性_id,值_id)

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

最后补充三点意见。

首先,我想再次强调,就流而言,并不是所有属性和值的组合都能产生有效的可交付成果。宽度可能是28-42,长度可能是28-43,但你可能不会看到一条非常紧身的28x42牛仔裤。你最好不要在默认情况下自动填充每种产品的每一种可能的变体:添加UI以根据需要启用/禁用它们,并在默认情况下勾选它,同时勾选名称、条形码和价格字段。(名称和价格通常会留空;但有一天,你需要组织一场仅限蓝色套头衫的销售,理由是该颜色已停产,而你需要继续销售其他套头衫。)

其次,请记住,如果你需要额外管理产品选项,那么许多实际上是伪装的产品属性,而那些不能产生新SKU的产品属性在库存时也必须考虑在内。例如,笔记本电脑的一个更大的高清选项实际上是同一产品的变体(普通与大高清尺寸),由于(非常有效的)UI考虑,它伪装成了一个选项。相比之下,将笔记本电脑包装成圣诞礼物是一种真正的选择,它在记账方面引用了一个完全独立的SKU(例如,800万的礼品包装)——而且,如果你需要计算平均边际成本,只需员工时间的一小部分。

最后,您需要为您的属性、它们的值和后续变体制定一个排序方法。为此,最简单的方法是在属性和值表中添加一个额外的位置字段。

我会使用4张桌子:

generic_product: product_id, name, description 

例如1,“地毯”,“咖啡地毯”/2,“马克杯”,“一个咖啡马克杯”

generic_product_property: product_id, property_id, property_name 

例如1,10,“颜色”/1,11,“材料”

sellable_product: sku, product_id, price 

例如“A121”,1,50.00/“A122”,1、45.00

sellable_product_property: sku, property_id, property_value 

例如,“A121”,10,“红色”/“A121’,11,“羊毛”/“A122”,10、“绿色”/“Al22”,11、“羊毛”

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

您的应用程序必须通过其业务逻辑确保sellable_products得到完整描述(检查是否为每个适用的通用产品属性定义了sellableproduct属性)。

这与我不久前在SO上看到的另一个问题类似

Designing a database : Which is the better approach?

如果你看一下,你会发现你基本上是在问同样的窄表(基于属性)和宽表问题。我已经根据场景使用了这两种方法,但我会非常小心现在的实现方式。事实上,确实没有一个好的方法来将这些变体与SKU相匹配(至少我想不出来),这可能会迫使你改变你的表格。

如果您有这么多不同的变体,您可能还想查看键值数据库或其他一些NoSQL解决方案。

一般来说,你正在寻找所谓的“追星族”或“垃圾维度”。基本上,这只是一排combination.@sahalMoidu的架构看起来应该能满足您的要求。

但是,在过于拘泥于规范化之前,您需要知道数据库是用于存储数据(事务等)还是用于获取数据(维度、报告等)。即使它是一个事务数据库,您也必须问问自己,您试图通过规范化来实现什么。

Sku是你的主要钥匙。您可以使用sku设置与变体表的外键关系。完全忘记生产。

创建表x(sku,price,description)主键sku