本文介绍一种更健壮的发票数据库设计方案，通过引入销售主表（sales）作为核心枢纽，分离交易行为、客户信息与商品明细，确保每日商品收入统计准确，同时支持定金、价格快照、分项折扣等业务需求。
本文介绍一种更健壮的发票数据库设计方案，通过引入销售主表（sales）作为核心枢纽，分离交易行为、客户信息与商品明细，确保每日商品收入统计准确，同时支持定金、价格快照、分项折扣等业务需求。
在当前结构中，将 invoices 表直接承载订单总览（如 deposit、rest_price_to_pay）与 invoice_items 表存储明细看似简洁，但存在根本性缺陷：它混淆了“交易行为”与“财务结算状态”。例如，当客户仅支付定金（status = 3）时，invoice_items.price 记录的是商品标价，而非实际已确认收入；若后续价格变动或需按历史单价开票，原始数据无法回溯——这导致每日按商品统计真实收入（即“已确认归属该商品的实收金额”）完全失真。
✅ 正确解法是采用 “销售主表（sales）+ 明细快照（sale_items）+ 结算记录（payments）”三层模型：
-- 1. 销售主表：记录一次完整销售行为（无论是否付清）
CREATE TABLE sales (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
date DATETIME NOT NULL,
customer_id INT, -- 可选，关联客户
status ENUM('draft', 'confirmed', 'partially_paid', 'paid') DEFAULT 'confirmed',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 2. 销售明细表：每行保存当时快照化的商品信息（关键！）
CREATE TABLE sale_items (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
sale_id BIGINT NOT NULL,
service_id INT NOT NULL,
quantity DECIMAL(10,2) NOT NULL,
unit_price DECIMAL(10,2) NOT NULL, -- ✅ 快照价格，永不变更
discount_amount DECIMAL(10,2) DEFAULT 0.00, -- 支持单品折扣
tax_rate DECIMAL(5,4) DEFAULT 0.00,
FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE CASCADE
);
-- 3. 支付记录表：独立追踪每一笔资金流入（含定金、尾款等）
CREATE TABLE payments (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
sale_id BIGINT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
payment_type ENUM('deposit', 'balance', 'full') NOT NULL,
paid_at DATETIME NOT NULL,
FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE RESTRICT
);? 为什么此模型解决你的问题？
- 每日商品收入精准可查：只需聚合 sale_items 中对应日期的 unit_price × quantity，再按 service_id 分组——该值恒等于客户当时确认购买的商品价值，与后续是否付款、付多少无关；
- 定金不再干扰统计：payments 表单独记录资金流，sale_items 专注商品交付承诺，二者解耦；
- 历史价格可追溯：unit_price 在 sale_items 中固化，即使 services 表中价格调整，旧发票仍能精确还原；
- 灵活支持复杂场景：如分次支付、部分退款、多币种结算，均可在 payments 表扩展字段实现，不影响核心商品逻辑。
⚠️ 迁移与使用提示：
- 现有 invoices 表应重构为 sales 表（保留 id, date, status 等核心字段），原 invoice_items 迁移至 sale_items 并补全 unit_price 快照值；
- deposit、rest_price_to_pay 等字段从主表移除，由应用层根据 sale_items 汇总 + payments 实际到账动态计算；
- 日报查询示例（MySQL）：
SELECT si.service_id, DATE(s.date) as sale_date, SUM(si.quantity * si.unit_price) as daily_item_revenue FROM sales s JOIN sale_items si ON s.id = si.sale_id WHERE s.date >= '2024-01-01' AND s.date < '2024-01-02' GROUP BY si.service_id, DATE(s.date);
该模型虽增加一张表，却换来数据语义清晰、业务扩展性强、统计结果可信——这才是支撑长期增长的发票系统基石。
