SQL Server 中如何设计客户 Credit、Debit 和 Balance 记录

结论

不要为每个客户单独建账务表。所有客户的应收、付款和调整记录都应保存在同一张客户明细账表中，通过 CustomerID 区分，并按实际查询方式建立索引。

也不建议在每笔记录产生后直接保存 Balance。余额是累计计算结果，补录、冲销或并发写入都可能造成数据不一致。更合适的做法是保存不可变的借贷流水，在查询时计算余额。数据量很大且频繁计算余额时，可以增加余额汇总表。

推荐的数据模型

POS 系统至少可以拆分为以下几个部分：

• Customer：客户资料。
• Invoice：发票头信息。
• InvoiceLine：发票商品明细。
• Payment：客户实际支付的款项。
• PaymentAllocation：付款与发票之间的核销关系。
• CustomerLedger：客户应收账款流水。

客户明细账可以采用以下记账方式：

• 开具赊销发票时记入 Debit，客户欠款增加。
• 收到付款时记入 Credit，客户欠款减少。
• 余额为 SUM(Debit - Credit)。

系统也可以采用其他会计口径，但必须在所有模块中保持一致。

表结构示例

Transaction 在 SQL 中容易产生歧义，建议改用含义更明确的 CustomerLedger。

CREATE TABLE Customer (
    CustomerID      bigint IDENTITY PRIMARY KEY,
    CustomerName    nvarchar(200) NOT NULL
);

CREATE TABLE Invoice (
    InvoiceID       bigint IDENTITY PRIMARY KEY,
    InvoiceDate     datetime2(0) NOT NULL,
    CustomerID      bigint NOT NULL,
    Comments        nvarchar(1000) NULL,

    CONSTRAINT FK_Invoice_Customer
        FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);

CREATE TABLE InvoiceLine (
    InvoiceID       bigint NOT NULL,
    LineItemNo      int NOT NULL,
    ProductID       bigint NOT NULL,
    Qty             decimal(18, 3) NOT NULL,
    UnitPrice       decimal(19, 4) NOT NULL,

    CONSTRAINT PK_InvoiceLine
        PRIMARY KEY (InvoiceID, LineItemNo),

    CONSTRAINT FK_InvoiceLine_Invoice
        FOREIGN KEY (InvoiceID) REFERENCES Invoice(InvoiceID),

    CONSTRAINT CK_InvoiceLine_Qty
        CHECK (Qty > 0),

    CONSTRAINT CK_InvoiceLine_UnitPrice
        CHECK (UnitPrice >= 0)
);

CREATE TABLE CustomerLedger (
    EntryID         bigint IDENTITY PRIMARY KEY,
    CustomerID      bigint NOT NULL,
    EntryDate       datetime2(0) NOT NULL,
    EntryType       varchar(20) NOT NULL,
    SourceID        bigint NOT NULL,
    Debit           decimal(19, 4) NOT NULL DEFAULT 0,
    Credit          decimal(19, 4) NOT NULL DEFAULT 0,
    Description     nvarchar(500) NULL,

    CONSTRAINT FK_CustomerLedger_Customer
        FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),

    CONSTRAINT CK_CustomerLedger_EntryType
        CHECK (EntryType IN ('INVOICE', 'PAYMENT', 'CREDIT_NOTE', 'ADJUSTMENT')),

    CONSTRAINT CK_CustomerLedger_Amount
        CHECK (
            (Debit > 0 AND Credit = 0)
            OR
            (Credit > 0 AND Debit = 0)
        ),

    CONSTRAINT UQ_CustomerLedger_Source
        UNIQUE (EntryType, SourceID)
);

SourceID 指向生成这条流水的业务记录，例如 InvoiceID 或 PaymentID。普通外键无法根据 EntryType 动态引用不同的表，所以这层关联需要由应用程序或存储过程维护。

如果希望所有引用都由外键约束，也可以设置多个可空列，例如 InvoiceID、PaymentID 和 CreditNoteID，再通过 CHECK 约束确保每条记录只能填写其中一个。

单独保存付款和核销记录

MethodOfPayment 不适合只保存在 Invoice 中。一张发票可能分多次付款，一次付款也可能同时使用现金、银行卡等多种方式。可以为此增加付款表：

CREATE TABLE Payment (
    PaymentID       bigint IDENTITY PRIMARY KEY,
    CustomerID      bigint NOT NULL,
    PaymentDate     datetime2(0) NOT NULL,
    Amount          decimal(19, 4) NOT NULL,
    PaymentMethod   varchar(30) NOT NULL,
    ReferenceNo     nvarchar(100) NULL,

    CONSTRAINT FK_Payment_Customer
        FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),

    CONSTRAINT CK_Payment_Amount
        CHECK (Amount > 0)
);

CREATE TABLE PaymentAllocation (
    PaymentID       bigint NOT NULL,
    InvoiceID       bigint NOT NULL,
    AllocatedAmount decimal(19, 4) NOT NULL,

    CONSTRAINT PK_PaymentAllocation
        PRIMARY KEY (PaymentID, InvoiceID),

    CONSTRAINT FK_Allocation_Payment
        FOREIGN KEY (PaymentID) REFERENCES Payment(PaymentID),

    CONSTRAINT FK_Allocation_Invoice
        FOREIGN KEY (InvoiceID) REFERENCES Invoice(InvoiceID),

    CONSTRAINT CK_Allocation_Amount
        CHECK (AllocatedAmount > 0)
);

这种结构既支持一笔付款结清多张发票，也支持一张发票分期付款。

查询客户当前余额

SELECT
    CustomerID,
    SUM(Debit - Credit) AS CurrentBalance
FROM CustomerLedger
WHERE CustomerID = @CustomerID
GROUP BY CustomerID;

客户没有任何流水时，这条查询可能不会返回记录。如果无论是否存在流水都需要返回余额，可以使用：

SELECT
    COALESCE(SUM(Debit - Credit), 0) AS CurrentBalance
FROM CustomerLedger
WHERE CustomerID = @CustomerID;

查询带逐笔余额的历史记录

在 SQL Server 中，可以使用窗口函数计算每笔交易完成后的余额：

SELECT
    EntryID,
    EntryDate,
    EntryType,
    SourceID,
    Debit,
    Credit,
    SUM(Debit - Credit) OVER (
        PARTITION BY CustomerID
        ORDER BY EntryDate, EntryID
        ROWS UNBOUNDED PRECEDING
    ) AS RunningBalance
FROM CustomerLedger
WHERE CustomerID = @CustomerID
ORDER BY EntryDate, EntryID;

当多条记录的时间相同时，EntryID 可以提供稳定的排序。如果查询限定了日期范围，还要把起始日期之前的余额作为期初余额，否则窗口函数计算的只是筛选范围内的累计值。

索引和数据量

在一张表中保存数百万甚至更多流水是关系数据库的正常用法，本身不代表设计有问题。索引需要与查询方式匹配：

CREATE INDEX IX_CustomerLedger_Customer_Date
ON CustomerLedger (CustomerID, EntryDate, EntryID)
INCLUDE (EntryType, SourceID, Debit, Credit);

这个索引适合查询单个客户的账务历史和当前余额。只有当数据规模、保留周期或实际查询性能出现问题时，才需要考虑按日期分区、归档历史数据或维护余额汇总表。没有必要一开始就按客户拆表。

发票状态的处理

InvoiceStatus 描述的是发票，不是账务流水，因此不应放在客户流水记录中。它可以保存在 Invoice 表中，但缓存的状态可能与付款数据不一致。

更可靠的方式是根据发票总额和已核销金额判断状态：

• 已核销金额为 0：未付款。
• 已核销金额小于发票金额：部分付款。
• 已核销金额等于发票金额：已结清。

如果为了提高查询速度而保存状态，那么创建付款、撤销付款或调整核销时，必须在同一个数据库事务中同步更新状态。

实现时需要注意

• 使用 decimal 保存金额，不要使用 float 或 real。
• Invoice 中的 UnitPrice 应保存销售发生时的价格，不能只依赖产品表中的当前价格。
• 发票、付款、核销和账务流水应在同一个 SQL 事务中写入。
• 已过账的流水尽量不要直接修改或删除。应通过冲销记录进行纠正，以保留审计历史。
• 使用唯一约束，防止同一张发票或同一笔付款被重复记账。
• 现金散客可以共用一条名为 “Walk-in Customer” 的客户记录。需要完整审计时，可以同时记录发票借方和现金付款贷方，使最终余额归零。
• 如果需要频繁查询大量客户的余额，可以维护一张每位客户一行的余额汇总表，但账务事实仍以流水表为准。

备注：内容仅供参考。