reservation 与 incidentals billing 数据库 schema 设计

结论

不要在 payment 表里用一个简单字段区分“这笔钱属于 reservation 还是 incidentals”。更合适的设计是：

• reservation 保存预订信息；
• charge 统一记录房费、税费、餐饮、损坏赔偿等应收项目；
• payment 只记录实际收款；
• payment_allocation 记录每笔付款冲抵了哪些费用。

这种设计既允许一笔付款同时支付房费和杂费，也允许多笔付款共同支付同一项费用，还能处理押金、部分付款和退款。

为什么不建议直接给 payment 分类

如果在 payment 表中加入：

payment_type ENUM('reservation', 'incidental')

这种做法看起来简单，却隐含了“一笔付款只能有一种用途”的限制。实际业务中经常会出现以下情况：

• 客人一次支付 1,200 元，其中 1,000 元是房费，200 元是餐饮费；
• 一笔押金在入住后分别用于抵扣房费和损坏赔偿；
• 同一笔账单由现金和信用卡共同支付；
• 收到付款时，还没有确定最终要冲抵哪些项目；
• 退款只涉及某一项费用，而不是整笔付款。

因此，付款的“用途”不应由 payment 自身决定，而应由付款与应收费用之间的分配关系决定。

推荐的数据模型

核心关系可以设计为：

reservation
    └── folio
          └── charge

payment
    └── payment_allocation ── charge

其中：

• folio 表示某次住宿或某位付款人的账务账户；
• charge 表示应收明细；
• payment 表示实际到账的资金；
• payment_allocation 表示付款与费用之间的冲抵关系。

业务规模较小时，可以暂时省略 folio，让 charge 和 payment 直接关联 reservation。不过，一旦需要拆账、公司代付、同行客人分别结算或生成多个账单，加入 folio 会更方便扩展。

Schema 示例

以下示例使用 PostgreSQL。其他数据库需要相应调整数据类型和约束语法。

CREATE TABLE reservation (
    reservation_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    guest_id       BIGINT NOT NULL,
    check_in_date  DATE NOT NULL,
    check_out_date DATE NOT NULL,
    status         VARCHAR(30) NOT NULL,
    CHECK (check_out_date > check_in_date)
);

CREATE TABLE folio (
    folio_id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    reservation_id BIGINT NOT NULL
        REFERENCES reservation(reservation_id),
    status         VARCHAR(20) NOT NULL DEFAULT 'open',
    created_at     TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE charge (
    charge_id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    folio_id       BIGINT NOT NULL
        REFERENCES folio(folio_id),
    charge_type    VARCHAR(30) NOT NULL,
    description    VARCHAR(255),
    amount         NUMERIC(12, 2) NOT NULL,
    currency       CHAR(3) NOT NULL,
    occurred_at    TIMESTAMPTZ NOT NULL,
    status         VARCHAR(20) NOT NULL DEFAULT 'posted',
    CHECK (amount > 0),
    CHECK (
        charge_type IN (
            'room',
            'tax',
            'food',
            'minibar',
            'parking',
            'damage',
            'other'
        )
    )
);

CREATE TABLE payment (
    payment_id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    folio_id       BIGINT
        REFERENCES folio(folio_id),
    amount         NUMERIC(12, 2) NOT NULL,
    currency       CHAR(3) NOT NULL,
    payment_method VARCHAR(30) NOT NULL,
    status         VARCHAR(20) NOT NULL,
    provider_ref   VARCHAR(100),
    received_at    TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CHECK (amount > 0),
    CHECK (
        status IN (
            'pending',
            'authorized',
            'captured',
            'failed',
            'refunded',
            'voided'
        )
    )
);

CREATE TABLE payment_allocation (
    payment_id      BIGINT NOT NULL
        REFERENCES payment(payment_id),
    charge_id       BIGINT NOT NULL
        REFERENCES charge(charge_id),
    allocated_amount NUMERIC(12, 2) NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (payment_id, charge_id),
    CHECK (allocated_amount > 0)
);

charge_type = 'room' 表示 reservation 产生的房费，food、minibar、parking 等表示 incidentals。付款本身不必区分这两类用途。

一笔付款同时支付房费和杂费

假设：

• charge_id = 101 是 1,000 元房费；
• charge_id = 102 是 200 元餐饮费；
• payment_id = 501 是一笔 1,200 元的付款。

可以写入两条分配记录：

INSERT INTO payment_allocation (
    payment_id,
    charge_id,
    allocated_amount
) VALUES
    (501, 101, 1000.00),
    (501, 102, 200.00);

查询这笔付款冲抵了哪些项目：

SELECT
    p.payment_id,
    p.amount AS payment_amount,
    c.charge_type,
    c.description,
    pa.allocated_amount
FROM payment AS p
JOIN payment_allocation AS pa
    ON pa.payment_id = p.payment_id
JOIN charge AS c
    ON c.charge_id = pa.charge_id
WHERE p.payment_id = 501;

如果只需要计算其中有多少金额用于 incidentals：

SELECT COALESCE(SUM(pa.allocated_amount), 0) AS incidental_amount
FROM payment_allocation AS pa
JOIN charge AS c
    ON c.charge_id = pa.charge_id
WHERE pa.payment_id = 501
  AND c.charge_type IN (
      'food',
      'minibar',
      'parking',
      'damage',
      'other'
  );

余额的计算方式

账务余额通常不应保存为可随意修改的独立字段，而应根据费用和付款分配记录计算。

查询某个 folio 中每项费用的余额：

SELECT
    c.charge_id,
    c.charge_type,
    c.amount AS charge_amount,
    COALESCE(SUM(pa.allocated_amount), 0) AS paid_amount,
    c.amount - COALESCE(SUM(pa.allocated_amount), 0) AS balance
FROM charge AS c
LEFT JOIN payment_allocation AS pa
    ON pa.charge_id = c.charge_id
WHERE c.folio_id = :folio_id
  AND c.status = 'posted'
GROUP BY
    c.charge_id,
    c.charge_type,
    c.amount;

执行分配操作时，还要在事务中验证：

SUM(payment_allocation.allocated_amount) <= payment.amount
SUM(payment_allocation.allocated_amount for one charge) <= charge.amount
payment.currency = charge.currency
payment 与 charge 属于允许互相结算的 folio

普通 CHECK 约束无法可靠检查跨行合计。这些规则通常需要通过事务、触发器或受控的存储过程实现，同时锁定相关记录，防止并发操作造成超额分配。

押金和信用卡预授权要单独处理

押金、信用卡预授权和实际收款是不同的概念：

• authorized：额度已经冻结，但资金尚未正式入账；
• captured：资金已经实际扣取；
• voided：预授权已经撤销；
• refunded：已经发生退款。

支付流程较复杂时，可以拆分为 payment_intent、payment_transaction 和 payment_allocation。其中，payment_transaction 保存 authorization、capture、refund 等支付网关操作。尚未 capture 的预授权不能直接计入已支付金额。

退款与冲正

不要直接删除原付款或调小原付款金额，否则会丢失审计轨迹。可以单独建立退款表：

CREATE TABLE refund (
    refund_id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    original_payment_id BIGINT NOT NULL
        REFERENCES payment(payment_id),
    amount             NUMERIC(12, 2) NOT NULL,
    currency           CHAR(3) NOT NULL,
    status             VARCHAR(20) NOT NULL,
    provider_ref       VARCHAR(100),
    refunded_at        TIMESTAMPTZ,
    CHECK (amount > 0)
);

如果需要知道退款对应哪项费用，还可以增加 refund_allocation，作用与 payment_allocation 类似。

误记的费用也应通过 voided 状态或反向调整记录处理，不要物理删除已经入账的数据。

设计时还要注意

• 金额应使用 NUMERIC 或最小货币单位的整数，不要使用 FLOAT、REAL。
• 每条费用和付款都要保存 currency。不同币种不能直接分配，除非另有汇率和换汇记录。
• 最好为 provider_ref 建立唯一索引，防止支付网关回调造成重复入账。
• charge_type 用来表示费用性质比笼统的 is_incidental 更合适。
• 订单状态、费用状态和支付状态应分别保存，不能共用一个状态字段。
• 已经入账的财务记录应尽量保持不可变。需要修正时，应保留冲正或退款记录。
• 高频余额查询可以缓存汇总结果，但最终账务仍应以明细记录为准。
• 如果某项消费有独立的业务信息，例如餐饮订单或停车记录，可以让 charge 保存来源类型和来源 ID，也可以让具体业务表引用 charge_id。

只有当业务能够明确保证“一笔付款永远只支付一种费用”时，才适合在 payment 中直接保存 purpose。reservation billing 经常涉及合并付款、押金抵扣和部分退款，因此使用 charge + payment + payment_allocation 会更可靠。

备注：内容仅供参考。