Ainul Yaqin
System Architecture

Database Design Patterns for Modern Applications

Explore essential database design patterns including CQRS, Event Sourcing, and Data Mesh architectures for building scalable and maintainable systems.

Portrait of David Kim
David KimDatabase Architect
Sep 12, 2025•3 min read
DatabaseDesign-PatternsCqrs
All tags:
Event-SourcingArchitecture
Illustration for Database Design Patterns for Modern Applications

Database Design Patterns for Modern Applications

As applications grow in complexity and scale, traditional database approaches often fall short. Modern database design patterns provide solutions for scalability, performance, and maintainability.

CQRS (Command Query Responsibility Segregation)

Separate read and write operations for better performance:

Write Model (Commands)

interface CreateUserCommand {
  userId: string;
  email: string;
  name: string;
}

class UserCommandHandler {
  async handleCreateUser(command: CreateUserCommand) {
    // Validate command
    // Save to write database
    await this.eventStore.saveEvent({
      type: "UserCreated",
      data: command,
    });
  }
}

Read Model (Queries)

interface UserView {
  userId: string;
  email: string;
  name: string;
  createdAt: Date;
}

class UserQueryHandler {
  async getUserById(userId: string): Promise<UserView> {
    return await this.readDb.users.findOne({ userId });
  }
}

Event Sourcing

Store state changes as a sequence of events:

interface DomainEvent {
  eventId: string;
  aggregateId: string;
  eventType: string;
  data: any;
  timestamp: Date;
}

class EventStore {
  async saveEvent(event: DomainEvent) {
    await this.db.events.insertOne(event);
  }

  async getEvents(aggregateId: string): Promise<DomainEvent[]> {
    return await this.db.events
      .find({ aggregateId })
      .sort({ timestamp: 1 })
      .toArray();
  }
}

Data Mesh Architecture

Decentralize data ownership and architecture:

Domain-Driven Design

// Product domain
class ProductAggregate {
  constructor(private events: DomainEvent[]) {
    this.applyEvents(events);
  }

  updatePrice(newPrice: number) {
    this.applyEvent({
      type: "ProductPriceUpdated",
      data: { newPrice, oldPrice: this.price },
    });
  }
}

Data Products

Each domain owns its data products:

  • Product Catalog: Owned by Product team
  • User Profiles: Owned by User Management team
  • Order History: Owned by Order Processing team

Repository Pattern

Abstract data access logic:

interface Repository<T> {
  findById(id: string): Promise<T | null>;
  save(entity: T): Promise<void>;
  delete(id: string): Promise<void>;
}

class MongoUserRepository implements Repository<User> {
  constructor(private collection: Collection) {}

  async findById(id: string): Promise<User | null> {
    const doc = await this.collection.findOne({ _id: id });
    return doc ? this.mapToUser(doc) : null;
  }

  async save(user: User): Promise<void> {
    await this.collection.replaceOne(
      { _id: user.id },
      this.mapToDocument(user),
      { upsert: true },
    );
  }
}

Database Sharding Strategies

Horizontal Sharding

Distribute data across multiple databases:

// Hash-based sharding
function getShard(userId) {
  const hash = crypto.createHash("md5").update(userId).digest("hex");
  const shardNumber = parseInt(hash.substring(0, 8), 16) % totalShards;
  return shardNumber;
}

Vertical Sharding

Split tables across different databases:

  • User Data: user_profiles, user_preferences
  • Content Data: posts, comments, media
  • Analytics Data: events, metrics, logs

Indexing Strategies

Composite Indexes

Optimize complex queries:

-- For queries filtering by status and created_at
CREATE INDEX idx_orders_status_created
ON orders (status, created_at DESC);

-- For queries filtering by user_id and type
CREATE INDEX idx_notifications_user_type
ON notifications (user_id, type, created_at DESC);

Partial Indexes

Index only relevant data:

-- Index only active users
CREATE INDEX idx_active_users
ON users (email)
WHERE active = true;

Data Consistency Patterns

Saga Pattern

Manage distributed transactions:

class OrderSaga {
  async processOrder(orderId: string) {
    try {
      await this.reserveInventory(orderId);
      await this.processPayment(orderId);
      await this.shipOrder(orderId);
      await this.completeOrder(orderId);
    } catch (error) {
      await this.compensateOrder(orderId);
    }
  }
}

Eventual Consistency

Accept temporary inconsistencies for better performance:

class ReadModelUpdater {
  async handleUserCreated(event: UserCreatedEvent) {
    // Update read model asynchronously
    await this.updateUserView(event.userId, event.data);
  }
}

Migration Strategies

Blue-Green Migrations

Zero-downtime schema changes:

-- Create new table with new schema
CREATE TABLE users_v2 (
  id UUID PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Migrate data
INSERT INTO users_v2 (id, email, name, created_at)
SELECT id, email, name, created_at FROM users;

-- Switch application to use new table
-- Remove old table after verification

Monitoring and Observability

Query Performance Monitoring

Track slow queries and optimize:

-- Enable query logging
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- Analyze query performance
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

Database Health Checks

Monitor database metrics:

  • Connection pool utilization
  • Query execution times
  • Deadlock frequency
  • Replication lag

Conclusion

Modern database design requires balancing performance, scalability, and maintainability. Choose patterns that fit your specific use case and team capabilities, and always plan for evolution as your system grows.