MSSQL 会员订阅付款的 SQL Table Design

2026-10-24支付阿杰

结论

不要按年份归档或清空 Payments。建议把数据分成三类保存：

收费标准：某种会籍在某个会籍年度的标准费用。
会员应收：某位会员在该年度实际应付的金额。
付款与核销：实际收到的款项，以及每笔款项对应哪个年度的应收。

需要注意的是，付款日期不代表付款所属年度。会员可能在 2011 年补交 2010 年的欠款，所以不能只根据 PaymentDate 判断订阅年度。

可以采用下面的关系：

MembershipTypes
        │
        ├── SubscriptionRates（各年度标准价格）
        │
Members └── MemberSubscriptions（会员各年度实际应收）
                         │
                         └── PaymentAllocations ── Payments

这样可以同时保留 2010 年的 £1000 和 2011 年的 £1100，也不必删除任何历史付款。

1. 保存每年的订阅费标准

每个“会籍类型 + 会籍年度”对应一条价格记录：

CREATE TABLE dbo.SubscriptionRates
(
    SubscriptionRateID bigint IDENTITY(1,1) NOT NULL
        CONSTRAINT PK_SubscriptionRates PRIMARY KEY,

    MembershipTypeID int NOT NULL,
    MembershipYear smallint NOT NULL,
    AnnualFee decimal(19, 4) NOT NULL,
    CurrencyCode char(3) NOT NULL
        CONSTRAINT DF_SubscriptionRates_CurrencyCode DEFAULT ('GBP'),

    CONSTRAINT FK_SubscriptionRates_MembershipTypes
        FOREIGN KEY (MembershipTypeID)
        REFERENCES dbo.MembershipTypes (MembershipTypeID),

    CONSTRAINT UQ_SubscriptionRates_TypeYear
        UNIQUE (MembershipTypeID, MembershipYear),

    CONSTRAINT CK_SubscriptionRates_AnnualFee
        CHECK (AnnualFee >= 0)
);

示例数据：

INSERT INTO dbo.SubscriptionRates
(
    MembershipTypeID,
    MembershipYear,
    AnnualFee,
    CurrencyCode
)
VALUES
    (1, 2010, 1000.00, 'GBP'),
    (1, 2011, 1100.00, 'GBP');

新增 2011 年的价格时，插入一条新记录，不要更新 2010 年的记录。已经使用过的历史费率最好保持不变。如果发现录入错误，应通过受控流程进行更正。

2. 为每位会员建立年度应收记录

收费标准是标准价格，会员实际应付的金额还可能受到折扣、减免、入会时间或会籍变更的影响，因此需要单独建立年度应收表：

CREATE TABLE dbo.MemberSubscriptions
(
    MemberSubscriptionID bigint IDENTITY(1,1) NOT NULL
        CONSTRAINT PK_MemberSubscriptions PRIMARY KEY,

    MemberID int NOT NULL,
    SubscriptionRateID bigint NOT NULL,
    MembershipYear smallint NOT NULL,
    AmountDue decimal(19, 4) NOT NULL,
    DueDate date NULL,
    CreatedAt datetime2(0) NOT NULL
        CONSTRAINT DF_MemberSubscriptions_CreatedAt DEFAULT (SYSDATETIME()),

    CONSTRAINT FK_MemberSubscriptions_Members
        FOREIGN KEY (MemberID)
        REFERENCES dbo.Members (MemberID),

    CONSTRAINT FK_MemberSubscriptions_Rates
        FOREIGN KEY (SubscriptionRateID)
        REFERENCES dbo.SubscriptionRates (SubscriptionRateID),

    CONSTRAINT UQ_MemberSubscriptions_MemberYear
        UNIQUE (MemberID, MembershipYear),

    CONSTRAINT CK_MemberSubscriptions_AmountDue
        CHECK (AmountDue >= 0)
);

AmountDue 保存会员在该年度已经确认的应收金额。即使标准价格后来因纠错或其他原因有所调整，也不应连带改变已经确认的历史应收。

创建年度应收记录时，可以从费率表复制标准金额：

INSERT INTO dbo.MemberSubscriptions
(
    MemberID,
    SubscriptionRateID,
    MembershipYear,
    AmountDue,
    DueDate
)
SELECT
    @MemberID,
    sr.SubscriptionRateID,
    sr.MembershipYear,
    sr.AnnualFee,
    @DueDate
FROM dbo.SubscriptionRates AS sr
WHERE sr.MembershipTypeID = @MembershipTypeID
  AND sr.MembershipYear = @MembershipYear;

如果会员可以在一年内变更会籍，UNIQUE (MemberID, MembershipYear) 就不适用了。此时应允许同一会员在同一年度拥有多条应收记录，并增加生效日期、失效日期或收费期间等字段。

3. 永久保存每一笔实际付款

付款表只记录实际收到的款项，不判断款项属于哪个年度：

CREATE TABLE dbo.Payments
(
    PaymentID bigint IDENTITY(1,1) NOT NULL
        CONSTRAINT PK_Payments PRIMARY KEY,

    MemberID int NOT NULL,
    PaymentMethodID int NOT NULL,
    PaymentDate date NOT NULL,
    PaymentAmount decimal(19, 4) NOT NULL,
    ReferenceNumber nvarchar(100) NULL,
    CreatedAt datetime2(0) NOT NULL
        CONSTRAINT DF_Payments_CreatedAt DEFAULT (SYSDATETIME()),

    CONSTRAINT FK_Payments_Members
        FOREIGN KEY (MemberID)
        REFERENCES dbo.Members (MemberID),

    CONSTRAINT FK_Payments_PaymentMethods
        FOREIGN KEY (PaymentMethodID)
        REFERENCES dbo.PaymentMethods (PaymentMethodID),

    CONSTRAINT CK_Payments_PaymentAmount
        CHECK (PaymentAmount > 0)
);

避免使用 Date、Year 这类含义模糊或容易产生歧义的列名。PaymentDate 和 MembershipYear 表达得更清楚。

4. 将付款核销到对应年度

一笔付款可能需要分配到多个年度。例如，会员一次支付 £1200，其中 £200 用于补交上一年度的欠款，另外 £1000 用于支付本年度费用。遇到这种情况，可以使用独立的核销表：

CREATE TABLE dbo.PaymentAllocations
(
    PaymentAllocationID bigint IDENTITY(1,1) NOT NULL
        CONSTRAINT PK_PaymentAllocations PRIMARY KEY,

    PaymentID bigint NOT NULL,
    MemberSubscriptionID bigint NOT NULL,
    AllocatedAmount decimal(19, 4) NOT NULL,

    CONSTRAINT FK_PaymentAllocations_Payments
        FOREIGN KEY (PaymentID)
        REFERENCES dbo.Payments (PaymentID),

    CONSTRAINT FK_PaymentAllocations_MemberSubscriptions
        FOREIGN KEY (MemberSubscriptionID)
        REFERENCES dbo.MemberSubscriptions (MemberSubscriptionID),

    CONSTRAINT UQ_PaymentAllocations_PaymentSubscription
        UNIQUE (PaymentID, MemberSubscriptionID),

    CONSTRAINT CK_PaymentAllocations_Amount
        CHECK (AllocatedAmount > 0)
);

假设会员在 2010 年分 10 次付款，每次 £100。每一笔款项都写入 Payments，再通过 PaymentAllocations 关联到该会员的 2010 年应收记录。

BEGIN TRANSACTION;

INSERT INTO dbo.Payments
(
    MemberID,
    PaymentMethodID,
    PaymentDate,
    PaymentAmount
)
VALUES
(
    @MemberID,
    @PaymentMethodID,
    @PaymentDate,
    100.00
);

DECLARE @PaymentID bigint = SCOPE_IDENTITY();

INSERT INTO dbo.PaymentAllocations
(
    PaymentID,
    MemberSubscriptionID,
    AllocatedAmount
)
VALUES
(
    @PaymentID,
    @MemberSubscriptionID,
    100.00
);

COMMIT TRANSACTION;

付款和核销应在同一个事务中完成，以免付款已经创建，对应的核销却没有写入。

5. 查询各年度已付款与未付款金额

可以按会员和年度汇总：

SELECT
    ms.MemberSubscriptionID,
    ms.MemberID,
    ms.MembershipYear,
    mt.MembershipTypeName,
    ms.AmountDue,
    COALESCE(SUM(pa.AllocatedAmount), 0.00) AS AmountPaid,
    ms.AmountDue - COALESCE(SUM(pa.AllocatedAmount), 0.00) AS AmountOutstanding
FROM dbo.MemberSubscriptions AS ms
INNER JOIN dbo.SubscriptionRates AS sr
    ON sr.SubscriptionRateID = ms.SubscriptionRateID
INNER JOIN dbo.MembershipTypes AS mt
    ON mt.MembershipTypeID = sr.MembershipTypeID
LEFT JOIN dbo.PaymentAllocations AS pa
    ON pa.MemberSubscriptionID = ms.MemberSubscriptionID
WHERE ms.MemberID = @MemberID
GROUP BY
    ms.MemberSubscriptionID,
    ms.MemberID,
    ms.MembershipYear,
    mt.MembershipTypeName,
    ms.AmountDue
ORDER BY
    ms.MembershipYear;

查询结果可以同时显示：

2010  应付 £1000  已付 £1000  未付 £0
2011  应付 £1100  已付  £440  未付 £660

完整的付款历史可以这样查询：

SELECT
    p.PaymentID,
    p.PaymentDate,
    p.PaymentAmount,
    pm.PaymentMethodName,
    ms.MembershipYear,
    pa.AllocatedAmount
FROM dbo.Payments AS p
INNER JOIN dbo.PaymentMethods AS pm
    ON pm.PaymentMethodID = p.PaymentMethodID
LEFT JOIN dbo.PaymentAllocations AS pa
    ON pa.PaymentID = p.PaymentID
LEFT JOIN dbo.MemberSubscriptions AS ms
    ON ms.MemberSubscriptionID = pa.MemberSubscriptionID
WHERE p.MemberID = @MemberID
ORDER BY
    p.PaymentDate,
    p.PaymentID;

需要特别注意的约束

普通 CHECK 约束无法检查跨多行的汇总结果，因此还需要通过业务逻辑、存储过程或触发器保证：

一笔付款的核销总额不能超过 PaymentAmount。
某项应收的核销总额通常不能超过 AmountDue。
付款对应的会员必须与年度应收记录中的会员一致。
调整或退款不能通过直接删除历史付款来处理。

退款、冲销和会费减免最好分别保存为可追踪的交易或调整记录，不要修改原付款金额。财务记录入账后，保留原记录并增加反向记录，通常更便于审计。

如果业务规则明确规定“一笔付款永远只对应一个年度”，可以直接在 Payments 中增加 MemberSubscriptionID，不再使用 PaymentAllocations。不过，独立的核销表可以处理跨年度付款、预付款和部分核销，扩展性更好。