Database Design Patterns for Modern Applications

Introduction

Database design patterns are proven solutions to common data modeling problems. They provide reusable templates for organizing data efficiently, ensuring data integrity, and optimizing performance. Understanding these patterns is crucial for building scalable and maintainable applications.

In this comprehensive guide, we'll explore essential database design patterns for both SQL and NoSQL databases, with practical examples and implementation strategies for modern applications.

SQL vs NoSQL Design Patterns

The choice between SQL and NoSQL databases significantly impacts your design patterns:

// SQL Approach - Structured Data
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT REFERENCES users(id),
  total DECIMAL(10,2),
  status ENUM('pending', 'completed', 'cancelled'),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

// NoSQL Approach - Flexible Schema
{
  "_id": ObjectId("..."),
  "name": "John Doe",
  "email": "john@example.com",
  "createdAt": ISODate("2024-01-15"),
  "orders": [
    {
      "id": "order_123",
      "total": 99.99,
      "status": "completed",
      "items": [...],
      "createdAt": ISODate("2024-01-20")
    }
  ]
}

Fundamental Design Patterns

1. Singleton Pattern

Ensure a class has only one instance and provide global access to it:

// Database Connection Singleton
class DatabaseConnection {
  private static instance: DatabaseConnection;
  private connection: any;

  private constructor() {
    this.connection = this.createConnection();
  }

  public static getInstance(): DatabaseConnection {
    if (!DatabaseConnection.instance) {
      DatabaseConnection.instance = new DatabaseConnection();
    }
    return DatabaseConnection.instance;
  }

  private createConnection() {
    // Initialize database connection
    return new DatabaseClient(process.env.DB_URL);
  }

  public getConnection() {
    return this.connection;
  }
}

// Usage
const db1 = DatabaseConnection.getInstance();
const db2 = DatabaseConnection.getInstance();
console.log(db1 === db2); // true

2. Factory Pattern

Create objects without specifying the exact class:

// Database Factory
interface DatabaseConfig {
  type: 'mysql' | 'postgresql' | 'mongodb';
  host: string;
  port: number;
  database: string;
}

class DatabaseFactory {
  static createConnection(config: DatabaseConfig) {
    switch (config.type) {
      case 'mysql':
        return new MySQLConnection(config);
      case 'postgresql':
        return new PostgreSQLConnection(config);
      case 'mongodb':
        return new MongoDBConnection(config);
      default:
        throw new Error(`Unsupported database type: ${config.type}`);
    }
  }
}

// Usage
const config = { type: 'mysql', host: 'localhost', port: 3306, database: 'myapp' };
const db = DatabaseFactory.createConnection(config);

3. Repository Pattern

Abstract the data layer and provide a clean interface:

// Repository Interface
interface IRepository {
  findById(id: string): Promise;
  findAll(): Promise;
  create(data: Partial): Promise;
  update(id: string, data: Partial): Promise;
  delete(id: string): Promise;
}

// User Repository Implementation
class UserRepository implements IRepository {
  constructor(private db: Database) {}

  async findById(id: string): Promise {
    const result = await this.db.query('SELECT * FROM users WHERE id = ?', [id]);
    return result[0] || null;
  }

  async create(userData: Partial): Promise {
    const result = await this.db.query(
      'INSERT INTO users (name, email) VALUES (?, ?)',
      [userData.name, userData.email]
    );
    return this.findById(result.insertId);
  }

  async update(id: string, userData: Partial): Promise {
    await this.db.query(
      'UPDATE users SET name = ?, email = ? WHERE id = ?',
      [userData.name, userData.email, id]
    );
    return this.findById(id);
  }
}

Data Modeling Patterns

1. One-to-Many Relationship

Common pattern for parent-child relationships:

-- SQL Implementation
CREATE TABLE categories (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  parent_id INT NULL,
  FOREIGN KEY (parent_id) REFERENCES categories(id)
);

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(200) NOT NULL,
  category_id INT NOT NULL,
  price DECIMAL(10,2),
  FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- NoSQL Implementation
{
  "_id": ObjectId("..."),
  "name": "Electronics",
  "products": [
    {
      "name": "Laptop",
      "price": 999.99,
      "specs": { "cpu": "i7", "ram": "16GB" }
    },
    {
      "name": "Mouse",
      "price": 29.99,
      "specs": { "type": "wireless", "dpi": "1600" }
    }
  ]
}

2. Many-to-Many Relationship

Handle complex relationships between entities:

-- SQL Implementation with Junction Table
CREATE TABLE students (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE courses (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE student_courses (
  student_id INT REFERENCES students(id),
  course_id INT REFERENCES courses(id),
  enrollment_date DATE,
  PRIMARY KEY (student_id, course_id)
);

-- NoSQL Implementation
{
  "_id": ObjectId("..."),
  "name": "John Doe",
  "enrollments": [
    {
      "course_id": ObjectId("..."),
      "course_name": "Mathematics",
      "enrollment_date": ISODate("2024-01-15"),
      "grade": "A"
    },
    {
      "course_id": ObjectId("..."),
      "course_name": "Physics",
      "enrollment_date": ISODate("2024-01-16"),
      "grade": "B+"
    }
  ]
}

Performance Optimization Patterns

1. Indexing Strategy

Proper indexing is crucial for query performance:

-- SQL Indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status_date ON orders(status, created_at);

-- Composite Index for complex queries
CREATE INDEX idx_products_category_price ON products(category_id, price DESC);

-- NoSQL Indexes
db.users.createIndex({ email: 1 }, { unique: true });
db.orders.createIndex({ userId: 1, status: 1, createdAt: -1 });
db.products.createIndex({ categoryId: 1, price: -1 });

-- Text Index for search
db.articles.createIndex({ title: "text", content: "text" });

2. Caching Pattern

Implement caching to reduce database load:

// Cache-Aside Pattern
class UserService {
  constructor(
    private userRepository: UserRepository,
    private cache: CacheService
  ) {}

  async getUser(id: string): Promise {
    // Try cache first
    const cachedUser = await this.cache.get(`user:${id}`);
    if (cachedUser) {
      return JSON.parse(cachedUser);
    }

    // Fetch from database
    const user = await this.userRepository.findById(id);
    if (user) {
      // Cache for 1 hour
      await this.cache.set(`user:${id}`, JSON.stringify(user), 3600);
    }

    return user;
  }

  async updateUser(id: string, data: Partial): Promise {
    const user = await this.userRepository.update(id, data);
    
    // Update cache
    await this.cache.set(`user:${id}`, JSON.stringify(user), 3600);
    
    return user;
  }
}

3. Connection Pooling

Manage database connections efficiently:

// Connection Pool Configuration
const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  connectionLimit: 20,    // Maximum connections
  queueLimit: 50,         // Maximum queued requests
  acquireTimeout: 60000,  // Timeout for getting connection
  timeout: 60000         // Query timeout
});

class DatabaseService {
  async query(sql: string, params?: any[]): Promise {
    return new Promise((resolve, reject) => {
      pool.query(sql, params, (error, results) => {
        if (error) {
          reject(error);
        } else {
          resolve(results);
        }
      });
    });
  }
}

Scalability Patterns

1. Database Sharding

Distribute data across multiple database instances:

// Sharding Strategy
class ShardingService {
  private shards: Map = new Map();

  constructor() {
    // Initialize shards
    this.shards.set('shard_0', new Database('db_shard_0'));
    this.shards.set('shard_1', new Database('db_shard_1'));
    this.shards.set('shard_2', new Database('db_shard_2'));
  }

  private getShardKey(userId: string): string {
    // Hash user ID to determine shard
    const hash = this.hashCode(userId);
    return `shard_${hash % 3}`;
  }

  async createUser(userData: User): Promise {
    const shardKey = this.getShardKey(userData.id);
    const shard = this.shards.get(shardKey);
    return await shard.insert('users', userData);
  }

  async getUser(userId: string): Promise {
    const shardKey = this.getShardKey(userId);
    const shard = this.shards.get(shardKey);
    return await shard.findById('users', userId);
  }

  private hashCode(str: string): number {
    let hash = 0;
    for (let i = 0; i < str.length; i++) {
      const char = str.charCodeAt(i);
      hash = ((hash << 5) - hash) + char;
      hash = hash & hash; // Convert to 32-bit integer
    }
    return Math.abs(hash);
  }
}

2. Read Replicas

Separate read and write operations for better performance:

// Read-Write Splitting Pattern
class DatabaseCluster {
  private master: Database;
  private replicas: Database[];

  constructor(masterConfig: Config, replicaConfigs: Config[]) {
    this.master = new Database(masterConfig);
    this.replicas = replicaConfigs.map(config => new Database(config));
  }

  async write(sql: string, params?: any[]): Promise {
    // All writes go to master
    return await this.master.query(sql, params);
  }

  async read(sql: string, params?: any[]): Promise {
    // Load balance reads across replicas
    const replica = this.getRandomReplica();
    return await replica.query(sql, params);
  }

  private getRandomReplica(): Database {
    const index = Math.floor(Math.random() * this.replicas.length);
    return this.replicas[index];
  }

  async readFromMaster(sql: string, params?: any[]): Promise {
    // For queries requiring up-to-date data
    return await this.master.query(sql, params);
  }
}

Data Consistency Patterns

1. Transaction Pattern

Ensure data integrity across multiple operations:

// ACID Transaction Pattern
class OrderService {
  async createOrder(orderData: OrderData): Promise {
    const transaction = await this.database.beginTransaction();

    try {
      // Create order
      const order = await transaction.insert('orders', {
        user_id: orderData.userId,
        total: orderData.total,
        status: 'pending'
      });

      // Update inventory
      for (const item of orderData.items) {
        const product = await transaction.findById('products', item.productId);
        
        if (product.stock < item.quantity) {
          throw new Error(`Insufficient stock for product ${item.productId}`);
        }

        await transaction.update('products', item.productId, {
          stock: product.stock - item.quantity
        });

        // Create order item
        await transaction.insert('order_items', {
          order_id: order.id,
          product_id: item.productId,
          quantity: item.quantity,
          price: item.price
        });
      }

      // Commit transaction
      await transaction.commit();
      return order;

    } catch (error) {
      // Rollback on error
      await transaction.rollback();
      throw error;
    }
  }
}

2. Event Sourcing

Store all changes as events for audit trail and replay:

// Event Sourcing Pattern
interface Event {
  id: string;
  aggregateId: string;
  type: string;
  data: any;
  timestamp: Date;
  version: number;
}

class EventStore {
  async saveEvent(event: Event): Promise {
    await this.db.insert('events', event);
  }

  async getEvents(aggregateId: string): Promise {
    return await this.db.query(
      'SELECT * FROM events WHERE aggregate_id = ? ORDER BY version ASC',
      [aggregateId]
    );
  }

  async replayAggregate(aggregateId: string): Promise {
    const events = await this.getEvents(aggregateId);
    let state = {};

    for (const event of events) {
      state = this.applyEvent(state, event);
    }

    return state;
  }

  private applyEvent(state: any, event: Event): any {
    switch (event.type) {
      case 'USER_CREATED':
        return { ...state, id: event.aggregateId, ...event.data };
      case 'USER_UPDATED':
        return { ...state, ...event.data };
      case 'USER_DELETED':
        return { ...state, deleted: true };
      default:
        return state;
    }
  }
}

Best Practices

Design Principles

• Normalization vs Denormalization: Balance between data integrity and query performance
• Index Strategy: Create indexes based on query patterns, not just columns
• Data Types: Choose appropriate data types to optimize storage and performance
• Constraint Usage: Use constraints to enforce data integrity
• Query Optimization: Analyze and optimize slow queries regularly

Security Considerations

• Parameterized Queries: Prevent SQL injection attacks
• Least Privilege: Grant minimum necessary permissions
• Data Encryption: Encrypt sensitive data at rest and in transit
• Audit Logging: Track all database modifications
• Regular Backups: Implement automated backup strategies

Conclusion

Database design patterns provide proven solutions to common data modeling challenges. By understanding and applying these patterns appropriately, you can build more efficient, scalable, and maintainable database systems.

Remember that no single pattern fits all scenarios. The key is to understand your specific requirements, data access patterns, and scalability needs, then choose the appropriate combination of patterns to address those challenges effectively.

Related Articles