あのデータどうやって持たせるのが良いだろう？と頭を抱えることありませんか。
この記事ではサブスク管理人のデータベースの設計を紹介します。
テーブル設計
AIと検討を重ねた結果、次のようなテーブル定義になりました。
subscriptions
本サービスのメインテーブル。ユーザーが契約しているサブスクの情報を保持します。
| カラム名 | 説明 |
|---|---|
| id | サブスクリプションID |
| user_id | ユーザーID（外部キー） |
| name | サービス名 |
| price | 月額料金 |
| billing_cycle | 決済サイクル（月次/年次） |
| next_billing_date | 次回決済予定日 |
| is_trial | 無料トライアル中かどうかのフラグ |
| trial_end_date | トライアル終了日 |
月額料金は整数型で管理しています。現在は国内を対象としているため日本円を想定しています。将来的に多通貨の対応が必要になった場合は、通貨コードを持たせる等の拡張をします。
users
ユーザーの基本情報を管理します。ゲストユーザーもユーザーテーブルで管理します。
| カラム名 | 説明 |
|---|---|
| id | ユーザーID |
| メールアドレス（ゲスト時はnull可） | |
| password | パスワードハッシュ（ゲスト時はnull可） |
| is_guest | ゲストユーザーかどうかのフラグ |
sessions
| カラム名 | 説明 |
|---|---|
| id | セッションID |
| token | Bearerトークン |
| user_id | ユーザーID |
| expires_at | 有効期限 |
| created_at | 発行日時 |
reset_tokens
| カラム名 | 説明 |
|---|---|
| id | リセットトークンID |
| token | リセットトークン |
| user_id | ユーザーID |
| 申請時点のメールアドレス | |
| expires_at | 有効期限 |
| created_at | 発行日時 |
設計ポイント
設計において重視したポイントを紹介します。
ゲストユーザー
まずは試してみたいというニーズに応えるため、メールアドレス登録なしで利用できるゲストモードを実装しました。登録ユーザーとゲストユーザーを同一のテーブルで扱えるよう設計しています。
絵文字対応
サービス名を示す絵文字を保存するため、文字セットは utf8mb4 を採用しました。
トライアル期間
トライアル期間を管理するため、is_trial フラグと trial_end_date を定義しました。将来的にあと数日で無料期間が終わるサービスを抽出しアラートを出す機能などの機能拡張ができるように設計しています。
整合性とパフォーマンス
リリース後のデータの破損や動作遅延は避けたい要素です。
ユーザー削除時の設計
ユーザー削除時に紐づくサブスクデータが自動で消えるようにON DELETE CASCADE を設定してあります。不要なデータが残らない設計を心がけています。
インデックスによる速度改善
サブスクの一覧表示や次回決済日でのソートが頻繁に行われることを想定し、適切なインデックスを貼って高速化を図っています。
| インデックス名 | 対象カラム | 役割 |
|---|---|---|
| idx_subscriptions_user_id | user_id | ユーザーごとの一覧表示の高速化 |
| idx_subscriptions_next_billing_date | next_billing_date | 支払日が近い順の並べ替えを高速化 |
設計書の管理方法
サブスク管理人では設計書をDBML（Database Markup Language）で管理しています。
AIがテーブル構造をすぐに理解できるため、制約を考慮したコード提案を受けやすく開発速度が向上しました。
終わりに
DB設計のコード化とAIとの連携によりデータベース周りはあまり滞りなく進行しました。AIが理解しやすい設計書を整備することはますます重要になるだろうと実感しています。
連載記事一覧
- サブスク管理人という個人サービスをリリースしました
- 【サブスク管理人】デザインシステムとUI実装
- 【サブスク管理人】データベースの設計←本記事
- 【サブスク管理人】APIの設計と開発(準備中)
- 【サブスク管理人】インフラ設計と構築(準備中)
- 【サブスク管理人】データ分析基盤の整備(準備中)