ソシャゲのクエストシステムのDB構造を考えたい

趣味で作っているAPIにソシャゲのようなクエストシステム(クライアントサイドで動くクエスト+クエスト結果のサーバー上での確定)が必要になったので、それを実装するためのテーブル構造を考えたい回

※ 以下、普通のフロントエンドエンジニアが考える 趣味等小規模な環境での利用を想定した設計です。専門外なので、プロダクションでスケールする方法であるかどうかは全く保証できません。

要件

前提

• 既にuser_quest_logsというテーブルがあるものとする

quest_log_id PK integer
user_id FK integer
quest_id FK integer
clear_rank integer
quest_data text
wave_data text
drop_data text
wave_id integer

• これで一応は動いているが、クエストクリア履歴情報を求めるに辺り膨大なクエリコストがかかるようになってしまっているため改善案を考える。

クエストの処理の流れ

クエスト開始エンドポイント

• 1 クエスト開始時、クライアントはクエストIDとパーティ編成IDを送る
• 2 サーバーでクエスト開始処理を行う
  • 対応するクエスト情報を取ってくる
  • クエストのウェーブ毎のランダム情報を計算する (遭遇する敵キャラを求める)
  • クエストの敵キャラ毎のドロップアイテムを求める
  • クエストログ情報を生成する
    • 進行中のクエストログID
    • 生成したウェーブランダムJSON
    • 生成したドロップアイテムJSON
    • 現在のウェーブ番号 (途中でアプリがキルされた場合の再開用)
    • クリアランク(完了時に代入)
• 3 サーバーは クエストログID / ウェーブ / ドロップをクライアントに返す (パーティ情報は他のエンドポイントが返す)

クエスト進行エンドポイント

• 1 クエストのウェーブ進行時、クライアントは クエストログIDとウェーブ番号を送る
• 2 サーバーでクエスト状況の更新処理を行う
  • クエストログIDを元に クエストログ情報を取ってくる
  • 現在のウェーブ番号を更新して保存する
• 3 サーバーは 200を返す(進行状態の保存がしたいだけで不要)

ログインエンドポイント

• 1 サーバーがクライアントに クエストログ情報を丸ごと返す
• 2 クライアントがクエストログ情報を元にクエスト進捗を復元する

クエスト完了エンドポイント

• 1 クエストの完了時、クライアントはクエストログIDと各スキルの利用回数を送る
• 2 クエストログIDからクエスト情報を取得する
• 3 ユーザーの各種スキルレベルを更新する
• 4 クエストログのクリアランクを更新し、不要になったフィールドは消し飛ばす

クエスト情報エンドポイント

• 1 クエストログを元にクリアランクを取得し、マスターデータと組み合わせてクライアントに返す

備考

• 進行中のクエスト履歴情報とクリア履歴テーブルを分けないと、パフォーマンス的な限界が見える...

クエスト情報エンドポイントで、DBがパフォーマンス的なボトルネックになっているので、クエスト履歴情報テーブルを廃止して、クリア履歴テーブルを新たに作るとしてみる。クエスト履歴にあったフィールドは、別に検索用に用意しているわけではないので、単にJSONにまとめてユーザー情報の1フィールドにまとめてみる

クエストクリア履歴テーブル

quest_clear_id PK integer
user_id FK integer
quest_id FK integer
claer_rank integer

ユーザーテーブル

user_id PK integer
name string
last_quest_data string (json)

last_quest_dataの構造

quest_log_id integer
quest_id integer
quest_data string
wave_data string
wave_id integer

クエスト進行エンドポイントで、送られたウェーブ番号の妥当性を検証するために
questDataにはクエスト開始時点の完全なクエスト情報全てが入ってたほうが良さそう