如何为 billing system 设计规范化的 Database schema

2026-10-24支付阿杰

结论

建议将数据拆成四个核心实体：

customers：客户
products：商品
bills：账单主表
bill_items：账单明细表

一张账单可以有多条明细，每条明细记录一种商品的购买数量和成交单价。不要在账单表中设置 apple_qty、banana_qty 之类的字段，也不要用逗号分隔的字符串保存多个商品或数量。

关系如下：

customers 1 ── N bills
bills     1 ── N bill_items
products  1 ── N bill_items

bills 和 products 是多对多关系，通过 bill_items 关联。

推荐的表结构

下面使用常见的关系型数据库语法。不同数据库的自增主键、时间类型和约束语法可能略有不同。

CREATE TABLE customers (
    customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_name VARCHAR(200) NOT NULL
);

CREATE TABLE products (
    product_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_name VARCHAR(200) NOT NULL,
    current_price DECIMAL(12, 2) NOT NULL,
    CONSTRAINT chk_product_price
        CHECK (current_price >= 0)
);

CREATE TABLE bills (
    bill_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    bill_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(30) NOT NULL DEFAULT 'DRAFT',

    CONSTRAINT fk_bills_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers (customer_id)
);

CREATE TABLE bill_items (
    bill_id BIGINT NOT NULL,
    line_no INTEGER NOT NULL,
    product_id BIGINT NOT NULL,
    quantity DECIMAL(12, 3) NOT NULL,
    unit_price DECIMAL(12, 2) NOT NULL,

    PRIMARY KEY (bill_id, line_no),

    CONSTRAINT fk_bill_items_bill
        FOREIGN KEY (bill_id)
        REFERENCES bills (bill_id)
        ON DELETE CASCADE,

    CONSTRAINT fk_bill_items_product
        FOREIGN KEY (product_id)
        REFERENCES products (product_id),

    CONSTRAINT chk_bill_item_quantity
        CHECK (quantity > 0),

    CONSTRAINT chk_bill_item_price
        CHECK (unit_price >= 0)
);

如果商品只能按整数销售，可以把 quantity 改为 INTEGER。如果包含按重量销售的商品，例如按千克计价的水果，使用 DECIMAL 更合适。

为什么要单独建立账单明细表

一张账单可以包含任意数量的商品，同一种商品也可以出现在多张账单中。只使用 bills 和 products 两张表，无法保存每次购买对应的信息，例如：

购买数量
成交单价
折扣
税率
商品在账单中的行号

这些信息描述的是“某张账单中的某个商品”，所以应保存在关联表 bill_items 中。

这种结构也符合常见的规范化要求：

每个字段只保存一个值，符合第一范式。
数量、成交价等明细属性依赖完整的明细键，符合第二范式。
客户资料和商品资料分别保存在各自的表中，避免非主键属性之间出现不必要的传递依赖，符合第三范式。

示例数据

先创建客户和商品：

INSERT INTO customers (customer_name)
VALUES ('张三');

INSERT INTO products (product_name, current_price)
VALUES
    ('苹果', 3.50),
    ('香蕉', 2.00),
    ('西瓜', 20.00);

假设生成的客户编号为 1，三个商品的编号依次为 1、2、3，接着创建账单及其明细：

INSERT INTO bills (customer_id, status)
VALUES (1, 'ISSUED');

-- 假设新账单的 bill_id 为 1
INSERT INTO bill_items (
    bill_id,
    line_no,
    product_id,
    quantity,
    unit_price
)
VALUES
    (1, 1, 1, 2, 3.50),
    (1, 2, 2, 3, 2.00),
    (1, 3, 3, 1, 20.00);

这三条明细表示：

2 个苹果，单价 3.50
3 根香蕉，单价 2.00
1 个西瓜，单价 20.00

账单和明细最好在同一个事务中创建，以免写入账单主表后操作中断，导致账单缺少明细。

查询完整账单

SELECT
    b.bill_id,
    b.bill_date,
    c.customer_name,
    bi.line_no,
    p.product_name,
    bi.quantity,
    bi.unit_price,
    bi.quantity * bi.unit_price AS line_total
FROM bills AS b
JOIN customers AS c
    ON c.customer_id = b.customer_id
JOIN bill_items AS bi
    ON bi.bill_id = b.bill_id
JOIN products AS p
    ON p.product_id = bi.product_id
WHERE b.bill_id = 1
ORDER BY bi.line_no;

整张账单的总金额可以这样计算：

SELECT
    bi.bill_id,
    SUM(bi.quantity * bi.unit_price) AS bill_total
FROM bill_items AS bi
WHERE bi.bill_id = 1
GROUP BY bi.bill_id;

成交价必须保存在账单明细中

即使 products 表已经有 current_price，bill_items 仍然需要保存 unit_price，因为商品价格会发生变化。

例如，苹果今天售价为 3.50，下个月调整为 4.00。如果历史账单只引用 products.current_price，再次查询旧账单时，金额也会随当前价格变化。

两个字段的用途不同：

products.current_price 表示当前参考售价。
bill_items.unit_price 表示开单时实际使用的价格。

历史交易还可能需要保留当时的商品名称、税率或折扣。是否将这些信息直接存入明细，取决于业务是否要求在商品资料变化后仍能完整还原账单。

是否限制同一商品只能出现一次

如果规定同一种商品在一张账单中只能有一行，可以增加唯一约束：

ALTER TABLE bill_items
ADD CONSTRAINT uq_bill_items_product
UNIQUE (bill_id, product_id);

这种情况下，也可以考虑把 (bill_id, product_id) 作为联合主键。

不过在实际计费中，同一种商品可能因为折扣、税率、批次或单价不同而出现多行。因此，使用 (bill_id, line_no) 作为主键通常更灵活。

不建议存储可计算总额

line_total 可以通过以下公式计算：

quantity × unit_price

通常没有必要把它作为普通字段重复保存。账单总额同样可以通过所有明细金额求和得到。重复存储容易造成数据不一致，例如修改数量后忘记同步更新总额。

如果因性能、审计、舍入规则或财务锁账要求必须保存总额，需要明确：

金额如何舍入
折扣和税费的计算顺序
哪个字段是最终权威值
明细变化时如何同步更新总额

其他注意事项

财务金额应使用 DECIMAL 或 NUMERIC，不要使用 FLOAT、REAL。
为外键列建立索引，例如 bills.customer_id、bill_items.product_id。部分数据库不会自动为外键创建索引。
已开具或已支付的账单通常不应直接修改，可以通过作废、贷项单或冲销记录处理。
如果系统支持多币种，应在账单层保存 currency_code，并保证同一张账单中的金额使用同一种币种。
客户姓名、地址等资料可能发生变化。如果需要保留开票时的信息，应在账单中保存相应快照，不能只引用当前客户资料。
删除商品时，不要级联删除历史账单明细。通常应将商品标记为停用，同时保留历史记录。