如何设计支持多种 billing option 的 database schema？

明确结论

可以分别为 Credit Card、House Account 和 COD 建立明细表。不过，应该先建立一张统一的账单主表，再让各类型明细表使用主表主键作为自己的主键和外键。

不建议在 ORDERS 中增加一个含义随 billingoption_id 变化的 billing_data_id。这种“多态外键”无法通过普通 foreign key 约束它究竟引用哪张表，很容易产生无效引用。

推荐使用以下关系：

ORDERS
  └── ORDER_BILLING
        ├── CREDIT_CARD_BILLING
        ├── HOUSE_ACCOUNT_BILLING
        └── COD_BILLING

如果每个订单只有一种结算方式，可以为 ORDER_BILLING.order_id 添加唯一约束。如果一个订单可能拆分付款或多次扣款，则应改为一对多关系。

为什么要分成主表和明细表

三种 billing option 有一些共同属性：

• 所属订单
• 应收金额
• 币种
• 支付状态
• 创建时间
• 完成时间

它们也各有不同的数据：

• Credit Card：支付平台、token、卡号末四位
• House Account：内部账户、账期或授权信息
• COD：代收金额、物流单号、签收状态

如果把所有字段都放进一张表，会产生大量可空列，也很难约束每种类型只能填写自己的字段。如果完全拆成三张互不相关的表，查询、状态管理和外键设计又会变得复杂。

采用“统一主表 + 类型明细表”，可以同时处理公共字段、类型差异和引用完整性。

推荐的表结构

以下示例使用 PostgreSQL 风格的 SQL。其他数据库可能需要调整 identity、时间类型和约束语法。

CREATE TABLE billing_options (
    billing_option_code VARCHAR(30) PRIMARY KEY
);

INSERT INTO billing_options (billing_option_code)
VALUES
    ('CREDIT_CARD'),
    ('HOUSE_ACCOUNT'),
    ('COD');

CREATE TABLE orders (
    order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

账单主表保存所有结算方式共有的数据：

CREATE TABLE order_billing (
    billing_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id BIGINT NOT NULL,
    billing_option_code VARCHAR(30) NOT NULL,
    amount NUMERIC(12, 2) NOT NULL CHECK (amount >= 0),
    currency CHAR(3) NOT NULL,
    billing_status VARCHAR(30) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL,

    CONSTRAINT fk_order_billing_order
        FOREIGN KEY (order_id)
        REFERENCES orders (order_id),

    CONSTRAINT fk_order_billing_option
        FOREIGN KEY (billing_option_code)
        REFERENCES billing_options (billing_option_code),

    CONSTRAINT uq_order_billing_type
        UNIQUE (billing_id, billing_option_code),

    CONSTRAINT uq_one_billing_per_order
        UNIQUE (order_id)
);

uq_one_billing_per_order 表示一个订单只能有一条账单记录。如果业务允许拆单支付、组合支付或多次结算，应删除这个约束。

Credit Card 明细

CREATE TABLE credit_card_billing (
    billing_id BIGINT PRIMARY KEY,
    billing_option_code VARCHAR(30)
        NOT NULL DEFAULT 'CREDIT_CARD'
        CHECK (billing_option_code = 'CREDIT_CARD'),

    payment_provider VARCHAR(50) NOT NULL,
    payment_token VARCHAR(255) NOT NULL,
    card_brand VARCHAR(30),
    card_last_four CHAR(4),
    provider_transaction_id VARCHAR(100),

    CONSTRAINT fk_credit_card_billing
        FOREIGN KEY (billing_id, billing_option_code)
        REFERENCES order_billing (billing_id, billing_option_code)
);

House Account 明细

建议把 house account 建成独立实体，不要直接把账号文本写入订单。

CREATE TABLE house_accounts (
    house_account_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    account_number VARCHAR(50) NOT NULL UNIQUE,
    account_status VARCHAR(30) NOT NULL,
    credit_limit NUMERIC(12, 2)
);

CREATE TABLE house_account_billing (
    billing_id BIGINT PRIMARY KEY,
    billing_option_code VARCHAR(30)
        NOT NULL DEFAULT 'HOUSE_ACCOUNT'
        CHECK (billing_option_code = 'HOUSE_ACCOUNT'),

    house_account_id BIGINT NOT NULL,
    authorization_reference VARCHAR(100),

    CONSTRAINT fk_house_account_billing
        FOREIGN KEY (billing_id, billing_option_code)
        REFERENCES order_billing (billing_id, billing_option_code),

    CONSTRAINT fk_house_account
        FOREIGN KEY (house_account_id)
        REFERENCES house_accounts (house_account_id)
);

COD 明细

如果 COD 没有任何专属数据，可以不建明细表，只在 order_billing 中记录类型。如果需要记录代收和物流信息，则可以建立：

CREATE TABLE cod_billing (
    billing_id BIGINT PRIMARY KEY,
    billing_option_code VARCHAR(30)
        NOT NULL DEFAULT 'COD'
        CHECK (billing_option_code = 'COD'),

    collection_amount NUMERIC(12, 2)
        NOT NULL CHECK (collection_amount >= 0),
    carrier_name VARCHAR(100),
    tracking_number VARCHAR(100),
    collected_at TIMESTAMP NULL,

    CONSTRAINT fk_cod_billing
        FOREIGN KEY (billing_id, billing_option_code)
        REFERENCES order_billing (billing_id, billing_option_code)
);

这里使用复合 foreign key，可以防止把 CREDIT_CARD 类型的账单错误写入 cod_billing。

创建账单记录的方式

以信用卡结算为例，主表和明细表应在同一个事务中写入：

BEGIN;

INSERT INTO order_billing (
    order_id,
    billing_option_code,
    amount,
    currency,
    billing_status
)
VALUES (
    10001,
    'CREDIT_CARD',
    299.00,
    'CNY',
    'PENDING'
)
RETURNING billing_id;

-- 假设返回 billing_id = 501
INSERT INTO credit_card_billing (
    billing_id,
    payment_provider,
    payment_token,
    card_brand,
    card_last_four
)
VALUES (
    501,
    'example_provider',
    'provider_generated_token',
    'VISA',
    '1234'
);

COMMIT;

事务可以避免出现只写入主表、没有写入对应明细表的半成品数据。

是否还要在 ORDERS 中保留 billingoption_id

通常不需要。结算方式已经记录在 order_billing.billing_option_code 中，如果再存入 orders，就会产生重复数据，两个字段也可能不一致。

如果经常需要从订单查询结算方式，可以使用关联查询：

SELECT
    o.order_id,
    b.billing_option_code,
    b.amount,
    b.currency,
    b.billing_status
FROM orders AS o
JOIN order_billing AS b
    ON b.order_id = o.order_id
WHERE o.order_id = 10001;

只有在明确接受数据冗余，并且能够通过数据库约束或统一写入流程保证同步时，才应考虑在 orders 中重复保存该字段。

需要注意的问题

不要保存完整信用卡数据

一般只应保存支付服务商提供的 token、卡品牌和末四位等信息，不要直接保存 CVV。是否允许保存完整卡号，以及如何加密、审计和限制访问，取决于适用的 PCI DSS 范围和支付服务商方案。

账单方式和支付交易不是同一个概念

一次信用卡结算可能经历授权、扣款、失败、重试和退款。不要通过不断覆盖 order_billing 来记录这些过程，最好另建交易流水表：

CREATE TABLE billing_transactions (
    transaction_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    billing_id BIGINT NOT NULL,
    transaction_type VARCHAR(30) NOT NULL,
    transaction_status VARCHAR(30) NOT NULL,
    amount NUMERIC(12, 2) NOT NULL,
    provider_reference VARCHAR(100),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_billing_transaction
        FOREIGN KEY (billing_id)
        REFERENCES order_billing (billing_id)
);

“恰好存在一条对应明细”需要额外保证

普通 foreign key 可以防止写入错误类型的明细记录，但无法单独保证每条 order_billing 必然存在且只存在一种明细。这个条件可以通过事务和应用层校验来保证。如果所有写入入口都由数据库控制，也可以使用 deferred constraint trigger 进行严格校验。

是否需要为 COD 建表取决于实际字段

不要为了结构统一而强行建立空表。只有当 COD 确实包含代收金额、承运商、物流单号或收款时间等专属数据时，cod_billing 才有实际价值。

总体而言，ORDERS → ORDER_BILLING → 各类型明细表 是较为稳妥的设计。它既能避免多态 foreign key，也方便以后增加新的结算方式、支付状态和交易流水。

备注：内容仅供参考。