如何设计SQL表存储酒吧饮品每周库存更新数据？

你的初始表结构设计完全符合需求，是这类库存追踪场景的标准范式化设计，既能清晰分离静态数据与动态库存数据，又能高效支持你需要的查询需求，下面详细拆解并补充优化建议：

1. 核心表结构说明 #

drinks表（饮品基础信息表） #

这个表存储饮品的静态属性，结构合理：

CREATE TABLE drinks (
    id INT PRIMARY KEY AUTO_INCREMENT, -- 唯一标识，自增主键
    name VARCHAR(100) NOT NULL, -- 饮品名称，不可为空
    category VARCHAR(50), -- 品类（比如啤酒、鸡尾酒、软饮）
    price DECIMAL(10,2) NOT NULL -- 售价，保留两位小数
);

• 用id作为主键，避免名称重复或变更带来的关联问题
• 字段设置合理的约束（比如NOT NULL）保证数据完整性

stock表（周库存记录表） #

这个表存储动态的周库存数据，你的设计完全匹配需求：

CREATE TABLE stock (
    drink_id INT NOT NULL,
    week_start DATE NOT NULL, -- 周起始日期，建议统一用周一或周日作为周起始
    start_stock INT NOT NULL, -- 本周初始库存
    end_stock INT NOT NULL, -- 本周末库存
    total_sold INT NOT NULL, -- 本周总销量
    PRIMARY KEY (drink_id, week_start), -- 复合主键，确保同一饮品同一周只有一条记录
    FOREIGN KEY (drink_id) REFERENCES drinks(id) ON DELETE CASCADE -- 外键关联，保证数据一致性
);

• 复合主键(drink_id, week_start)是关键：杜绝同一饮品在同一周出现多条重复库存记录
• 外键约束确保stock表的drink_id必须对应drinks表中存在的饮品，避免无效数据

2. 关键优化建议 #

• 索引优化：如果需要频繁查询特定饮品的历史库存，或者按周统计所有饮品销量，可以给stock表添加索引：

  CREATE INDEX idx_stock_drink ON stock(drink_id);
  CREATE INDEX idx_stock_week ON stock(week_start);
  

• 数据一致性校验：如果你的库存逻辑是「总销量 = 初始库存 - 周末库存」（无损耗/中途补货的情况），可以添加检查约束保证数据合理性：

  ALTER TABLE stock ADD CONSTRAINT chk_sold CHECK (total_sold = start_stock - end_stock);
  

  若存在损耗或中途补货，可去掉该约束，改为在应用层做逻辑校验
• 可选扩展字段：如果需要更清晰的周范围记录，可以添加week_end DATE字段，或者通过week_start计算（比如DATE_ADD(week_start, INTERVAL 6 DAY)得到周日）

3. 示例查询 #

你需要的「特定饮品特定周的库存数据」查询示例：

SELECT d.name, s.week_start, s.start_stock, s.end_stock, s.total_sold
FROM drinks d
JOIN stock s ON d.id = s.drink_id
WHERE d.name = '莫吉托' AND s.week_start = '2024-05-20';

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