Chat Application Database Design with Redis Caching

What We Are Building

We design the database for a chat app like WhatsApp or Slack. We cover users, conversations (direct and group), messages, attachments, read receipts, and reactions. Then we explain why a database alone struggles at chat speed, and how Redis caching fixes it. The engine is MySQL / MariaDB.

The Nine Tables at a Glance

Users, Devices, and Blocks

A user is one account. A device is one phone or browser, used for push notifications and presence. A block row hides two people from each other.

-- ============ USERS ============
CREATE TABLE users (
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    username      VARCHAR(40)     NOT NULL,
    display_name  VARCHAR(120)    NOT NULL,
    phone         VARCHAR(20)     NULL,
    avatar_url    VARCHAR(255)    NULL,
    last_seen_at  TIMESTAMP       NULL,      -- hot column; better kept in Redis (see caching)
    created_at    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_username (username),
    UNIQUE KEY uq_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============ DEVICES (push tokens + presence) ============
CREATE TABLE devices (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id         BIGINT UNSIGNED NOT NULL,     -- FK -> users
    platform        ENUM('android','ios','web') NOT NULL,
    push_token      VARCHAR(255)    NULL,
    last_active_at  TIMESTAMP       NULL,
    PRIMARY KEY (id),
    KEY ix_dev_user (user_id),
    CONSTRAINT fk_dev_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============ BLOCKS ============
CREATE TABLE blocks (
    blocker_id  BIGINT UNSIGNED NOT NULL,   -- FK -> users (who blocks)
    blocked_id  BIGINT UNSIGNED NOT NULL,   -- FK -> users (who is blocked)
    created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (blocker_id, blocked_id),
    CONSTRAINT fk_block_er FOREIGN KEY (blocker_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_block_ed FOREIGN KEY (blocked_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Conversations and Members

One table holds both chat types. A direct conversation has two members. A group has many, with a title and admins. The conversation_members table also stores last_read_message_id, which powers unread counts.

-- ============ CONVERSATIONS ============
CREATE TABLE conversations (
    id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    type             ENUM('direct','group') NOT NULL,
    title            VARCHAR(150)    NULL,        -- group name; NULL for direct
    created_by       BIGINT UNSIGNED NULL,        -- FK -> users
    last_message_id  BIGINT UNSIGNED NULL,        -- pointer for chat-list order (no FK: avoids a cycle)
    created_at       TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY ix_conv_type (type),
    CONSTRAINT fk_conv_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============ CONVERSATION MEMBERS ============
CREATE TABLE conversation_members (
    conversation_id       BIGINT UNSIGNED NOT NULL,   -- FK -> conversations
    user_id               BIGINT UNSIGNED NOT NULL,   -- FK -> users
    role                  ENUM('member','admin') NOT NULL DEFAULT 'member',
    last_read_message_id  BIGINT UNSIGNED NULL,       -- drives unread counts
    is_muted              TINYINT(1)      NOT NULL DEFAULT 0,
    joined_at             TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (conversation_id, user_id),
    KEY ix_member_user (user_id),
    CONSTRAINT fk_cm_conv FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
    CONSTRAINT fk_cm_user FOREIGN KEY (user_id)         REFERENCES users(id)         ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ER Diagram (1/2) — Identity and Rooms

Each arrow reads one-to-many (1 → N). blocks links to users twice (blocker and blocked).

Messages, Attachments, Receipts, Reactions

The messages table is the heart of the app. It uses millisecond timestamps so messages sort in the exact order they were sent. Attachments, receipts, and reactions hang off each message.

-- ============ MESSAGES ============
CREATE TABLE messages (
    id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    conversation_id  BIGINT UNSIGNED NOT NULL,   -- FK -> conversations
    sender_id        BIGINT UNSIGNED NOT NULL,   -- FK -> users
    type             ENUM('text','image','file','system') NOT NULL DEFAULT 'text',
    body             TEXT            NULL,
    reply_to_id      BIGINT UNSIGNED NULL,       -- FK -> messages (a reply)
    created_at       TIMESTAMP(3)    NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    edited_at        TIMESTAMP(3)    NULL,
    deleted_at       TIMESTAMP(3)    NULL,      -- soft delete
    PRIMARY KEY (id),
    KEY ix_msg_timeline (conversation_id, created_at),  -- the "load chat" query
    KEY ix_msg_sender (sender_id),
    CONSTRAINT fk_msg_conv   FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
    CONSTRAINT fk_msg_sender FOREIGN KEY (sender_id)       REFERENCES users(id),
    CONSTRAINT fk_msg_reply  FOREIGN KEY (reply_to_id)     REFERENCES messages(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============ MESSAGE ATTACHMENTS ============
CREATE TABLE message_attachments (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    message_id  BIGINT UNSIGNED NOT NULL,   -- FK -> messages
    url         VARCHAR(255)    NOT NULL,
    media_type  VARCHAR(50)     NOT NULL,   -- image/jpeg, application/pdf
    size_bytes  BIGINT UNSIGNED NULL,
    PRIMARY KEY (id),
    KEY ix_att_msg (message_id),
    CONSTRAINT fk_att_msg FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============ MESSAGE RECEIPTS (delivered / read) ============
CREATE TABLE message_receipts (
    message_id  BIGINT UNSIGNED NOT NULL,   -- FK -> messages
    user_id     BIGINT UNSIGNED NOT NULL,   -- FK -> users
    status      ENUM('delivered','read') NOT NULL,
    at          TIMESTAMP(3)    NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    PRIMARY KEY (message_id, user_id),
    KEY ix_rcpt_user (user_id),
    CONSTRAINT fk_rcpt_msg  FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE CASCADE,
    CONSTRAINT fk_rcpt_user FOREIGN KEY (user_id)    REFERENCES users(id)    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============ REACTIONS ============
CREATE TABLE reactions (
    message_id  BIGINT UNSIGNED NOT NULL,   -- FK -> messages
    user_id     BIGINT UNSIGNED NOT NULL,   -- FK -> users
    emoji       VARCHAR(16)     NOT NULL,
    created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (message_id, user_id, emoji),
    CONSTRAINT fk_react_msg  FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE CASCADE,
    CONSTRAINT fk_react_user FOREIGN KEY (user_id)    REFERENCES users(id)    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ER Diagram (2/2) — Messaging

Messages sit at the centre. Dashed boxes are tables defined earlier. A message can also reply to another message (self link).

UML Use-Case Diagram

Two actors. A normal user chats; a group admin also manages the group.

Why a Database Alone Struggles

The schema above is correct and durable. But a busy chat app asks the database for the same hot data thousands of times a second. A disk-based database was not built for that. Here are the pain points.

Redis to the Rescue — Caching Architecture

The app checks Redis first. On a hit it answers in memory. On a miss it reads MySQL, then fills the cache so the next read is fast.

What to Cache, and How

Redis commands for the common cases

# Cache the newest messages of conversation 42 (keep only the last 100)
LPUSH chat:42:messages "{id:9001,from:57,body:'hi'}"
LTRIM chat:42:messages 0 99
LRANGE chat:42:messages 0 49          # read last 50, no DB hit

# Presence: mark user online for 30 seconds; heartbeat refreshes it
SETEX presence:user:57 30 "online"
GET presence:user:57                    # NULL means offline

# Unread counter per user per conversation
INCR unread:user:57:conv:42            # new message arrives
SET  unread:user:57:conv:42 0          # user opens the chat

# Typing indicator: shows for 5 seconds, then vanishes on its own
SETEX typing:conv:42:user:57 5 "1"

# Real-time fan-out: tell every app server a new message landed
PUBLISH conv:42 "{id:9001,from:57}"

Caching Patterns Compared

Sending a Message — The Full Flow

-- 1) durable write in MySQL
INSERT INTO messages (conversation_id, sender_id, type, body)
VALUES (42, 57, 'text', 'Running 5 minutes late!');
SET @msg_id = LAST_INSERT_ID();

-- keep the chat list ordered by newest
UPDATE conversations SET last_message_id = @msg_id WHERE id = 42;

# 2) update the cache, then 3) fan out (run right after the DB write)
LPUSH chat:42:messages "{id:9001,from:57,body:'Running 5 minutes late!'}"
LTRIM chat:42:messages 0 99
INCR  unread:user:88:conv:42          # the other member
PUBLISH conv:42 "{id:9001,from:57}"

Consistency — Keep Cache and Database in Step

A cache is a copy. Copies go stale. The rule is simple: MySQL is always right; Redis is a fast copy you can rebuild. Design so a wrong or missing cache value is never a disaster.

Golden Rules