MariaDB下商品/变体/套餐/购物车SQL数据库结构设计咨询

原设计存在的核心问题 #

1. 数据冗余与一致性风险：cartItems中同时存储variant_id和bundle_id，无法保证一个购物车项只能是「单独变体」或「套餐」，容易出现无效数据（比如两个都填/都不填）；bundleItems中的product_id属于冗余字段，因为variant已经关联了product，直接通过variant_id关联即可获取商品信息。
2. 折扣逻辑分散：折扣类型和值重复出现在bundles、bundleItems、carts、cartItems多个表中，后续修改折扣规则（比如新增折扣类型）需要修改多处，维护成本高，且缺少统一的合法性约束。
3. 约束缺失：原设计未明确外键、唯一键等约束，容易出现关联数据不存在、重复添加同一商品/套餐的情况。

优化后的数据库结构 #

1. 基础商品表 #

保留你原有的核心结构，补充必要约束：

-- 商品主表
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL COMMENT '商品名称'
);

-- 商品变体表
CREATE TABLE variants (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL COMMENT '变体名称（如颜色、尺寸）',
    product_id INT NOT NULL COMMENT '关联商品ID',
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE KEY (product_id, name) COMMENT '同一商品下变体名称唯一'
);

2. 折扣规则表（抽象复用折扣逻辑） #

将分散的折扣字段抽离为单独表，统一管理折扣规则，避免重复代码：

CREATE TABLE discounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    discount_type ENUM('PERCENTAGE', 'ABSOLUTE') NOT NULL COMMENT '折扣类型：百分比/绝对值',
    discount_value INT NOT NULL COMMENT '折扣值（百分比存0-100，绝对值存金额分，避免浮点误差）',
    description VARCHAR(255) NULL COMMENT '折扣描述（可选，如"套餐专属优惠"）'
);

如果不需要复用折扣规则，也可以将discount_type和discount_value直接嵌入到需要的表中，但抽离后扩展性更强。

3. 套餐相关表 #

拆分套餐主信息与套餐包含的变体，支持套餐整体折扣和套餐内单个变体折扣：

-- 套餐主表
CREATE TABLE bundles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL COMMENT '套餐名称',
    discount_id INT NULL COMMENT '套餐整体折扣ID（可选）',
    FOREIGN KEY (discount_id) REFERENCES discounts(id) ON DELETE SET NULL
);

-- 套餐包含的变体关联表
CREATE TABLE bundle_variants (
    id INT PRIMARY KEY AUTO_INCREMENT,
    bundle_id INT NOT NULL COMMENT '关联套餐ID',
    variant_id INT NOT NULL COMMENT '关联变体ID',
    quantity INT NOT NULL DEFAULT 1 COMMENT '变体在套餐中的数量',
    discount_id INT NULL COMMENT '套餐内该变体的专属折扣ID（可选）',
    FOREIGN KEY (bundle_id) REFERENCES bundles(id) ON DELETE CASCADE,
    FOREIGN KEY (variant_id) REFERENCES variants(id) ON DELETE CASCADE,
    UNIQUE KEY (bundle_id, variant_id) COMMENT '同一套餐避免重复添加同一变体'
);

4. 购物车相关表 #

明确购物车项的类型（变体/套餐），通过约束保证数据一致性，支持购物车整体折扣和单个项折扣：

-- 购物车主表
CREATE TABLE carts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    discount_id INT NULL COMMENT '购物车整体折扣ID（可选）',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
);

-- 购物车项表
CREATE TABLE cart_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    cart_id INT NOT NULL COMMENT '关联购物车ID',
    item_type ENUM('VARIANT', 'BUNDLE') NOT NULL COMMENT '项类型：单独变体/套餐',
    variant_id INT NULL COMMENT '关联变体ID（仅当item_type=VARIANT时必填）',
    bundle_id INT NULL COMMENT '关联套餐ID（仅当item_type=BUNDLE时必填）',
    quantity INT NOT NULL DEFAULT 1 COMMENT '购买数量',
    discount_id INT NULL COMMENT '该购物车项的专属折扣ID（可选）',
    FOREIGN KEY (cart_id) REFERENCES carts(id) ON DELETE CASCADE,
    FOREIGN KEY (variant_id) REFERENCES variants(id) ON DELETE SET NULL,
    FOREIGN KEY (bundle_id) REFERENCES bundles(id) ON DELETE SET NULL,
    -- 约束：变体和套餐只能二选一
    CHECK (
        (item_type = 'VARIANT' AND variant_id IS NOT NULL AND bundle_id IS NULL)
        OR (item_type = 'BUNDLE' AND bundle_id IS NOT NULL AND variant_id IS NULL)
    ),
    -- 约束：同一购物车下同一变体/套餐只能添加一次
    UNIQUE KEY (cart_id, item_type, COALESCE(variant_id, bundle_id))
);

优化方案优势 #

• 数据一致性：通过CHECK约束和item_type字段，彻底避免购物车项同时关联变体和套餐的无效情况；外键约束保证关联数据的合法性。
• 减少冗余：去掉不必要的product_id字段，通过关联查询获取商品信息，降低数据存储和维护成本。
• 扩展性强：折扣规则集中管理，后续新增折扣类型、有效期等属性，只需修改discounts表即可；购物车和套餐的结构可轻松扩展其他属性（如套餐价格、购物车用户ID等）。
• 查询便捷：通过明确的关联关系，查询套餐包含的商品、购物车总金额等逻辑更清晰，减少复杂的条件判断。

内容的提问来源于stack exchange，提问作者Cristian-Alexandru SANDU