会員状態を例に状態管理のデータベース設計を考える #MySQL

はじめに

データベースを設計において、状態の扱いを考えてみます。
状態は時間経過や特定のイベントによって変化します。
例えば、会員情報には「入会手続き中（メール認証待ち）」「入会済み」「有料会員」「退会」など、さまざまな状態が考えられます。

本記事では、会員状態を例に、状態管理のデータベース設計を考えます。

備考

クエリの例はMySQLを使います。

若干アンチパターン

まず、よくやってしまう若干アンチパターンな設計を紹介します。
悪い点もあれば、いい点もあるため、結局はその案件にマッチしているかどうかを重視してください。

若干アンチパターン１：状態フラグを持たせる

状態フラグを持ったテーブルは次の理由で若干アンチパターンの一種となっています。

テーブル修正コストがかかるから
UNIQUE制約がつけられない

会員テーブルを例に見てみましょう。
会員状態に「入会手続き中（メール認証待ち）」「入会済み」「有料会員」「退会」の状態が考えられるとし、次のようなテーブルになります。

is_joinフラグは入会済みかどうかのフラグです。

is_joinがFALSEの場合、「入会手続き中（メール認証待ち）」
is_joinがTRUEの場合、「入会済み」

また、有料会員かどうかのフラグはis_paid_memberです。

is_paid_memberがFALSEの場合、「無料会員」
is_paid_memberがTRUEの場合、「有料会員」

そして、現在会員が退会しているかどうかは、delete_date退会日が存在するかどうかで判断します。

delete_dateがNULLの場合、「退会済み」
delete_dateに値が存在する（2024-12-01など）場合、「まだ退会していない会員」

なぜ若干アンチパターンなのか？→テーブル修正コストがかかるから

上記の会員テーブルの例だと、状態判断するカラムが3つ存在しています。（is_join,is_paid_member,delete_date）
このようにカラム数が状態の数に比例して多くなると、管理が複雑になります。

また、新しい状態を追加したい場合、テーブル変更が必要となり、修正コストがかかります。

なぜ若干アンチパターンなのか？→UNIQUE制約がつけられない

「nameはログインで使うから重複させたくない。しかし、もし会員が退会していた場合、同じnameが使える」という仕様があるとします。
そのとき、nameにUNIQUE制約を付けたくても、退会している会員と退会していない会員の２レコードが存在することになるため、UNIQUE制約をつけることができません。

このように、UNIQUE制約がつけられず、整合性を担保できないことがこの設計のデメリットです。

member_id	name	create_date	delete_date	is_join	is_paid_member
1	user123	2023-01-01 12:00:00	2023-01-03 14:00:00	TRUE	FALSE
2	user123(UNIQUE制約がつけられない)	2023-01-03 14:00:00	NULL	FALSE	FALSE
3	user456	2023-01-02 13:00:00	NULL	TRUE	TRUE
4	user789	2023-01-04 15:00:00	NULL	TRUE	TRUE

若干アンチパターンを使っても良い例

状態フラグのいいところは、テーブルを見た時、すぐにTRUEかFALSEか判別できる点です。
そのため、状態が非常に少ない場合、また状態がすべて明確に定まっており、将来的に増えることがない場合は、状態フラグを持たせる設計は一つの手法として用いるのを検討しても良いと思います。

若干アンチパターン2：状態テーブルを作成する

会員テーブルに対し、会員状態テーブルを作成する設計は、よくみられます。
しかし、この設計には次の理由で若干アンチパターンとされています。

過去の状態を保持できない
複数の状態を管理できない

なぜ若干アンチパターンなのか？→過去の状態を保持できない

会員状態テーブルは、新しい状態が追加されても、レコードを追加するだけでよく、さらに会員テーブルには外部キー制約を付けられるため、整合性を担保することができます。

しかし、それは 「今現在の状態のみ」であり「過去にどんな状態だったか」を知ることができません。

例えば、「以前、有料会員だったが、今は無料会員の会員を取得したい」という要望が来ても、現在無料会員なことしか判別できません。

なぜ若干アンチパターンなのか？→複数の状態を管理できない

会員テーブルと会員状態テーブルは１対１の関係です。そのため、複数の状態を持つことができません。
例えば、「休会中かつ有料会員の会員」という情報を問い合わせることができません。

若干アンチパターンを使っても良い例

状態テーブルを作成する設計は、シンプルで状態が増えても対応できます。
そのため、過去の状態を保持する必要がない、また同時に複数の状態が発生する状況が存在しない場合はこの設計を使うのも一つの手段になります。

状態履歴テーブルの作成

状態の履歴を残すかつ、会員テーブルをシンプルに設計する方法として、状態履歴テーブルの作成があります。

会員テーブルに状態情報を持たせず、すべて会員状態履歴テーブルに記録します。
これにより、会員がいつ、なんの状態になったのかを辿ることができ、状態管理に高い柔軟性を出すことができます。

デメリットとして、問い合わせクエリが少し複雑になる、状態が複数存在できるため、アプリ側で状態が単数か複数か判別しなければならないことが挙げられます。

name="user123"のユーザーの状態を取得するクエリは次のようになります。

-- name="user123"のユーザーの状態を取得する
SELECT
    m.name,
    ms.status_name,
    msh.change_status_date
FROM
    Member m
    INNER JOIN
        MemberStatusHistory msh-- 会員状態履歴テーブル
    ON  m.id = msh.member_id
    INNER JOIN
        MemberStatus ms-- 会員状態テーブル
    ON  ms.status_id = msh.status_id
WHERE
    m.name = 'user123'
ORDER BY
    msh.change_status_date DESC-- 状態変更日時でソート
;

結果は以下です。
状態の変更時刻から、いつ、どのような状態になったのかがわかります。

2023-01-01 10:00:00 入会手続き中（メール認証待ち）
↓
2023-01-01 10:30:00　入会
↓
2024-02-02 11:00:00　退会

終わりに

会員状態を例に状態管理のデータベース設計を考えてみました。
どの設計がいいのかは、案件によるので、その都度適切な設計を考えることが大切ですね。

参考

「失敗から学ぶRDBの正しい歩き方」第5章　フラグの闇
https://gihyo.jp/book/2019/978-4-297-10408-5

PluntUML

おまけです。

@startuml
skinparam class {
    BackgroundColor White
    ArrowThickness 1
}
!define TABLE(x) class x << (T,#FFAAAA) >>
!define FOREIGN_KEY(x) #x

TABLE(会員) {
 * id : INT
--
  name : VARCHAR(255)
  create_date : DATETIME
}

TABLE(会員状態) {
 * status_id : INT
--
  status_name : VARCHAR(255)
}

TABLE(会員状態履歴) {
  FOREIGN_KEY(member_id) : INT
  FOREIGN_KEY(status_id) : INT
--
  change_status_date : DATETIME
}
会員状態 ||--|{会員状態履歴
会員 ||--|{会員状態履歴

@enduml