Database Design for Chat Applications: A Complete Beginner's Guide

2025-11-18 · Database Design · 8 min read

#database #postgresql #mongodb #chat #beginner

Building a chat application? This guide will teach you how to design your database from scratch. We'll cover users, conversations, and messages with clear examples you can use right away.

Database structure for chat applications

Understanding the Three Core Tables

Every chat application needs three main pieces of data:

  • Users - The people using your app
  • Conversations - Chat sessions or threads
  • Messages - The actual chat messages

Think of it like this: A user can have many conversations, and each conversation can have many messages.

Step 1: The Users Table

This is the simplest table. It stores information about each person using your app.

PostgreSQL - Users Tablesql
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

What each field means:

  • id - A unique identifier for each user (like a serial number)
  • email - User's email (must be unique)
  • name - User's display name
  • password_hash - Encrypted password (never store plain passwords!)
  • created_at - When the user signed up
  • updated_at - Last time user info was updated

Example data:

Sample Userssql
-- Insert a user
INSERT INTO users (email, name, password_hash) 
VALUES ('john@example.com', 'John Doe', '$2b$10$...');


-- Result in table:
-- id: '550e8400-e29b-41d4-a716-446655440000'
-- email: 'john@example.com'
-- name: 'John Doe'
-- created_at: '2024-11-18 10:00:00'

Step 2: The Conversations Table

This table stores each chat session. Each conversation belongs to one user.

PostgreSQL - Conversations Tablesql
CREATE TABLE conversations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title VARCHAR(255) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);


-- Index for faster queries
CREATE INDEX idx_conversations_user ON conversations(user_id);

What each field means:

  • id - Unique identifier for this conversation
  • user_id - Which user owns this conversation (links to users table)
  • title - A name for the conversation (e.g., "Project Planning")
  • created_at - When the conversation started
  • updated_at - Last message time

The important part: REFERENCES users(id) means this conversation MUST belong to a user that exists. If you delete a user, ON DELETE CASCADE automatically deletes all their conversations too.

Sample Conversationssql
-- Create a conversation for John
INSERT INTO conversations (user_id, title)
VALUES ('550e8400-e29b-41d4-a716-446655440000', 'My First Chat');


-- Result:
-- id: '660e8400-e29b-41d4-a716-446655440001'
-- user_id: '550e8400-e29b-41d4-a716-446655440000' (John's ID)
-- title: 'My First Chat'

Step 3: The Messages Table

This is where the actual chat messages are stored. Each message belongs to one conversation.

PostgreSQL - Messages Tablesql
CREATE TABLE messages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  conversation_id UUID NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
  role VARCHAR(20) NOT NULL CHECK (role IN ('user', 'assistant', 'system')),
  content TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  sequence_number INTEGER NOT NULL
);


-- Index for faster queries (very important!)
CREATE INDEX idx_messages_conversation ON messages(conversation_id, sequence_number);

What each field means:

  • id - Unique identifier for this message
  • conversation_id - Which conversation this message belongs to
  • role - Who sent it: 'user' (human), 'assistant' (AI), or 'system' (app)
  • content - The actual message text
  • created_at - When the message was sent
  • sequence_number - Message order (1, 2, 3, ...)
Sample Messagessql
-- User sends a message
INSERT INTO messages (conversation_id, role, content, sequence_number)
VALUES ('660e8400-e29b-41d4-a716-446655440001', 'user', 'Hello! How are you?', 1);


-- AI responds
INSERT INTO messages (conversation_id, role, content, sequence_number)
VALUES ('660e8400-e29b-41d4-a716-446655440001', 'assistant', 'I am doing well! How can I help you today?', 2);


-- User responds
INSERT INTO messages (conversation_id, role, content, sequence_number)
VALUES ('660e8400-e29b-41d4-a716-446655440001', 'user', 'Can you explain databases?', 3);

How They Connect: The Relationships

Here's how the tables link together:

Relationship Diagramtext
Users
  ↓ (one user has many conversations)
Conversations
  ↓ (one conversation has many messages)
Messages


Real example:
- John (user)
  - "Project Planning" (conversation)
    - "Let's discuss the timeline" (message 1)
    - "Sure, when should we start?" (message 2)
    - "Next Monday works" (message 3)
  - "Weekend Chat" (conversation)
    - "What are you doing this weekend?" (message 1)
    - "Going hiking!" (message 2)

Common Queries You'll Need

1. Get all conversations for a user

List User's Conversationssql
SELECT * FROM conversations 
WHERE user_id = '550e8400-e29b-41d4-a716-446655440000'
ORDER BY updated_at DESC;


-- Returns all of John's conversations, newest first

2. Get all messages in a conversation

Load Conversation Messagessql
SELECT * FROM messages
WHERE conversation_id = '660e8400-e29b-41d4-a716-446655440001'
ORDER BY sequence_number ASC;


-- Returns all messages in order (1, 2, 3, ...)

3. Get conversation with all messages (JOIN)

Complete Conversationsql
SELECT 
  c.id as conversation_id,
  c.title,
  c.created_at as conversation_created,
  m.id as message_id,
  m.role,
  m.content,
  m.created_at as message_created
FROM conversations c
LEFT JOIN messages m ON c.id = m.conversation_id
WHERE c.id = '660e8400-e29b-41d4-a716-446655440001'
ORDER BY m.sequence_number ASC;


-- Returns conversation info + all messages in one query

4. Create a new conversation with first message

New Conversationsql
-- Step 1: Create the conversation
INSERT INTO conversations (user_id, title)
VALUES ('550e8400-e29b-41d4-a716-446655440000', 'New Chat')
RETURNING id;
-- Returns: '770e8400-e29b-41d4-a716-446655440002'


-- Step 2: Add first message
INSERT INTO messages (conversation_id, role, content, sequence_number)
VALUES ('770e8400-e29b-41d4-a716-446655440002', 'user', 'Hello!', 1);

5. Add a new message to existing conversation

Add Messagesql
-- Get the next sequence number
SELECT MAX(sequence_number) FROM messages 
WHERE conversation_id = '660e8400-e29b-41d4-a716-446655440001';
-- Returns: 3


-- Insert new message with sequence_number = 4
INSERT INTO messages (conversation_id, role, content, sequence_number)
VALUES ('660e8400-e29b-41d4-a716-446655440001', 'assistant', 'Great question!', 4);


-- Update conversation timestamp
UPDATE conversations 
SET updated_at = NOW() 
WHERE id = '660e8400-e29b-41d4-a716-446655440001';

MongoDB Version (Document Database)

With MongoDB, you can store conversations differently - all messages inside the conversation document:

MongoDB - Users Collectionjavascript
// Users collection
{
  _id: ObjectId("507f1f77bcf86cd799439011"),
  email: "john@example.com",
  name: "John Doe",
  passwordHash: "$2b$10$...",
  createdAt: ISODate("2024-11-18T10:00:00Z")
}
MongoDB - Conversations Collection (with embedded messages)javascript
// Conversations collection
{
  _id: ObjectId("507f1f77bcf86cd799439012"),
  userId: ObjectId("507f1f77bcf86cd799439011"),
  title: "My First Chat",
  messages: [
    {
      _id: ObjectId("507f1f77bcf86cd799439013"),
      role: "user",
      content: "Hello! How are you?",
      createdAt: ISODate("2024-11-18T10:05:00Z"),
      sequenceNumber: 1
    },
    {
      _id: ObjectId("507f1f77bcf86cd799439014"),
      role: "assistant",
      content: "I'm doing well! How can I help?",
      createdAt: ISODate("2024-11-18T10:05:15Z"),
      sequenceNumber: 2
    },
    {
      _id: ObjectId("507f1f77bcf86cd799439015"),
      role: "user",
      content: "Can you explain databases?",
      createdAt: ISODate("2024-11-18T10:06:00Z"),
      sequenceNumber: 3
    }
  ],
  createdAt: ISODate("2024-11-18T10:05:00Z"),
  updatedAt: ISODate("2024-11-18T10:06:00Z")
}

MongoDB Queries:

Common MongoDB Operationsjavascript
// Get all conversations for a user
db.conversations.find({ 
  userId: ObjectId("507f1f77bcf86cd799439011") 
}).sort({ updatedAt: -1 });


// Get one conversation with all messages
db.conversations.findOne({ 
  _id: ObjectId("507f1f77bcf86cd799439012") 
});


// Add a new message to conversation
db.conversations.updateOne(
  { _id: ObjectId("507f1f77bcf86cd799439012") },
  { 
    $push: { 
      messages: {
        _id: ObjectId(),
        role: "assistant",
        content: "Great question!",
        createdAt: new Date(),
        sequenceNumber: 4
      }
    },
    $set: { updatedAt: new Date() }
  }
);


// Create new conversation
db.conversations.insertOne({
  userId: ObjectId("507f1f77bcf86cd799439011"),
  title: "New Chat",
  messages: [
    {
      _id: ObjectId(),
      role: "user",
      content: "Hello!",
      createdAt: new Date(),
      sequenceNumber: 1
    }
  ],
  createdAt: new Date(),
  updatedAt: new Date()
});

SQL vs MongoDB: Which Should You Use?

Choose PostgreSQL (SQL) if:

  • You're new to databases (SQL is more standard and easier to learn)
  • You need complex queries (analytics, reports, user stats)
  • Conversations might have thousands of messages
  • You want strong data integrity (relationships are enforced)
  • You might add features like shared conversations, teams, etc.

Choose MongoDB if:

  • You want faster development (no schema to define upfront)
  • Most conversations will be under 1,000 messages
  • You mainly fetch entire conversations at once
  • You're comfortable with JavaScript/JSON
  • You might need to scale to millions of users later

My Recommendation for Beginners:

Start with PostgreSQL. It's more structured, teaches you proper database design, and you won't hit limitations as your app grows. Plus, every developer should know SQL - it's been around for 50 years and will be around for 50 more.

Complete Working Example (Node.js + PostgreSQL)

Here's a complete example you can copy and use:

database.js - Database Connectionjavascript
const { Pool } = require('pg');


// Create connection pool
const pool = new Pool({
  user: 'your_username',
  host: 'localhost',
  database: 'chatapp',
  password: 'your_password',
  port: 5432,
});


module.exports = { pool };
chatService.js - Chat Functionsjavascript
const { pool } = require('./database');


// Get all conversations for a user
async function getUserConversations(userId) {
  const result = await pool.query(
    'SELECT * FROM conversations WHERE user_id = $1 ORDER BY updated_at DESC',
    [userId]
  );
  return result.rows;
}


// Get conversation with all messages
async function getConversation(conversationId) {
  const result = await pool.query(
    `SELECT 
      c.id, c.title, c.created_at, c.updated_at,
      json_agg(
        json_build_object(
          'id', m.id,
          'role', m.role,
          'content', m.content,
          'created_at', m.created_at
        ) ORDER BY m.sequence_number
      ) as messages
    FROM conversations c
    LEFT JOIN messages m ON c.id = m.conversation_id
    WHERE c.id = $1
    GROUP BY c.id`,
    [conversationId]
  );
  return result.rows[0];
}


// Create new conversation
async function createConversation(userId, title, firstMessage) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    
    // Create conversation
    const convResult = await client.query(
      'INSERT INTO conversations (user_id, title) VALUES ($1, $2) RETURNING *',
      [userId, title]
    );
    const conversation = convResult.rows[0];
    
    // Add first message
    await client.query(
      'INSERT INTO messages (conversation_id, role, content, sequence_number) VALUES ($1, $2, $3, $4)',
      [conversation.id, 'user', firstMessage, 1]
    );
    
    await client.query('COMMIT');
    return conversation;
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();
  }
}


// Add message to conversation
async function addMessage(conversationId, role, content) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    
    // Get next sequence number
    const seqResult = await client.query(
      'SELECT COALESCE(MAX(sequence_number), 0) + 1 as next_seq FROM messages WHERE conversation_id = $1',
      [conversationId]
    );
    const nextSeq = seqResult.rows[0].next_seq;
    
    // Insert message
    const msgResult = await client.query(
      'INSERT INTO messages (conversation_id, role, content, sequence_number) VALUES ($1, $2, $3, $4) RETURNING *',
      [conversationId, role, content, nextSeq]
    );
    
    // Update conversation timestamp
    await client.query(
      'UPDATE conversations SET updated_at = NOW() WHERE id = $1',
      [conversationId]
    );
    
    await client.query('COMMIT');
    return msgResult.rows[0];
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();
  }
}


module.exports = {
  getUserConversations,
  getConversation,
  createConversation,
  addMessage
};
app.js - Using the Functionsjavascript
const {
  getUserConversations,
  getConversation,
  createConversation,
  addMessage
} = require('./chatService');


async function demo() {
  const userId = '550e8400-e29b-41d4-a716-446655440000';
  
  // Create a new conversation
  console.log('Creating conversation...');
  const conv = await createConversation(
    userId,
    'Learning Databases',
    'Can you help me understand databases?'
  );
  console.log('Created:', conv);
  
  // Add AI response
  console.log('Adding AI response...');
  await addMessage(
    conv.id,
    'assistant',
    'Of course! Let me explain step by step...'
  );
  
  // Get full conversation
  console.log('Loading conversation...');
  const full = await getConversation(conv.id);
  console.log('Full conversation:', JSON.stringify(full, null, 2));
  
  // List all user conversations
  console.log('All conversations:');
  const all = await getUserConversations(userId);
  console.log(all);
}


demo().catch(console.error);

Best Practices & Tips

1. Always use indexes

Indexes make queries fast. Without them, your app will be slow.

sql
-- These indexes are CRITICAL for performance
CREATE INDEX idx_conversations_user ON conversations(user_id);
CREATE INDEX idx_messages_conversation ON messages(conversation_id, sequence_number);

2. Use transactions for related operations

When creating a conversation + first message, wrap in a transaction so if one fails, both are rolled back.

3. Add pagination for long conversations

Load Messages in Batchessql
-- Get last 50 messages
SELECT * FROM messages
WHERE conversation_id = $1
ORDER BY sequence_number DESC
LIMIT 50;


-- Get messages 51-100 (for infinite scroll)
SELECT * FROM messages
WHERE conversation_id = $1
ORDER BY sequence_number DESC
LIMIT 50 OFFSET 50;

4. Consider soft deletes

Instead of actually deleting data, mark it as deleted:

sql
-- Add deleted_at column
ALTER TABLE conversations ADD COLUMN deleted_at TIMESTAMPTZ;
ALTER TABLE messages ADD COLUMN deleted_at TIMESTAMPTZ;


-- "Delete" a conversation
UPDATE conversations SET deleted_at = NOW() WHERE id = $1;


-- Only show non-deleted conversations
SELECT * FROM conversations 
WHERE user_id = $1 AND deleted_at IS NULL;

Common Mistakes to Avoid

❌ Storing all messages in one TEXT field

Don't store all messages as JSON in a single column. Use proper tables!

❌ Not using foreign keys

Always use REFERENCES to link tables. It prevents orphaned data.

❌ Fetching all messages every time

Use LIMIT and OFFSET to paginate. Don't load 10,000 messages at once!

❌ Storing passwords in plain text

Always hash passwords with bcrypt before storing. Never store them plain!

Next Steps

You now understand the core database structure! Here's what to learn next:

  1. Add authentication - Learn about JWT tokens, sessions, password hashing
  2. Add file uploads - Store images/files that users send in chat
  3. Add search - Let users search their message history
  4. Add real-time updates - Use WebSockets so messages appear instantly

Resources

© 2025 Ovodo Blog