🧩

テーブル設計: 記事投稿サイト

2024/05/27に公開

architecture

記事投稿サービス

Qiitaのような記事投稿サービスのテーブル設計について考えたい。

会員、記事のstatusのライフサイクルを切り出す。statusカラムによる負債を生み出さないようにしたい

前提

この記事で説明したロングタームイベントパターンを前提としています。

要件定義

アクセス解析

MAU: 26.2M件と想定できる。

機能要件

ざっくりと、

OGPリンク内に記事のタイトル、著者のプロフィールの一部を含める。
ユーザーは会員登録をしたのち、記事内に表示されるプロフィールを設定できる。
ユーザーは会員登録をしたのち、記事を公開できる。
ユーザーは記事を下書きで保存できる。また、公開後も下書きに戻せる(アクセスすると404になる)
ユーザーは記事内に画像を1つあたりのアップロードできる(容量5MB以内を想定)
ユーザーが退会した場合、ログアウトされてマイページにアクセスできなくなる。
ユーザーが退会した場合、公開記事にアクセスすると404になる。

上記を実装するため、下記のように実装する

SSRで記事を表示させる。
ログインはfirebase login(メールアドレスとパスワード入力形式のみ)を想定する。
ログインしたらfirebaseが生成したmetadataをfirestoreに保存する。
下書き記事は、テーブル内に保存する。
公開記事は、GCSにファイルとして保存する。
公開記事のファイルのうち過去のものは、バッチ処理でファイル、過去の公開レコードを削除する。
GCSに保存した投稿済み記事ファイルはCDNでキャッシュする(有効期限は一旦1週間とする)

システム構成図

一般的なウェブアプリケーションの構成を想定。
図を作成中

テーブル設計

各テーブルについて1つずつ説明していく。

会員

member_status_activitiesテーブル

statusはロングタームイベントパターンで値を保存する。
viewテーブルから最新の状態を取得する。

各ステータスのテーブルについて、

member_pending_verificationは会員登録を送信して、firebaseでメールアドレス未認証の場合にレコードを保存する。
member_verfiedはfirebaseメールアドレス認証完了した場合にレコードを保存する。
member_activeは会員がログイン可能な状態になった場合にレコードを保存する。
member_resignedは会員が退会した場合にレコードを保存する(サイトにログインできなくなる)
member_disabledは会員がfirebaseログインできなくなった時に保存する
member_bannedは管理者が会員をbanした時に保存する(サイトにログインできなくなる)
※イベントが変更された理由、操作した管理者のidを保存する
member_restoredは管理者が会員を復元した時に保存する(併せて、member_activeにも再度レコードを保存する)
※イベントが変更された理由、操作した管理者のidを保存する

member_status_activityテーブル

member_status_activitiesテーブルのうち、member_idごとの最新のレコードのみをもつ。
※(hasuraの場合group byする機能がないため)事前に集計したviewを用意してREADしやすくするため。サーバーをもつ場合はviewの代わりにレポジトリ層で吸収できそう。

会員の設定項目

profilesテーブル

ログインユーザーのプライベートな設定値を保存する。
※記事の上部にアイコンやIDとして表示されます。

accountsテーブル

ログインユーザーのプライベートな設定値を保存する。

記事

itemsテーブル

記事idと著者idの関連レコードを保存する。
※中間テーブルにすべきかは迷う（メリットデメリットあれば教えていただきたいです）

item_status_activites

statusはロングタームイベントパターンで値を保存する。
viewテーブルから最新の状態を取得する。

各ステータスのテーブルについて、

item_draftedは記事を下書きで保存した場合にレコードを保存する。
item_publicは記事を公開で保存した場合にレコードを保存する。
item_archivedは記事を削除した場合にレコードを保存する。
item_force_archivedは、管理者が記事を削除した場合にレコードを保存する。
※イベントが変更された理由、操作した管理者のidを保存する

drafted_item_contentsテーブル

下書き記事の内容
タイトル、本文(文字列)、更新日時

下書きの場合はファイルを保存しない。
DBに文字列として保存する(常に上書きする)

public_item_contentsテーブル

公開記事の内容
タイトル、ファイルパス（GCS)

public_item_content_latestテーブル

公開記事の内容のうち最新のもの
※(hasuraの場合group byする機能がないため)事前に集計したviewを用意してREADしやすくするため。サーバーをもつ場合はviewの代わりにレポジトリ層で吸収できそう。