订阅产品需要更好的 database schema 设计方案

2026-10-24支付阿杰

结论

不必为每个用户的每个有效日期单独生成一行记录。可以采用下面的设计：

subscriptions 保存订阅有效期和计费规则。
subscription_pauses 只记录暂停的日期区间。
实际账务由独立的 payments 或 invoices 记录，避免让 transaction 同时承担订阅状态和付款记录两种职责。

这样，用户暂停 3 个不连续的单日，只需增加 3 条记录；连续暂停 10 天，只需记录 1 个区间。

180 万行对 MariaDB 来说并不算特别大。原方案的问题主要不在数据量，而在于保存了大量可以根据订阅有效期推导出的重复信息，延期、补录和计费调整也会因此变得麻烦。

推荐的表结构

下面使用左闭右开区间：

[start_date, end_date)

区间包含 start_date，不包含 end_date，可以避免日期边界上的歧义。

CREATE TABLE plans (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price_per_day DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE subscriptions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    plan_id BIGINT UNSIGNED NOT NULL,

    start_date DATE NOT NULL,
    end_date DATE NOT NULL,

    status VARCHAR(20) NOT NULL,

    -- 保存购买时的价格，避免 plan 后续调价影响历史账单
    price_per_day DECIMAL(10, 2) NOT NULL,

    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,

    PRIMARY KEY (id),
    KEY idx_subscriptions_user (user_id),
    KEY idx_subscriptions_period (start_date, end_date),
    KEY idx_subscriptions_plan (plan_id),

    CONSTRAINT fk_subscriptions_plan
        FOREIGN KEY (plan_id) REFERENCES plans(id)
) ENGINE=InnoDB;

CREATE TABLE subscription_pauses (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    subscription_id BIGINT UNSIGNED NOT NULL,

    pause_start DATE NOT NULL,
    pause_end DATE NOT NULL,

    reason VARCHAR(255) NULL,
    created_at DATETIME NOT NULL,

    PRIMARY KEY (id),
    KEY idx_pauses_subscription_period
        (subscription_id, pause_start, pause_end),

    CONSTRAINT fk_pauses_subscription
        FOREIGN KEY (subscription_id)
        REFERENCES subscriptions(id)
) ENGINE=InnoDB;

例如，一项订阅从 2017 年 1 月 1 日开始，覆盖 30 个自然日：

start_date = 2017-01-01
end_date   = 2017-01-31

如果只暂停 1 月 10 日，可以记录为：

INSERT INTO subscription_pauses (
    subscription_id,
    pause_start,
    pause_end,
    created_at
) VALUES (
    1,
    '2017-01-10',
    '2017-01-11',
    NOW()
);

如果连续暂停 1 月 15 日至 1 月 17 日，则记录为：

INSERT INTO subscription_pauses (
    subscription_id,
    pause_start,
    pause_end,
    created_at
) VALUES (
    1,
    '2017-01-15',
    '2017-01-18',
    NOW()
);

这条记录表示 15、16、17 日暂停，18 日恢复。

查询某一天是否有效

可以用 NOT EXISTS 判断某项订阅在指定日期是否可用：

SELECT s.id
FROM subscriptions AS s
WHERE s.id = ?
  AND ? >= s.start_date
  AND ? < s.end_date
  AND s.status IN ('active', 'paused')
  AND NOT EXISTS (
      SELECT 1
      FROM subscription_pauses AS p
      WHERE p.subscription_id = s.id
        AND ? >= p.pause_start
        AND ? < p.pause_end
  );

这里的几个日期参数都传入同一个待查询日期。

不要只通过 status = 'active' 判断。status 表示订阅整体所处的生命周期状态，某一天是否暂停则由暂停区间决定。

计算应计费天数

如果暂停不会延长订阅的结束日期，应计费天数为：

订阅覆盖天数 - 暂停天数

确认各暂停区间互不重叠后，可以这样计算：

SELECT
    s.id,
    DATEDIFF(s.end_date, s.start_date) -
    COALESCE(
        SUM(
            GREATEST(
                0,
                DATEDIFF(
                    LEAST(p.pause_end, s.end_date),
                    GREATEST(p.pause_start, s.start_date)
                )
            )
        ),
        0
    ) AS billable_days
FROM subscriptions AS s
LEFT JOIN subscription_pauses AS p
    ON p.subscription_id = s.id
   AND p.pause_start < s.end_date
   AND p.pause_end > s.start_date
WHERE s.id = ?
GROUP BY
    s.id,
    s.start_date,
    s.end_date;

应付金额可以继续按下面的公式计算：

billable_days * price_per_day

在生产环境中，结算完成后应将结果写入不能随意修改的账单明细。展示历史账单时，不宜每次都重新计算。

必须防止暂停区间重叠

下面两条记录相互重叠：

[2017-01-10, 2017-01-15)
[2017-01-12, 2017-01-18)

如果直接累计暂停天数，12、13、14 日会被重复扣除。新增暂停区间前，应在同一个数据库事务中执行检查：

SELECT id
FROM subscription_pauses
WHERE subscription_id = ?
  AND pause_start < ?
  AND pause_end > ?
FOR UPDATE;

两个参数依次是新记录的 pause_end 和 pause_start。如果查询返回记录，就表示区间发生了重叠。

此时可以拒绝本次操作，也可以合并区间或修改已有区间。

MariaDB 没有 PostgreSQL exclusion constraint 那样的原生区间排斥约束，所以通常需要由应用层配合事务处理。如果并发操作较多，还应锁定对应的 subscriptions 记录，防止两个请求同时通过重叠检查。

先确定“暂停”的业务含义

“暂停”可能对应两种完全不同的业务规则，设计表结构前需要先确定采用哪一种。

暂停后不延长结束日期

用户购买的是固定自然日期区间，例如 1 月 1 日至 1 月 31 日。即使中间暂停 3 天，订阅仍在 1 月 31 日结束，只收取 27 天的费用。

这种情况可以直接使用前面的 start_date、end_date 和暂停区间。

暂停后顺延结束日期

用户购买的是 30 个“实际服务日”。如果暂停 3 天，订阅结束日期也要顺延 3 天。

这种情况可以增加一个字段：

ALTER TABLE subscriptions
    ADD COLUMN purchased_service_days INT UNSIGNED NULL;

例如：

start_date             = 2017-01-01
purchased_service_days = 30

最终结束日期不能简单地设为“开始日期加 30 天，再加暂停天数”，因为顺延后的日期仍可能落入新的暂停区间。更稳妥的处理方式是逐日推进，直到累计满 30 个非暂停日；也可以在每次暂停和恢复时维护经过验证的预计结束日期。

付款和订阅应分开建模

不宜使用含义过于宽泛的 transaction 表，同时保存计划选择、订阅期限和付款状态。可以拆分为：

users
plans
subscriptions
subscription_pauses
invoices
invoice_items
payments

各表的职责如下：

subscriptions 表示用户获得服务的契约。
subscription_pauses 记录服务不可用的例外区间。
invoices 和 invoice_items 记录某个账期实际收取的费用。
payments 记录支付尝试、成功、失败和退款。

账单生成后，应保存当时使用的单价、计费天数和金额。否则，一旦计划价格或暂停记录发生变化，历史账单也可能被意外改动。

是否完全不能使用逐日记录

逐日表并非一定不可取。只要索引设计合理，180 万行对 MariaDB 通常并不可怕。在以下场景中，逐日明细也有实际价值：

每日价格可能不同；
每天适用不同的税率、折扣或用量；
需要保留不可修改的计费审计记录；
经常统计每天的活跃订阅数量。

不过，逐日数据更适合作为结算时生成的账单明细或汇总表，不适合成为订阅状态的唯一数据源。

针对当前需求，可以优先保存订阅有效期和少量暂停区间。这样既能减少存储，也更贴合数据本身的含义。生成账单或统计报表时，再按需展开为每日数据。