状態をデータベースのカラムではなくテーブルで表現する

この記事はしおだいふく Advent Calendar 2020 18日目の記事です。 データ設計の案です。今日も考えてはいるけどやったことないシリーズです。

データの状態

トランザクションデータ¹には状態を伴うものがよくあります。 例えば、決済SaaSのStripeにおける決済状況を表すデータであるPayment Intentの場合、 取引の進行に応じて以下のような状態を取ります。

• requires_payment_method
• requires_confirmation
• requires_action
• processing
• succeeded
• canceled

今日はこの状態をどのようにデータとして保持するかを検討してみます。

普通によくある方式

パッと思いつくのは単純にPaymentIntentテーブルに status というカラムを用意する方式です。 データの作成時刻と更新時刻をセットで持つことにして、1件のレコードは以下のようになるでしょう。

PaymentIntent: {
  ID: 'xxxxxxxxxxxx',
  status: 'processing',
  createdAt: 2020-12-18 00:00:00,
  updatedAt: 2020-12-18 00:01:00,
  // ......
}

状態の更新はstatusカラムに対するUPDATEクエリで行います。 更新後のデータはこんな感じになります。

PaymentIntent: {
  ID: 'xxxxxxxxxxxx',
  status: 'succeeded',
  createdAt: 2020-12-18 00:00:00,
  updatedAt: 2020-12-18 00:01:00,
  // ......
}

メリット

• IDがユニークであることをかんたんに保証できる
• シンプル

デメリット

• 現在の状態を参照するためにちょっと複雑な結合が必要
• 状態を管理するテーブルのレコードがめちゃくちゃ増える

状態ごとにテーブルを分割する方式

代案として、状態ごとにテーブルを分割する方式が考えられます。

たとえば状態がprocessingのPaymentIntentはProcessingPaymentIntentテーブルに格納します。

ProcessingPaymentIntent: {
  ID: 'xxxxxxxxxxxx',
  createdAt: 2020-12-18 00:00:00,
  // ......
}

状態を更新する場合は、ProcessingPaymentIntentのデータを削除して、次の状態（ここではSucceededとします）のテーブルにデータを作成します。 もちろんこの削除と作成は同一のトランザクション内で行わなければなりません。

SucceededPaymentIntent: {
  ID: 'xxxxxxxxxxxx',
  createdAt: 2020-12-18 00:00:00,
  // ......
}

この方式ではデータの更新が行われないため、更新時刻をデータとして管理する必要はありません。

メリット

• 更新が発生しない
• 終了状態以外のテーブルにデータが増えていかない
• 単一の状態のデータをクエリする際にWHEREをつけなくていい

デメリット

• IDの衝突に注意が必要
• 複数の状態にまたがるデータを1発でクエリできない

状態遷移だけを蓄積するテーブルを用意する

第3の案は、状態遷移だけを別テーブルに積み上げていく方式です。

PaymentIntent: {
  ID: 'xxxxxxxxxxxx',
  createdAt: 2020-12-18 00:00:00,
  // ......
}

PaymentIntentStatusTransition: {
  ID: 'xxxxxxxxxxxx',
  status: 'processing',
  createdAt: 2020-12-18 00:00:00,
}

状態の更新はPaymentIntentStatusTransitionテーブルに新しいレコードを作成します。

PaymentIntentStatusTransition: {
  ID: 'xxxxxxxxxxxx',
  status: 'succeeded',
  createdAt: 2020-12-18 00:01:00,
}

以前の状態を示すレコードは削除しないでそのまま残しておきます。

メリット

• IDがユニークであることをかんたんに保証できる
• 更新が発生しない
• 状態遷移の履歴を完全にトレースできる

デメリット

• 現在の状態を参照するためにちょっと複雑な結合が必要
• 状態を管理するテーブルのレコードがめちゃくちゃ増える

なんでこんな事を考えているのか

多くの場合は1つ目のシンプルなテーブル設計で概ね問題がないと思います。 一方で、レコードがたくさんあると性能が劣化するクエリが存在するといった場合は、ひとつのテーブルにデータが蓄積され続けていくパターンだと苦しみを背負うことがあります。 たとえば、（状態以外の）なんらかの理由でデータを更新する際にロックが必要になるケースでたまに問題になります。 なので、うまいことやって1つのテーブルに存在するデータ数を抑えたいという気持ちになることがまれにあります。

テーブルを分割してしまうとIDがユニークになることを保証するのがちょっと（かなり）手間なのですが、 初期状態のデータの作成頻度が高くなければ許せそうな気がします。

状態ごとにテーブル分割方式では最終状態のテーブルにデータが溜まっていきますが、 それを参照するユースケースの利用頻度が低ければ問題にならなそうです。

取り上げた例がよくなくて、多分決済系では使えないんですけどユーザアカウントとかだったら使えるのかもしれません。 サービスを利用制限状態にしたユーザとか退会したユーザとかをメインのアクティブなユーザのテーブルから分けておくとか、結構いいのかもしれない。

いつかここにまとめた案が役に立つ日が来ると信じて...