ロールプレイチャットにおけるレスポンス管理のデータ設計比較
最初に
各モデル名は私とGeminiで勝手につけました。
1. レスポンス先の情報だけ持つ（シンプルモデル）
最も基本的な設計です。各メッセージが、どのメッセージへの返信なのか（レスポンス先）という情報だけを持ちます。
解説
messagesテーブルにresponse_to_message_idカラムを持たせることで、メッセージ間の返信関係を表現します。通知を送る際は、レスポンス先のメッセージを発言したキャラクターに送ることになります。
ER図
サンプルSQL：レスポンス先のmessageを取得する
あるメッセージ（message_id = 101）が、どのメッセージへの返信なのかを取得します。
-- メッセージID 101番のメッセージ情報を取得
SELECT
m1.message_id AS "current_message_id",
m1.message_body AS "current_message_body",
c1.character_id AS "author_id",
c1.character_name AS "author",
m2.message_id AS "response_target_id",
m2.message_body AS "response_target_body",
c2.character_id AS "response_target_author_id",
c2.character_name AS "response_target_author"
FROM
messages AS m1
-- 発言者情報を取得
JOIN
characters AS c1 ON m1.character_id = c1.character_id
-- レスポンス先のメッセージ情報をLEFT JOINで取得 (レスポンス先がない場合も考慮)
LEFT JOIN
messages AS m2 ON m1.response_to_message_id = m2.message_id
-- レスポンス先の発言者情報を取得
LEFT JOIN
characters AS c2 ON m2.character_id = c2.character_id
WHERE
m1.message_id = 101;
出力例
| current_message_id | current_message_body | author_id | author | response_target_id | response_target_body | response_target_author_id | response_target_author |
|---|---|---|---|---|---|---|---|
| 101 | こんにちは、アリスさん。 | 2 | ボブ | 100 | こんにちは！ | 1 | アリス |
メリット・デメリット
-
メリット:
- とてもシンプルで、実装がかんたん。
-
デメリット:
- 「Aさんの発言にBさんが返信し、その会話にCさんを加えたい」といった、柔軟な通知ができない。
- 会話の文脈（どの発言から始まった一連の会話か）を遡るのが難しい。
2. レスポンス先 + メンション先キャラクター情報を持つ（メンションモデル）
シンプルモデルに加えて、誰に通知を送りたいか（メンション）を明示的に管理するモデルです。
解説
messagesとcharactersの間に中間テーブルmentionsを設けます。これにより、あるメッセージが複数のキャラクターに宛てたものであることを表現できます。レスの途中で会話に参加させたいキャラクターを加えたり、逆に通知から外したりといったことができます。
ER図
サンプルSQL：レスポンス先のmessageとメンション先を取得する
上記SQLに加え、メッセージ（message_id = 101）でメンションされているキャラクターの一覧も取得します。
-- まずはレスポンス先を取得
SELECT
m1.message_id AS "current_message_id",
m1.message_body AS "current_message_body",
c1.character_id AS "author_id",
c1.character_name AS "author",
m2.message_id AS "response_target_id",
m2.message_body AS "response_target_body",
FROM
messages AS m1
JOIN
characters AS c1 ON m1.character_id = c1.character_id
LEFT JOIN
messages AS m2 ON m1.response_to_message_id = m2.message_id
WHERE
m1.message_id = 101;
-- 次にメッセージ101番のメンション先キャラクター一覧を取得
SELECT
c.character_id,
c.character_name
FROM
mentions AS mn
JOIN
characters AS c ON mn.character_id = c.character_id
WHERE
mn.message_id = 101;
出力例
1つ目のクエリ結果
| current_message_id | current_message_body | author_id | author | response_target_id | response_target_body |
|---|---|---|---|---|---|
| 101 | こんにちは、アリスさん。キャロルさんも見てるかな？ | 2 | ボブ | 100 | こんにちは！ |
2つ目のクエリ結果
| character_id | character_name |
|---|---|
| 1 | アリス |
| 3 | キャロル |
メリット・デメリット
-
メリット:
- 通知の宛先を自由につけ外しできるので、グループ会話などを実装しやすい。
-
デメリット:
- メンション先をつけ外しする機能を実装しないといけない。
- これだけでは、会話のツリー全体を把握するのは依然として難しい。
3. レスポンス先 + ルートメッセージによる擬似ツリー（ツリーモデル）
一連の会話を「スレッド」や「ツリー」としてまとめたいときに使えるモデルです。
解説
messagesテーブルにroot_message_idカラムを追加します。これは、その一連の会話の起点となったメッセージのIDを指します。返信の際は、レスポンス先のroot_message_idを引き継ぎます。これにより、同じroot_message_idを持つメッセージを検索するだけで、会話ツリーを取得できます。
ER図
サンプルSQL：ツリーのメッセージ一覧を取得する
あるメッセージ（message_id = 105）が含まれる会話ツリーの全メッセージを新しい順に取得します。
-- メッセージID 105番のルートメッセージIDを特定し、
-- そのルートIDを持つすべてのメッセージを取得する
SELECT
m.message_id,
m.message_body,
c.character_id AS author_id,
c.character_name AS author_name,
m.created_at
FROM
messages AS m
JOIN
characters AS c ON m.character_id = c.character_id
WHERE
m.root_message_id = (
SELECT root_message_id FROM messages WHERE message_id = 105
)
ORDER BY
m.created_at DESC;
出力例（例：root_message_id = 100 のスレッド）
| message_id | message_body | author_id | author_name | created_at |
|---|---|---|---|---|
| 105 | 了解、今夜20時に集合しよう。 | 3 | キャロル | 2025-10-11 12:10:05 |
| 104 | やった！ 私も参加する！ | 1 | アリス | 2025-10-11 12:08:30 |
| 103 | ボブ、こんにちは。キャロルも呼びましょう。 | 1 | アリス | 2025-10-11 12:05:12 |
| 102 | 今夜はバーベキュー会だったよね。 | 2 | ボブ | 2025-10-11 12:02:47 |
| 101 | こんにちは、アリスさん。 | 2 | ボブ | 2025-10-11 12:01:00 |
| 100 | 今日は天気がいいなあ。 | 1 | アリス | 2025-10-11 12:00:00 |
注: 例ではmessage_id=100をスレッド起点（root_message_id=100）として、以降の返信が同じroot_message_idを引き継いでいる想定です。
メリット・デメリット
-
メリット:
- 特定の話題に関する一連の会話をまとめて表示するのがとてもかんたんになる。
-
デメリット:
- 新規投稿時に
root_message_idに自身のIDを入れる、返信時には親のroot_message_idを引き継ぐ、といったロジックの実装が必要になる。 - 通知の柔軟性はこのモデル単体では解決しない。
- 新規投稿時に
4. すべての要素を持つ（メンションツリーモデル）
上記2と3を組み合わせました。
解説
メンションによる柔軟な通知と、ルートメッセージによる会話のツリー管理を両立します。うれしい要素は増えますが、その分構造は複雑になります。
ER図
サンプルSQL：ツリーのメッセージ一覧と、各メッセージのメンション先を取得する
ツリーのメッセージ一覧を取得し、さらに各メッセージが誰にメンションしているかを（キャラクター名をカンマ区切りで）表示します。
SELECT
m.message_id,
m.message_body,
c.character_id AS author_id,
c.character_name AS author_name,
m.created_at,
GROUP_CONCAT(mc.character_id) AS mention_target_ids,
GROUP_CONCAT(mc.character_name) AS mention_target_names
FROM
messages AS m
JOIN characters AS c ON m.character_id = c.character_id
LEFT JOIN mentions AS mn ON m.message_id = mn.message_id
LEFT JOIN characters AS mc ON mn.character_id = mc.character_id
WHERE
m.root_message_id = (
SELECT root_message_id FROM messages WHERE message_id = 105
)
GROUP BY
m.message_id
ORDER BY
m.created_at DESC;
※ GROUP_CONCATはMySQLやSQLiteで利用できます。他のDBMSでは同様の機能を持つ関数（例: PostgreSQLのSTRING_AGG）を使用してください。
出力例（例：root_message_id = 100 のスレッド）
| message_id | message_body | author_id | author_name | created_at | mention_target_ids | mention_target_names |
|---|---|---|---|---|---|---|
| 105 | 了解、今夜20時に集合しよう。 | 3 | キャロル | 2025-10-11 12:10:05 | 1,2 | アリス,ボブ |
| 104 | やった！ 私も参加する！ | 1 | アリス | 2025-10-11 12:08:30 | 3 | キャロル |
| 103 | ボブ、こんにちは。キャロルも呼びましょう。 | 1 | アリス | 2025-10-11 12:05:12 | 2,3 | ボブ,キャロル |
| 102 | 今夜はバーベキュー会だったよね。 | 2 | ボブ | 2025-10-11 12:02:47 | 1 | アリス |
| 101 | こんにちは、アリスさん。 | 2 | ボブ | 2025-10-11 12:01:00 | 1 | アリス |
| 100 | 今日は天気がいいなあ。 | 1 | アリス | 2025-10-11 12:00:00 | NULL | NULL |
メリット・デメリット
-
メリット:
- ぜんぶ盛りでうれしい。
-
デメリット:
- テーブル構造とアプリケーションロジックが最も複雑になる。
- クエリパフォーマンスにも注意が必要になるかも。
最後に
間違いなどありましたらお気軽にコメントください。
Discussion