供应商-店铺模式下产品价格存储方案优化咨询：平衡数据冗余与一致性的可行方案探讨

这个问题其实挺常见的——既要避免重复数据占用额外存储空间，又要保证数据不会出现矛盾冲突，我们可以通过拆分价格存储逻辑的方式来完美解决，具体设计思路如下：

核心思路 #

把价格分为两类独立存储：

• 供应商统一定价：对应有供应商的店铺，只存储「供应商+产品」的唯一价格
• 店铺自主定价：对应无供应商的店铺，存储「店铺+产品」的唯一价格

同时利用数据库的约束和关联关系，从底层保证数据一致性。

具体表结构设计 #

1. 供应商产品价格表 (SupplierProductPrices) #

专门存储供应商给产品的统一定价，一个供应商对一个产品只会有一条有效记录：

CREATE TABLE SupplierProductPrices (
    id INT PRIMARY KEY AUTO_INCREMENT,
    id_supplier INT NOT NULL,
    id_product INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    -- 唯一索引：确保一个供应商对一个产品只能有一个定价
    UNIQUE KEY uk_supplier_product (id_supplier, id_product),
    -- 外键约束：关联供应商表，保证供应商ID合法
    FOREIGN KEY (id_supplier) REFERENCES Suppliers(id)
);

2. 店铺自主产品价格表 (ShopOwnProductPrices) #

专门存储无供应商店铺的自主定价，一个店铺对一个产品只会有一条有效记录：

CREATE TABLE ShopOwnProductPrices (
    id INT PRIMARY KEY AUTO_INCREMENT,
    id_shop INT NOT NULL,
    id_product INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    -- 唯一索引：确保一个店铺对一个产品只能有一个自主定价
    UNIQUE KEY uk_shop_product (id_shop, id_product),
    -- 外键约束：关联店铺表，保证店铺ID合法
    FOREIGN KEY (id_shop) REFERENCES Shops(id)
);

补充约束（可选） #

为了彻底避免数据不一致，还可以在业务逻辑层或者数据库层面（比如触发器、CHECK约束，取决于数据库支持）做限制：

• 只有Shops.supplier IS NULL的店铺，才能在ShopOwnProductPrices中插入记录
• 已经关联供应商的店铺，不允许在ShopOwnProductPrices中创建对应产品的价格

查询逻辑：获取所有店铺的产品展示价格 #

通过联合查询或CASE语句，可以一次性获取所有店铺的目标产品价格，同时标注价格来源：

SELECT 
    s.name AS shop_name,
    p.name AS product_name,
    -- 优先取供应商定价，没有则取自主定价
    CASE
        WHEN s.supplier IS NOT NULL THEN spp.price
        ELSE sopp.price
    END AS display_price,
    -- 标注价格来源
    CASE
        WHEN s.supplier IS NOT NULL THEN CONCAT('供应商', s.supplier, '定价')
        ELSE '店铺自主定价'
    END AS price_source
FROM Shops s
-- 关联供应商定价表：匹配店铺对应的供应商和目标产品
LEFT JOIN SupplierProductPrices spp 
    ON s.supplier = spp.id_supplier 
    AND spp.id_product = 34 -- 替换为你要查询的产品ID
-- 关联自主定价表：匹配店铺和目标产品
LEFT JOIN ShopOwnProductPrices sopp 
    ON s.id = sopp.id_shop 
    AND sopp.id_product = 34
-- 关联产品基础信息表（如果有的话）
LEFT JOIN Products p ON p.id = 34
-- 过滤掉没有价格的记录
WHERE spp.price IS NOT NULL OR sopp.price IS NOT NULL;

方案优势 #

1. 彻底避免数据冗余：供应商的定价只存储一次，不管关联多少店铺，不会像方案2那样产生大量重复记录
2. 强数据一致性：
   • 唯一索引确保「供应商+产品」「店铺+产品」的价格唯一，不会出现冲突数据
   • 供应商更新价格时，只需修改一条记录，所有关联店铺的价格自动同步，无需批量操作
   • 通过约束避免了方案1中「有供应商的店铺却存在自主定价」的错误情况
3. 结构清晰易维护：两类价格逻辑分开存储，后续扩展（比如加价格历史、促销价）也更方便

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