Skip to content
TheChatArchiveThe Chat Archive
o3-2025-04-16· OpenAIHugging Face LMArena Human Preference 140K

DBのテーブル構成を考えてください。…

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with DeepSeekdeepseek-v3-0324’s answer.
  1. User

    DBのテーブル構成を考えてください。

    目的としてはチャット画面で「ユーザーからのメッセージ」「システムが送信したメッセージ」を同じ画面でタイムライン表示したいです。画像や動画はファイルに保存し、ファイルパスのみDB保存します。

    ブロードキャストなど、多人数に同時に送った場合のメッセージを、複製して保持するか、デリバーテーブルなどを用意してノーマライズするかを迷っています。

    MySQLですが、数千万行になってもパフォーマンスが極端に劣化しない方式が良いです。

    【記録したいデータ】

    • LINE公式アカウントでユーザーからWebhook経由で受信したイベント

    最低保持したい内容としては、送信先チャネルID、送信元、日付時刻、イベントID、イベントタイプ、ソースタイプ、メッセージタイプ、メッセージ内容

    種類: /**

     * イベントタイプ
    
     */
    
    const WH_EVENT_TYPE = array(
    
        1  => 'message',
    
        2  => 'unsend',
    
        3  => 'follow',
    
        4  => 'unfollow',
    
        5  => 'join',
    
        6  => 'leave',
    
        7  => 'memberJoined',
    
        8  => 'memberLeft',
    
        9  => 'postback',
    
        10 => 'videoPlayComplete',
    
        11 => 'beacon',
    
        12 => 'accountLink',
    
        13 => 'things',
    
        14 => 'membership',
    
    );
    

     ソース元はユーザー、グループ、ルーム

     メッセージの場合はさらにタイプ分類がある。payloadはJSONで保存。

    /**
    
     * メッセージタイプ
    
     */
    
    const WH_MESSAGE_TYPE = array(
    
        1 => 'text',
    
        2 => 'image',
    
        3 => 'video',
    
        4 => 'audio',
    
        5 => 'file',
    
        6 => 'location',
    
        7 => 'sticker',
    
    );
    
    • アプリからLINE公式アカウントの友達(ユーザー)へ送信したメッセージ

      種類: ブロードキャスト(すべての友達へ)/マルチキャスト(複数の友達)/プッシュ(一人ずつ)/reply(応答なので一人)

      最低保持したい内容: ソース(送信経路:一斉配信なのかチャット画面からなのか、オーディエンスからなのか)、送信者(WP ユーザーIDorSystemなど)、送信先、メッセージタイプ、メッセージ内容(payloadはJSONでOK)、ステータス

  2. Assistant

    以下では、

    ①「構造がシンプルで分かりやすい」
    ②「ブロードキャスト/マルチキャストでも行数爆発を起こさない」
    ③「1ユーザーのタイムラインを高速で引ける」

    を同時に満たすことを目標に MySQL 向けの論理設計を提案します。
    ※RDB のみで完結する案です(NoSQL 併用などは考慮外)。


    1. 全体俯瞰

     line_channels    (LINEチャネル)
     line_contacts    (友だち・グループ・ルーム)
     messages         (論理メッセージ本体)          ★=数千万行になるテーブル
     deliveries       (配信先 × メッセージ)         ★=行数爆発対策のキモ
     media_files      (画像/動画/ファイルの実体)
     webhook_events   (LINE→システムの生Webhookログ)
     message_types    (マスタ)
     event_types      (マスタ)
     delivery_status  (マスタ)
     wp_users         (送信者:WordPressユーザー)   ※既存テーブルを利用
    

    [image: ER図] open
    (簡易ER図/矢印はFK)


    2. テーブル定義(DDLイメージ)

    -- 1) チャネル(LINE公式アカウント)
    CREATE TABLE line_channels (
      channel_id        INT UNSIGNED NOT NULL AUTO_INCREMENT,
      line_channel_id   VARCHAR(50)  NOT NULL,     -- LINE側のID
      name              VARCHAR(100) NOT NULL,
      PRIMARY KEY(channel_id),
      UNIQUE(line_channel_id)
    );
    
    -- 2) 友だち・グループ・ルーム(送受信主体)
    CREATE TABLE line_contacts (
      contact_id        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      channel_id        INT UNSIGNED    NOT NULL,
      line_id           VARCHAR(50)     NOT NULL,  -- userId / groupId / roomId
      source_type       ENUM('user','group','room') NOT NULL,
      display_name      VARCHAR(100),
      PRIMARY KEY(contact_id),
      UNIQUE(channel_id, line_id),
      FOREIGN KEY(channel_id) REFERENCES line_channels(channel_id)
    );
    
    -- 3) メッセージ本体(入出力両方を格納)
    CREATE TABLE messages (
      message_id        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      channel_id        INT UNSIGNED    NOT NULL,          -- どのLINEチャネルか
      direction         ENUM('in','out') NOT NULL,         -- in=受信, out=送信
      -- 送信者:in時=contact_id、out時=wp_user_id or NULL(system)
      sender_contact_id BIGINT UNSIGNED,
      sender_wp_id      BIGINT UNSIGNED,
      event_type_id     TINYINT UNSIGNED,                  -- inで使用
      message_type_id   TINYINT UNSIGNED,                  -- text/image/...
      content_json      JSON          NOT NULL,            -- payload全文
      created_at        DATETIME      NOT NULL,
      -- 索引
      PRIMARY KEY(message_id),
      INDEX idx_channel_created (channel_id, created_at),
      INDEX idx_sender_contact (sender_contact_id),
      FOREIGN KEY(channel_id)        REFERENCES line_channels(channel_id),
      FOREIGN KEY(sender_contact_id) REFERENCES line_contacts(contact_id),
      FOREIGN KEY(sender_wp_id)      REFERENCES wp_users(ID),
      FOREIGN KEY(event_type_id)     REFERENCES event_types(event_type_id),
      FOREIGN KEY(message_type_id)   REFERENCES message_types(message_type_id)
    ) ENGINE=InnoDB;
    
    -- 4) 配信テーブル:メッセージ × 受信者
    CREATE TABLE deliveries (
      delivery_id       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      message_id        BIGINT UNSIGNED NOT NULL,
      recipient_id      BIGINT UNSIGNED NOT NULL,      -- line_contacts.contact_id
      status_id         TINYINT UNSIGNED NOT NULL,     -- 送信済/失敗/既読...
      delivered_at      DATETIME,
      read_at           DATETIME,
      PRIMARY KEY(delivery_id),
      UNIQUE(message_id, recipient_id),                -- 同一組合せは1行
      INDEX idx_recipient_time (recipient_id, delivered_at),
      FOREIGN KEY(message_id)   REFERENCES messages(message_id),
      FOREIGN KEY(recipient_id) REFERENCES line_contacts(contact_id),
      FOREIGN KEY(status_id)    REFERENCES delivery_status(status_id)
    ) ENGINE=InnoDB
      PARTITION BY HASH(recipient_id) PARTITIONS 32;  -- 🔑 パーティショニング
    
    -- 5) 画像/動画/ファイル
    CREATE TABLE media_files (
      media_id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      message_id        BIGINT UNSIGNED NOT NULL,
      media_type        ENUM('image','video','audio','file') NOT NULL,
      file_path         VARCHAR(255) NOT NULL,
      thumbnail_path    VARCHAR(255),
      metadata_json     JSON,
      PRIMARY KEY(media_id),
      FOREIGN KEY(message_id) REFERENCES messages(message_id)
    );
    
    -- 6) Webhook の生イベント
    CREATE TABLE webhook_events (
      event_id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      message_id        BIGINT UNSIGNED,              -- messagesへ後から紐付け可
      raw_payload       JSON          NOT NULL,
      received_at       DATETIME      NOT NULL,
      PRIMARY KEY(event_id),
      INDEX idx_received_at (received_at)
    );
    
    -- 7) マスタ類
    CREATE TABLE event_types (
      event_type_id TINYINT UNSIGNED PRIMARY KEY,
      name          VARCHAR(30) NOT NULL
    );
    /* 例:
    INSERT INTO event_types VALUES
    (1,'message'),(2,'unsend'),(3,'follow'), ... ;
    */
    
    CREATE TABLE message_types (
      message_type_id TINYINT UNSIGNED PRIMARY KEY,
      name            VARCHAR(20) NOT NULL
    );
    
    CREATE TABLE delivery_status (
      status_id TINYINT UNSIGNED PRIMARY KEY,
      name      VARCHAR(20) NOT NULL
    );
    /* 1=queued,2=sent,3=fail,4=read ... */
    

    3. なぜ「messages + deliveries」方式を採用するか?

    方式メリットデメリット
    ① 受信者ごとにメッセージを複製JOIN が不要。単一テーブルで完結ブロードキャストで行数が n倍 (最悪×数十万)
    ② 本体+デリバリーテーブル(採用)行数爆発を受信者数のみに隔離。スタンプ・画像等の重複保存も防止タイムライン取得時に JOIN が必要

    ✔︎ 数千万行規模になるのは「messages」のみ。
    ✔︎ ブロードキャスト 1通 → deliveries 100万行増えても、
    recipient_id でハッシュパーティショニングしておけば 1ユーザーのタイムライン取得は 1パーティションしか走らない のでスケールしやすい。


    4. タイムライン取得クエリ例

    「A さん(contact_id=?)のタイムライン」を取得。
    a) Aさんからの受信メッセージ (=sender_contact_id に本人)
    b) Aさん宛ての送信メッセージ (=deliveries.recipient_id に本人)
    を UNION して時系列ソート:

    (
      -- in(受信)
      SELECT
        m.message_id,
        m.direction,
        m.created_at,
        m.message_type_id,
        m.content_json
      FROM messages m
      WHERE m.sender_contact_id = :contact_id
    )
    UNION ALL
    (
      -- out(配信)
      SELECT
        m.message_id,
        m.direction,
        m.created_at,
        m.message_type_id,
        m.content_json
      FROM deliveries d
      JOIN messages m ON m.message_id = d.message_id
      WHERE d.recipient_id = :contact_id
    )
    ORDER BY created_at ASC
    LIMIT 50 OFFSET :offset;
    

    ポイント
    ・deliveries に INDEX(recipient_id, delivered_at) があるので INDEX RANGE + PK で高速。
    ・messages 側は PK で直接参照。


    5. パフォーマンス・運用 Tips

    1. ID 型

      • BIGINT UNSIGNED(約 9.2e18)で安心。
      • 自動増分はほぼ挿入順=時間順なのでクラスタリング効果も得られる。
    2. パーティショニング

      • deliveries を HASH(recipient_id) (or LINEAR HASH) で 32〜256 分割。
        ⇒ 1ユーザー照会は単一パーティションに限定。
      • messages は 日付 RANGE (月単位)で古いデータをアーカイブしやすく。
    3. カバーリングインデックス

      • タイムラインでよく使う列(recipient_id, delivered_at, message_id)を含める。
      • JSON の中身検索が必要であれば GENERATED COLUMN+INDEX で対応。
    4. 画像・動画

      • ファイルは S3 等に置き media_files.file_path に URI を保存。
      • 画像複数枚でも media_files が1:Nで管理。
    5. 「既読」管理

      • deliveries の read_at IS NULL/NOT NULL で表現。
      • 既読通知が来るたびに UPDATE。
    6. TTL / アーカイブ

      • 例えば messages を 2年で分割テーブルに抜き、deliveries も同時に移すスクリプトを用意。
      • 参照頻度が下がる古いデータは別サーバへ MySQL 8 の TABLESPACE 移動等でコスト節約。

    6. 使い分けまとめ

    ・1対1メッセージ ➜ messages 1行 + deliveries 1行
    ・マルチキャスト(10人) ➜ messages 1行 + deliveries 10行
    ・ブロードキャスト(全友だち100万) ➜ messages 1行 + deliveries 100万行
     → 行数は増えるが、問い合わせは recipient_id で分散されるため実運用では問題になりにくい。


    7. 今後拡張イメージ

    • リアクション(いいね等)
      message_reactions(message_id, contact_id, type, reacted_at)
    • スレッド(reply_to)
      messages に parent_message_id を持たせる
    • ボット or AI 応答ログ
      sender_wp_id = NULL + sender_contact_id = NULL, direction='out'

    8. まとめ

    ・「メッセージ本体は1行だけ保存」「誰に届いたかは deliveries で管理」というオーソドックスな正規化を選択。
    ・1ユーザーのタイムライン取得に必要な行は deliveries の 当該ユーザー分だけ なので、総行数が億に達してもスケールしやすい。
    ・パーティショニング & 適切なインデックスで数千万行規模の MySQL でも十分実績のある構成。

    以上をベースに実装すれば、要件(タイムライン表示・高パフォーマンス・ブロードキャスト対応)を満たせるはずです。👍

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.