有効期限付きポイントシステムの要求定義と設計

3秒まとめ

• 航空会社のマイルのような有効期限付きポイントシステムの設計を完全公開
• 3 つの設計アプローチを比較検証(トランザクションモデル、会計的アプローチ、オブジェクト指向)
• 取り消し処理や経理要件も満たす実装方法を詳細解説
• 15 年の運用実績あり、2 つのサービスで不整合なく稼働中
• ERD と実装手順つきで今日から使える設計書

どんな人向けの記事?

こんな悩みを持っている方にぴったりです

• 「有効期限付きポイントシステムを作りたいけど、どう設計すればいいか分からない...」
• 「ポイントの取り消し処理ってどうやって実装するの?」
• 「経理部門から『過去の残高を出して』って言われて困ってる」
• 「既存のポイントシステムの設計事例が見つからない！」

システムアーキテクトやバックエンドエンジニアの方、要件定義フェーズの方におすすめです。

ポイントシステムの種類を整理

まず、世の中にあるポイントシステムを見てみましょう。実は「ポイントに有効期限がある」と言っても、けっこう種類があるんですよね。

🛫 航空会社のマイルシステム(高難易度)

• ポイント獲得日ごとに有効期限が決まる仕組み
• 実装の難易度がめちゃくちゃ高いです！
• 15 年前に設計した当時は、この機能を実現しているサービスがほとんど見つけられませんでした
• 2023 年の今では大手ポイントサイトで採用されつつありますが、中小規模ではまだまだレアです
• まさにこれと同じ機能を実現したい方向けの記事です

🏬 ビックカメラ・ヨドバシカメラ型(低難易度)

• 最終利用日から 1 年で有効期限が切れるシンプルな仕組み
• 実装が簡単なので、あちこちで採用されています
• この記事では扱いません(簡単すぎるので！　)

要求定義:意外と複雑なんです

「ポイントシステムでしょ?簡単じゃん」と思われるかもしれませんが、実は運用を考えるとめちゃくちゃ複雑なんですよね。ぼくも最初に設計したときは「こんなに考慮すること多いの！?」ってなりました。

よく論点になるユースケースを整理しておきます。

登場人物(アクター)

機能要求:こんなに必要なの！?

🎯 基本機能

• ユーザに対してポイントを加算・減算できる
• 加算時に任意の有効期限をつける
• 有効期限が近いポイントから消費していく(FIFO: First In First Out)

🔄 運用機能(ここが難しい！)

• 減算を取り消すときには、減算前のポイントの有効期限を復元する
  • これが一番の難関です。「取り消したら元に戻る」って当たり前に聞こえますが、実装は大変...
• ある時点の残高を算出できる(「去年の3月末時点の残高は?」みたいな質問に答える)
• ポイントの整合性をチェックできる

👥 ユーザ向け機能

• 有効期限ごとに残高を表示できる
• 現在の残高をサクッと取得できる

💼 経理向け機能(見落としがち！)

• できるだけオンラインで処理できるようにする
• ある時点のBS(バランスシート)を算出
• ある時点のPL(Profit & Loss)を算出
• BSとPLの一致を検証(会計の基本ですね)

⚙️ システム要求

• 長期間運用しても遅くならないクエリ設計
• 減算・加算の整合性を検算できる仕組み

具体的なユースケース例

「こういうケースに対応できますか?」って必ず聞かれるので、先に書いておきます:

このシーケンス、特に④の取り消し処理が実現できるかどうかが設計の肝です。

3つの設計アプローチを比較

ここからが本題です！どのユースケースを満たしたいかによって、取るべきアプローチが変わってきます。

ぼくが検討した3つのアプローチをメリット・デメリット付きで紹介しますね。

各アプローチの概要

1. トランザクションモデル

   • 入金と出金の履歴だけを最小限保存
   • とにかくシンプルに作りたい場合

2. 会計的アプローチ

   • PL(損益)とBS(残高)を分離して保存
   • 集計クエリを重視する場合

3. オブジェクト指向 ⭐おすすめ

   • 入金・出金をオブジェクトとして厳密に管理
   • 取り消し処理が必要な場合(実務ではほぼ必須)

1. トランザクションモデル

「最低限の情報だけ持とう」というシンプル思考のアプローチです。

入金と出金の履歴は絶対に必要なので、それぞれをエンティティとして表現します。

ERD(mermaid版)

エンティティの説明

• User: ユーザ情報のマスタ
• DepositHistory: 入金履歴
  • amount: ポイント額
  • used_amount: その入金から利用済みの金額(デフォルト0)
  • expiration_date: 有効期限
• WithdrawHistory: 出金履歴

入金と出金を1つのテーブルにまとめることもできますが、used_amount と expiration_date は出金には存在しないカラムなので、正規化の観点から分けました。

メリット・デメリット

✅ メリット

• テーブルが2つで済むのでめちゃくちゃシンプル
• 実装が簡単

❌ デメリット

• 出金と入金の紐付けができない
• 出金の取り消しが実装できない(これが致命的...)

→「取り消し処理はいらない」と確信できる場合のみ採用しましょう。実務ではほぼ必要になると思います。

2. 会計的アプローチ

「PL(損益)とBS(残高)を分けて持とう」という会計思考のアプローチです。

会計をご存知の方なら、PL(Profit & Loss: 損益計算書)とBS(Balance Sheet: 貸借対照表)の両方を見られるように設計する、と言えば分かりやすいかもしれません。

ユーザや管理者が見たいのは基本的にPLかBSのどちらか、または両方です。だったら最初からその形でデータを保存しちゃえば集計が楽じゃん！というアプローチですね。

ERD(mermaid版)

メリット・デメリット

✅ メリット

• 集計がめちゃくちゃ簡単
• 現時点のBS(残高)がすぐに取得できる

❌ デメリット

• 入金と出金の紐付けがないので取り消しが行えない(これまた致命的...)
• 「去年の3月末時点の残高を出して」と言われたら、PLを最初から集計し直す必要がある
  • 経理から「昨年度の月末時点のポイント残高を月ごとに出して」とか言われると、ちょっと面倒なSQLを書く羽目になります

→ 集計重視だけど取り消し不要なら、このアプローチもアリです。

3. オブジェクト指向 ⭐おすすめ

「入金と出金を厳密に紐付けよう」という完全性重視のアプローチです。

ぼくが実際に採用したのはこの設計です。15年の運用実績があります！

入金と出金を別のテーブルで扱い、さらに入金に対する出金を関連テーブルで厳密に管理します。これによって取り消し処理が完璧に実装できるんです。

ERD(mermaid版)

設計のポイント

DepositWithdraw(中間テーブル)がキモ！

このテーブルが「どの入金から、どれだけのポイントを消費したか」を記録します。

例えば、150ポイント消費したときに:

• 6月期限の入金から100ポイント消費 → DepositWithdrawに1レコード
• 7月期限の入金から50ポイント消費 → DepositWithdrawに1レコード

取り消すときは、この2レコードを削除するだけで完璧に元に戻ります！

データの持ち方

• PLのデータは必須(Deposit, Withdraw, DepositWithdraw)
• BSは計算で出すか、別テーブルにするか選択可能
  • 参照頻度が高い → BS用テーブルを追加(パフォーマンス重視)
  • 参照頻度が低い → 計算で出す(シンプルさ重視)

ぼくの実装では、BS用のテーブルは作らずに、PLから計算で出しています。これでも十分高速に動作しています。

メリット・デメリット

✅ メリット

• 取り消し処理が完璧に実装できる(これが最大の強み！)
• PL、BSともに簡単なクエリで即時集計可能
• データの整合性が保証される
• 経理要件も満たせる

❌ デメリット

• テーブル数が多い(最低3テーブル)
• 排他制御が必要(デッドロック対策が必須)
• レコード数が多くなる(1回の出金で複数レコード作成される可能性)
• 実装がやや複雑

→ 実務で使うならこのアプローチ一択だと思います。最初はちょっと複雑に見えますが、運用を考えると絶対にこれが良いです。

実装:アプローチ3を実際に作る

ここからは、おすすめのアプローチ3(オブジェクト指向)を実装する方法を詳しく解説します。

技術選定

ストレージはRDBMS一択です。

なぜなら、複数テーブルをまたいだ排他制御が必要だから。NoSQLだとこれが難しいんですよね。

PostgreSQL、MySQL、どちらでもOKです。ぼくはPostgreSQLで実装しました。

排他制御の基本戦略

デッドロックを回避するための鉄則:

• 行ロックは必ず同じ順序でかける
• ERDの左側のエンティティから順番にロック
• User → Deposit → Withdrawの順

以下、各ユースケースごとの実装方法を解説しますね。

💰 加算(ポイント付与)

めちゃくちゃ簡単！

-- Depositにレコードを1件追加するだけ
INSERT INTO deposit (user_id, amount, expiration_date, created_at)
VALUES (123, 100, '2024-06-30', NOW());

ロックも不要。シンプルですね。

📊 残高の表示

-- 有効期限が未来のDepositから、使用済み金額を引く
SELECT
  d.expiration_date,
  d.amount - COALESCE(SUM(dw.amount), 0) AS balance
FROM deposit d
LEFT JOIN deposit_withdraw dw ON d.id = dw.deposit_id
WHERE d.user_id = 123
  AND d.expiration_date > CURRENT_DATE
GROUP BY d.id, d.expiration_date, d.amount
ORDER BY d.expiration_date;

💸 減算(ポイント消費)

**ここが一番複雑！**トランザクション必須です。

実装手順:

1. 残高チェック(不足していたらエラー)
2. Depositを有効期限が近い順でソートして取得
3. 影響のあるDepositを排他ロック(SELECT ... FOR UPDATE)
4. Withdrawテーブルにレコード追加
5. DepositWithdrawテーブルにレコード追加(複数の可能性あり)

-- 疑似コード
BEGIN;

-- 1. 残高チェック
SELECT check_balance(user_id, amount);

-- 2 & 3. 有効期限順でDepositを取得(ロック)
SELECT * FROM deposit
WHERE user_id = 123
  AND expiration_date > CURRENT_DATE
ORDER BY expiration_date
FOR UPDATE;

-- 4. Withdraw追加
INSERT INTO withdraw (user_id, amount, created_at)
VALUES (123, 150, NOW())
RETURNING id AS withdraw_id;

-- 5. DepositWithdraw追加(有効期限が近い順に消費)
-- deposit_id=1から100pt消費
INSERT INTO deposit_withdraw (deposit_id, withdraw_id, amount)
VALUES (1, withdraw_id, 100);

-- deposit_id=2から50pt消費
INSERT INTO deposit_withdraw (deposit_id, withdraw_id, amount)
VALUES (2, withdraw_id, 50);

COMMIT;

🔄 減算取り消し

取り消しもトランザクション必須！

実装手順:

1. 関連するDepositWithdrawを排他ロック
2. Withdrawを削除
3. 関連するDepositWithdrawを削除

BEGIN;

-- 1. 関連レコードをロック
SELECT * FROM deposit_withdraw
WHERE withdraw_id = 456
FOR UPDATE;

-- 2. Withdraw削除
DELETE FROM withdraw WHERE id = 456;

-- 3. DepositWithdraw削除
DELETE FROM deposit_withdraw WHERE withdraw_id = 456;

COMMIT;

これで完璧に元の状態に戻ります！

📋 一覧表示(注意点あり)

正直に言うと、これがこの設計の弱点です。

加算と減算を別テーブルで管理しているので、「時系列で全部見たい！」という要求に答えるのが大変なんです。計算量は O(n) になります。

対処法

アプローチ1: UNION ALLで結合

-- DepositとWithdrawをマージして時系列ソート
SELECT 'deposit' AS type, id, amount, created_at FROM deposit WHERE user_id = 123
UNION ALL
SELECT 'withdraw' AS type, id, amount, created_at FROM withdraw WHERE user_id = 123
ORDER BY created_at DESC;

この処理は重いので、UI側で工夫しましょう:

• 「入金履歴」と「出金履歴」を別タブで表示
• それぞれ単独で表示すれば高速

アプローチ2: 統合エンティティを追加

「入出金」という上位概念のテーブルを作って、DepositとWithdrawへの外部キーを持たせる方法もあります。

🗑️ ユーザの削除

退会ユーザの扱いはちょっと注意が必要です。

ベストプラクティス:

• 退会時に残ポイントを強制的に減算して残高を0にする
• そうしないと、PLやBS集計時に退会ユーザを除外する処理が複雑になる
• 「去年の3月末時点の残高」みたいなクエリが地獄になります...

-- 退会処理
BEGIN;

-- 残ポイントを全額減算
INSERT INTO withdraw (user_id, amount, created_at)
SELECT user_id, remaining_balance, NOW()
FROM (残高計算クエリ)
WHERE user_id = 退会ユーザID;

-- ユーザを論理削除
UPDATE user SET deleted_at = NOW() WHERE id = 退会ユーザID;

COMMIT;

⏰ ポイントの失効

有効期限切れポイントの扱いも要注意です。

実装方針:

• BSを計算する際に expiration_date で自動的にフィルタリング
• 失効時にバッチで減算レコードを作らない
  • なぜなら、ERだけでPL/BSを表現したいから
  • バッチの実行タイミングが難しい(失効と同時に実行するのは困難)

-- 現在の残高(失効分は自動的に除外される)
SELECT
  d.expiration_date,
  d.amount - COALESCE(SUM(dw.amount), 0) AS balance
FROM deposit d
LEFT JOIN deposit_withdraw dw ON d.id = dw.deposit_id
WHERE d.user_id = 123
  AND d.expiration_date > CURRENT_DATE  -- ここで失効分を除外
GROUP BY d.id, d.expiration_date, d.amount;

🔍 整合性チェック(検算)

毎日バッチで実行すべき検証クエリです。これがあると安心して眠れます。

-- 検証1: WithdrawとDepositWithdrawの合計が一致するか
SELECT
  CASE
    WHEN SUM(dw.amount) = SUM(w.amount) THEN 'OK'
    ELSE 'NG: データ不整合!'
  END AS check_result
FROM deposit_withdraw dw
JOIN withdraw w ON dw.withdraw_id = w.id;

-- 検証2: Depositの金額がDepositWithdrawの合計を上回っているか
SELECT
  d.id,
  d.amount AS deposit_amount,
  COALESCE(SUM(dw.amount), 0) AS used_amount,
  CASE
    WHEN d.amount >= COALESCE(SUM(dw.amount), 0) THEN 'OK'
    ELSE 'NG: 使いすぎ!'
  END AS check_result
FROM deposit d
LEFT JOIN deposit_withdraw dw ON d.id = dw.deposit_id
GROUP BY d.id, d.amount
HAVING d.amount < COALESCE(SUM(dw.amount), 0);  -- NGのみ抽出

毎日深夜にこの検証バッチを走らせていて、異常があれば Slack 通知するようにしています。15 年間、一度も不整合は起きていません！

まとめ:15年の運用実績から

この設計を最初に作ったのは 15 年前です。当時は「こんな複雑な設計で大丈夫かな...」って不安でしたが、今では確信を持って言えます。

この設計、めちゃくちゃ堅牢です！

🎉 実績

• 2 つのサービスで運用してきましたが、一度もデータ不整合が起きていません
• ビジネスサイド、ファイナンスサイドからのあらゆる要求に対応できています
• 監査、経理、マーケティングの集計要求にも答えられています

😅 正直な感想

• 設計がちょっと複雑なので、エンジニアかデータに詳しい人じゃないと複雑なクエリは書けない
• でも、それは設計の問題じゃなくてドメインの複雑さの問題だと思っています
• シンプルな設計で対応できるなら、それに超したことはないです

📢 この記事を公開した理由

正直に言うと、特許対策です。

「他人に特許を取られたら困るけど、自分で特許を取るほどでもない」ので、共有資産として残しておきたかったんです。もし誰かの役に立てば嬉しいです！

おまけ

📚 参考になる人

こんな方に読んでほしいです:

• ポイントシステムの設計を任された新米アーキテクト
• 「取り消し処理どうすればいいの！?」って困っているエンジニア
• 既存のポイントシステムをリプレースしたいシニアエンジニア

💭 設計のコツ

ポイントシステムに限らず、金銭に関わるシステムは「取り消し」を最初から考慮しておくことをおすすめします。

「取り消しは後から考えよう」は絶対に後悔します。ぼくは見てきました...。

🙏 最後に

この記事が、誰かの設計の参考になれば幸いです。

もし採用して実装したら、ぜひ感想を聞かせてください。Twitter(@matsubokkuri)でリプくれると嬉しいです！

質問や改善案があれば、Zenn のコメント欄や Twitter でお気軽にどうぞ〜 🙌