PAYATHON 2026

Electronic Bill Payment System 数据库设计与查询

支付阿杰

结论

建议把“客户订阅配置”和“每月实际账单”分开存储：

  • 订阅表记录客户订阅的公用事业公司，以及每月的计划生成日、截止日和付款到期日。
  • 账单表记录某个客户在具体账期内生成的账单，包括金额、状态和实际生成时间。

界面加载时，根据当前日期查询“已到生成日，但本账期还没有生成账单”的订阅即可。对于 100 多名客户，这类查询的负担很小，一般不需要定时任务。如果业务要求提前创建任务、发送提醒，或者在无人登录时继续处理，才需要数据库 Job 或后台调度服务。

推荐的数据模型

不要再让 Bill_Master 同时承担订阅规则和实际账单两种职责。可以把数据拆成以下几张表。

客户表

CREATE TABLE Customer (
    Customer_ID     BIGINT PRIMARY KEY,
    Customer_Name   VARCHAR(200) NOT NULL,
    Address         VARCHAR(500),
    Phone           VARCHAR(50)
);

公用事业公司表

CREATE TABLE UtilityCompany (
    UtilityCompany_ID   BIGINT PRIMARY KEY,
    Company_Name        VARCHAR(200) NOT NULL
);

客户账单订阅表

这张表保存长期有效的配置，不代表某个月的实际账单。

CREATE TABLE BillSubscription (
    Subscription_ID     BIGINT PRIMARY KEY,
    Customer_ID         BIGINT NOT NULL,
    UtilityCompany_ID   BIGINT NOT NULL,
    Enrollment_Date     DATE NOT NULL,

    Generation_Day      SMALLINT NOT NULL,
    Due_Day             SMALLINT NOT NULL,
    CutOff_Day          SMALLINT NOT NULL,

    Is_Active            BOOLEAN NOT NULL DEFAULT TRUE,
    End_Date             DATE NULL,

    CONSTRAINT FK_Subscription_Customer
        FOREIGN KEY (Customer_ID)
        REFERENCES Customer (Customer_ID),

    CONSTRAINT FK_Subscription_Utility
        FOREIGN KEY (UtilityCompany_ID)
        REFERENCES UtilityCompany (UtilityCompany_ID),

    CONSTRAINT CK_Subscription_GenerationDay
        CHECK (Generation_Day BETWEEN 1 AND 31),

    CONSTRAINT CK_Subscription_DueDay
        CHECK (Due_Day BETWEEN 1 AND 31),

    CONSTRAINT CK_Subscription_CutOffDay
        CHECK (CutOff_Day BETWEEN 1 AND 31)
);

这里使用 Generation_Day，而不是容易让人误解的 Bill_Generation_Date。如果该字段表示“每月 18 日”，它保存的是重复规则中的日号，而不是完整日期。

如果同一客户对同一家公用事业公司只能有一个有效订阅，还要添加对应的唯一约束。如果需要保留配置变更历史，可以增加 Effective_From 和 Effective_To，不要直接覆盖旧配置。

实际账单表

每个账期生成一条独立的账单记录。

CREATE TABLE Bill (
    Bill_ID              BIGINT PRIMARY KEY,
    Subscription_ID      BIGINT NOT NULL,

    Billing_Period_Start DATE NOT NULL,
    Billing_Period_End   DATE NOT NULL,
    Scheduled_Date       DATE NOT NULL,
    Due_Date             DATE NOT NULL,
    CutOff_Date           DATE NOT NULL,

    Amount                DECIMAL(18, 2) NOT NULL,
    Bill_Reference        VARCHAR(100),
    Status                VARCHAR(30) NOT NULL DEFAULT 'GENERATED',
    Generated_At          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    Paid_At               TIMESTAMP NULL,

    CONSTRAINT FK_Bill_Subscription
        FOREIGN KEY (Subscription_ID)
        REFERENCES BillSubscription (Subscription_ID),

    CONSTRAINT UQ_Bill_Subscription_Period
        UNIQUE (Subscription_ID, Billing_Period_Start)
);

UNIQUE (Subscription_ID, Billing_Period_Start) 可以防止用户重复点击，或者两个操作员同时处理时，为同一订阅和同一账期生成两张账单。

生产环境中还可以增加：

  • Created_By
  • Updated_At
  • Currency_Code
  • Payment_Status
  • Failure_Reason
  • 外部公用事业公司的账单编号

查询当天需要生成的账单

以下示例基于三个假设：

  • Generation_Day 表示每月可以开始录入账单的日期。
  • 每个自然月对应一个账期。
  • 到达生成日后，只要当月账单尚未创建，该记录就一直显示在待处理列表中。

PostgreSQL 示例：

SELECT
    s.Subscription_ID,
    c.Customer_ID,
    c.Customer_Name,
    u.UtilityCompany_ID,
    u.Company_Name,
    s.Generation_Day,
    s.Due_Day,
    s.CutOff_Day
FROM BillSubscription s
JOIN Customer c
    ON c.Customer_ID = s.Customer_ID
JOIN UtilityCompany u
    ON u.UtilityCompany_ID = s.UtilityCompany_ID
WHERE s.Is_Active = TRUE
  AND s.Enrollment_Date <= CURRENT_DATE
  AND (s.End_Date IS NULL OR s.End_Date >= CURRENT_DATE)
  AND EXTRACT(DAY FROM CURRENT_DATE) >= s.Generation_Day
  AND NOT EXISTS (
      SELECT 1
      FROM Bill b
      WHERE b.Subscription_ID = s.Subscription_ID
        AND b.Billing_Period_Start = DATE_TRUNC('month', CURRENT_DATE)::date
  )
ORDER BY s.Generation_Day, c.Customer_Name;

这里使用：

EXTRACT(DAY FROM CURRENT_DATE) >= s.Generation_Day

没有使用等号，是因为如果 18 日无人处理，19 日打开界面时，这条记录仍应出现在待处理列表中。

SQL Server 可以写成：

DECLARE @Today date = CAST(GETDATE() AS date);
DECLARE @PeriodStart date = DATEFROMPARTS(
    YEAR(@Today),
    MONTH(@Today),
    1
);

SELECT
    s.Subscription_ID,
    c.Customer_ID,
    c.Customer_Name,
    u.UtilityCompany_ID,
    u.Company_Name,
    s.Generation_Day,
    s.Due_Day,
    s.CutOff_Day
FROM BillSubscription AS s
JOIN Customer AS c
    ON c.Customer_ID = s.Customer_ID
JOIN UtilityCompany AS u
    ON u.UtilityCompany_ID = s.UtilityCompany_ID
WHERE s.Is_Active = 1
  AND s.Enrollment_Date <= @Today
  AND (s.End_Date IS NULL OR s.End_Date >= @Today)
  AND DAY(@Today) >= s.Generation_Day
  AND NOT EXISTS (
      SELECT 1
      FROM Bill AS b
      WHERE b.Subscription_ID = s.Subscription_ID
        AND b.Billing_Period_Start = @PeriodStart
  )
ORDER BY s.Generation_Day, c.Customer_Name;

按日期范围查找计划生成的客户

如果界面支持选择日期范围，例如查看 8 月 18 日至 8 月 25 日的计划，可以先算出每个订阅在目标月份对应的实际计划日期。

SQL Server 示例：

DECLARE @FromDate date = '2026-08-18';
DECLARE @ToDate   date = '2026-08-25';

WITH Candidate AS (
    SELECT
        s.Subscription_ID,
        s.Customer_ID,
        s.UtilityCompany_ID,
        DATEFROMPARTS(
            YEAR(@FromDate),
            MONTH(@FromDate),
            CASE
                WHEN s.Generation_Day >
                     DAY(EOMONTH(@FromDate))
                THEN DAY(EOMONTH(@FromDate))
                ELSE s.Generation_Day
            END
        ) AS Scheduled_Date
    FROM BillSubscription AS s
    WHERE s.Is_Active = 1
)
SELECT
    x.Subscription_ID,
    c.Customer_ID,
    c.Customer_Name,
    u.UtilityCompany_ID,
    u.Company_Name,
    x.Scheduled_Date
FROM Candidate AS x
JOIN Customer AS c
    ON c.Customer_ID = x.Customer_ID
JOIN UtilityCompany AS u
    ON u.UtilityCompany_ID = x.UtilityCompany_ID
WHERE x.Scheduled_Date BETWEEN @FromDate AND @ToDate
  AND NOT EXISTS (
      SELECT 1
      FROM Bill AS b
      WHERE b.Subscription_ID = x.Subscription_ID
        AND b.Billing_Period_Start =
            DATEFROMPARTS(YEAR(x.Scheduled_Date), MONTH(x.Scheduled_Date), 1)
  )
ORDER BY x.Scheduled_Date, c.Customer_Name;

这个示例会把配置中的“31 日”调整为当月最后一天，用于处理没有 31 日的月份。是否采用这种规则，应由业务决定。其他做法包括禁止配置 29、30、31 日，或者让订阅明确指定“月末”。

生成账单时应重新校验

不能只依赖界面首次查询的结果。用户提交金额时，后台要再次检查账单是否已经存在，并在事务中完成插入。

BEGIN TRANSACTION;

INSERT INTO Bill (
    Bill_ID,
    Subscription_ID,
    Billing_Period_Start,
    Billing_Period_End,
    Scheduled_Date,
    Due_Date,
    CutOff_Date,
    Amount,
    Bill_Reference,
    Status
)
VALUES (
    @BillId,
    @SubscriptionId,
    @PeriodStart,
    EOMONTH(@PeriodStart),
    @ScheduledDate,
    @DueDate,
    @CutOffDate,
    @Amount,
    @BillReference,
    'GENERATED'
);

COMMIT TRANSACTION;

即使应用层已经检查过，数据库中的唯一约束也必须保留。发生并发请求时，唯一约束是防止重复数据的最后一道防线。应用需要捕获唯一键冲突，并提示“该账期账单已经生成”。

查询还是定时任务

如果流程是“操作员打开页面，查看待处理项，再手工录入金额”，页面打开或刷新时直接查询就够了。100 多名客户的数据量很小，只要索引合理，查询成本通常可以忽略。

建议增加以下索引：

CREATE INDEX IX_BillSubscription_Active_GenerationDay
    ON BillSubscription (Is_Active, Generation_Day);

CREATE INDEX IX_Bill_Subscription_Period
    ON Bill (Subscription_ID, Billing_Period_Start);

以下情况更适合使用定时任务：

  • 没有操作员登录时，也必须在指定时间创建待办记录。
  • 系统需要发送邮件、短信或其他提醒。
  • 需要冻结当月配置，避免后续订阅变更影响历史结果。
  • 待处理任务需要经过分派、审核、重试或超时升级。
  • 数据量较大，实时计算已经影响页面响应。

遇到这些情况，可以增加 BillGenerationTask 表，由每日 Job 创建任务。任务生成过程必须具备幂等性，例如为 (Subscription_ID, Billing_Period_Start) 添加唯一约束。Job 再次运行时应跳过已有任务，避免重复创建。

定时任务不应直接生成带有虚构金额的正式账单。如果金额必须由操作员填写，Job 最多只能创建“待录入”任务，或者创建状态为 PENDING_INPUT 的账单草稿。

日期设计中的注意事项

先明确三个日期的含义

必须先定义 Bill_Generation_Date、Our_CutOff_Date 和 Bill_Due_Date 的先后关系。例如：

账单产生日 <= 银行处理截止日 <= 公用事业公司付款到期日

如果 Our_CutOff_Date 才是操作员开始处理账单的日期，待处理查询就应该使用 CutOff_Day，而不是 Generation_Day。字段名称不能代替明确的业务定义。

处理跨月日期

如果账单在 27 日到期，通常仍属于当月。但业务中也可能出现“每月 28 日生成，下月 5 日到期”。这时只保存 Due_Day = 5，无法判断到期日属于哪个月，还需要增加月份偏移，例如：

Due_Month_Offset = 1

也可以直接把规则定义为“生成日后多少天到期”。

处理月末和非工作日

以下规则都需要提前确定：

  • 配置为 31 日，但当月只有 30 天时如何处理。
  • 2 月遇到 29 日、30 日或 31 日时如何处理。
  • 日期落在周末或法定节假日时，是提前还是顺延。
  • 采用银行所在时区，还是数据库服务器时区。

涉及银行业务时，建议使用银行营业日历表，不要只根据星期几判断工作日。

保存历史快照

账单生成后，要把该账期的 Scheduled_Date、Due_Date 和 CutOff_Date 写入 Bill 表。以后即使订阅配置发生变化，历史账单也不应跟着改变。

订阅表回答“今后应该何时生成”，账单表回答“某个账期实际生成了什么”。把两类数据分开后，待处理查询、历史追溯、并发控制和自动调度都会更清楚。

备注：内容仅供参考。