通用型产品变体组合数据库结构设计问询

核心调整思路 #

放弃原有的variants表，改用变体类型-变体值-组合关联的三层结构，既保持通用性（无需单独建size/shape/color表），又能支持任意数量的变体组合。

调整后的表结构 #

1. products（基础产品表）

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    base_price DECIMAL(10,2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

存储产品的基础信息，不包含变体相关内容。

2. variant_types（通用变体类型表）

CREATE TABLE variant_types (
    variant_type_id INT PRIMARY KEY AUTO_INCREMENT,
    type_name VARCHAR(50) NOT NULL UNIQUE, -- 比如"Size", "Color", "Shape"
    description VARCHAR(255)
);

统一管理所有变体类型，新增类型直接插入即可，无需修改表结构。

3. variant_values（变体值表）

CREATE TABLE variant_values (
    variant_value_id INT PRIMARY KEY AUTO_INCREMENT,
    variant_type_id INT NOT NULL,
    value_name VARCHAR(50) NOT NULL, -- 比如"M", "Blue", "Round"
    FOREIGN KEY (variant_type_id) REFERENCES variant_types(variant_type_id),
    UNIQUE KEY (variant_type_id, value_name) -- 同类型下值唯一
);

关联到具体的变体类型，存储该类型下的所有可选值。

4. product_variant_combinations（产品变体组合表）

CREATE TABLE product_variant_combinations (
    combination_id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    sku VARCHAR(100) UNIQUE,
    price DECIMAL(10,2), -- 可覆盖基础价格
    stock_quantity INT DEFAULT 0,
    is_default BOOLEAN DEFAULT FALSE, -- 标记无变体时的默认组合
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

每个记录代表一个具体的变体组合（比如"Blue + M T恤"），存储该组合的SKU、价格、库存等独立属性。无变体的产品可以创建一条is_default = TRUE的记录。

5. product_variant_combination_values（组合-变体值关联表）

CREATE TABLE product_variant_combination_values (
    combination_value_id INT PRIMARY KEY AUTO_INCREMENT,
    combination_id INT NOT NULL,
    variant_value_id INT NOT NULL,
    FOREIGN KEY (combination_id) REFERENCES product_variant_combinations(combination_id),
    FOREIGN KEY (variant_value_id) REFERENCES variant_values(variant_value_id),
    UNIQUE KEY (combination_id, variant_value_id) -- 一个组合中同一变体值不重复
);

通过这个关联表，实现一个组合对应多个变体值（比如同时关联"Blue"和"M"），完美支持多变体组合。

实际运作示例 #

1. 新增变体类型和值

   • 插入variant_types：(type_name: "Color")、(type_name: "Size")
   • 插入variant_values：(variant_type_id: 1, value_name: "Blue")、(variant_type_id: 1, value_name: "Red")、(variant_type_id: 2, value_name: "M")、(variant_type_id: 2, value_name: "XS")

2. 创建产品及变体组合

   • 插入products：(name: "T恤")
   • 插入product_variant_combinations：(product_id: 1, sku: "TSHIRT-BLUE-M", price: 99.00, stock_quantity: 50)
   • 插入product_variant_combination_values：(combination_id: 1, variant_value_id: 1)、(combination_id: 1, variant_value_id: 3)
   • 重复上述步骤创建"Red + XS"的组合

3. 无变体产品处理

   • 插入products：(name: "纯色马克杯")
   • 插入product_variant_combinations：(product_id: 2, sku: "MUG-PLAIN", price: 49.00, stock_quantity: 100, is_default: TRUE)
   • 无需插入product_variant_combination_values记录

查询示例 #

获取某产品的所有变体组合及对应值

SELECT 
    p.name AS product_name,
    pc.sku,
    pc.price,
    GROUP_CONCAT(CONCAT(vt.type_name, ': ', vv.value_name) SEPARATOR ', ') AS variant_details
FROM products p
JOIN product_variant_combinations pc ON p.product_id = pc.product_id
LEFT JOIN product_variant_combination_values pcv ON pc.combination_id = pcv.combination_id
LEFT JOIN variant_values vv ON pcv.variant_value_id = vv.variant_value_id
LEFT JOIN variant_types vt ON vv.variant_type_id = vt.variant_type_id
WHERE p.product_id = 1
GROUP BY pc.combination_id;

返回结果示例：

内容的提问来源于stack exchange，提问作者LazioTibijczyk