DM機能のDB設計でハマった話

現在、内定者インターンに向けてタスク管理アプリを開発しています。

その中でDM機能のデータベース設計にかなり苦戦しました。

最初はシンプルに設計したつもりでしたが、途中で「これ拡張できなくないか？」と気づき、最終的に設計を大きく見直すことになりました。

この記事では、その試行錯誤と学びをまとめており、特にDMの設計にフォーカスしております。

データベース設計を自力でやってみた

最初にDB設計を自力でやってみました。
以下、最初の設計になります。
メモ書きなので書き方はお気になさらず...

Project

project_id PK uuid
title string
created_at Date
updated_at Date
started_at Date
deadline Date

Task

task_id PK uuid
title string
state enum(pending,running,finished)
created_at Date
updated_at Date
project_id uuid FK
user_id FK

User

email PK uuid
password
family_name string
given_name string
created_at Date

Room

id PK uuid
send_user_id string
recive_user_id string
created_at Date

Message

id PK uuid
room_id FK
send_user_id string
recive_user_id string
created_at Date

今見るとかなり酷いところがありますね。
世に出していいのか...

しっかり改善していきます。

ChatGPTによる添削を受けました。
今回は特に多対多の扱いの改善をメインに行いました。
（他の修正については説明省略）

今回一番難しかったのが、DMの設計でした。

まず、初めは中間テーブルを用意していませんでした。

これでは以下の問題があります。

ということで、Roomテーブルが必要ということで作りました。

これはRoomテーブルがあることで以下の利点があります。
Roomテーブルを導入することで、「メッセージ」ではなく「会話」という単位でデータを扱えるようになります。

中間テーブルとしてroom_usersテーブルを追加します。

こうすることで、

という利点があります。
これで完成です！
JOINが必要になるためクエリは複雑になりますが、その分データ構造としては正規化され、拡張性の高い設計になります。

軽く書いておきます。

最初はupdated_atをつけていませんでした。
しかし、例えばもしバグが起こった時などに備え、ログをきちんと残すべきでした。
フロントばかりを学習してきていたので、UIとして必要かで判断してしまっている節がありました。

id と xxx_id が混在していました。
ルール決めて統一する必要がありました。

deleted_at を入れるか迷い、ChatGPTと議論しました。
結局今回は不要と判断し、省きました。

ということで完成したER図がこちら。

まだまだ改善点はあるかと思いますが、今回はこれにて終了。
もし改善点があればコメントいただけますと幸いです。

今回こちらの記事を参照させていただきました。