Database Design Patterns: Building Scalable and Maintainable Data Architecturesh1

Hello! I’m Ahmet Zeybek, a full stack developer with extensive experience in database design and optimization. Over the years, I’ve seen how poor database design can cripple even the most well-architected applications. In this comprehensive guide, I’ll share essential database design patterns that have proven effective across various domains and scales.

The Foundation: Why Database Design Mattersh2

Database design is the cornerstone of application architecture. A well-designed database:

Ensures data integrity and consistency
Supports efficient querying and reporting
Scales horizontally and vertically
Facilitates maintenance and evolution
Enables ACID compliance when needed

Core Design Patternsh2

1. Single Table Inheritance (STI)h3

Problem: Multiple similar entities with slight variations.

Solution: Store all types in a single table with a discriminator column.

-- Users table supporting multiple user types
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    user_type VARCHAR(50) NOT NULL, -- 'customer', 'admin', 'vendor'
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- Customer specific fields
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    phone VARCHAR(20),

    -- Admin specific fields
    department VARCHAR(100),
    clearance_level INTEGER,

    -- Vendor specific fields
    company_name VARCHAR(200),
    tax_id VARCHAR(50),

    -- Ensure only relevant fields are filled based on type
    CONSTRAINT user_type_fields CHECK (
        (user_type = 'customer' AND first_name IS NOT NULL AND department IS NULL) OR
        (user_type = 'admin' AND department IS NOT NULL AND first_name IS NULL) OR
        (user_type = 'vendor' AND company_name IS NOT NULL AND first_name IS NULL)
    )
);

-- Query for customers only
SELECT * FROM users WHERE user_type = 'customer' AND first_name IS NOT NULL;

Pros:

Simple queries across all types
Single table to maintain
Efficient storage

Cons:

Sparse columns for unused fields
Type-specific constraints are complex

2. Class Table Inheritance (CTI)h3

Problem: Multiple entities with significant differences but shared attributes.

Solution: Separate tables for each type, linked to a base table.

-- Base user information
CREATE TABLE base_users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Customer specific information
CREATE TABLE customers (
    id SERIAL PRIMARY KEY REFERENCES base_users(id),
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    loyalty_points INTEGER DEFAULT 0
);

-- Admin specific information
CREATE TABLE admins (
    id SERIAL PRIMARY KEY REFERENCES base_users(id),
    department VARCHAR(100) NOT NULL,
    clearance_level INTEGER NOT NULL,
    badge_number VARCHAR(50)
);

-- Vendor specific information
CREATE TABLE vendors (
    id SERIAL PRIMARY KEY REFERENCES base_users(id),
    company_name VARCHAR(200) NOT NULL,
    tax_id VARCHAR(50) NOT NULL,
    credit_limit DECIMAL(10,2) DEFAULT 0
);

Pros:

Type-specific constraints and validations
No sparse columns
Easy to add type-specific indexes

Cons:

Complex queries across types
Multiple tables to maintain

3. Repository Patternh3

Problem: Direct database access scattered throughout application code.

Solution: Centralize data access logic in repository classes.

// Repository interface
interface IUserRepository {
  findById(id: number): Promise<User | null>
  findByEmail(email: string): Promise<User | null>
  save(user: User): Promise<User>
  delete(id: number): Promise<boolean>
}

// Concrete implementation
class PostgreSQLUserRepository implements IUserRepository {
  constructor(private db: DatabaseConnection) {}

  async findById(id: number): Promise<User | null> {
    const result = await this.db.query('SELECT * FROM base_users bu LEFT JOIN customers c ON bu.id = c.id WHERE bu.id = $1', [id])

    if (!result.rows[0]) return null

    return this.mapToUser(result.rows[0])
  }

  async save(user: User): Promise<User> {
    const client = await this.db.getClient()

    try {
      await client.query('BEGIN')

      // Save base user
      const baseResult = await client.query(
        `INSERT INTO base_users (email, password_hash) VALUES ($1, $2)
         ON CONFLICT (email) DO UPDATE SET password_hash = $2
         RETURNING id`,
        [user.email, user.passwordHash]
      )

      const userId = baseResult.rows[0].id

      // Save customer specific data
      if (user instanceof Customer) {
        await client.query(
          `INSERT INTO customers (id, first_name, last_name, phone, loyalty_points)
           VALUES ($1, $2, $3, $4, $5)
           ON CONFLICT (id) DO UPDATE SET first_name = $2, last_name = $3, phone = $4, loyalty_points = $5`,
          [userId, user.firstName, user.lastName, user.phone, user.loyaltyPoints]
        )
      }

      await client.query('COMMIT')
      return user
    } catch (error) {
      await client.query('ROLLBACK')
      throw error
    } finally {
      client.release()
    }
  }

  private mapToUser(row: any): User {
    if (row.first_name) {
      return new Customer(row.id, row.email, row.first_name, row.last_name, row.phone, row.loyalty_points)
    }
    // Handle other user types...
    return new User(row.id, row.email)
  }
}

4. Unit of Work Patternh3

Problem: Managing database transactions across multiple operations.

Solution: Coordinate multiple repository operations in a single transaction.

class UserService {
  constructor(
    private userRepo: IUserRepository,
    private orderRepo: IOrderRepository,
    private uow: IUnitOfWork
  ) {}

  async registerCustomerWithOrder(customerData: CustomerData, orderData: OrderData): Promise<void> {
    await this.uow.execute(async () => {
      // Create customer
      const customer = await this.userRepo.save(customerData)

      // Create initial order
      const order = await this.orderRepo.save({
        ...orderData,
        customerId: customer.id,
        status: 'pending',
      })

      // Send welcome email (could fail)
      await this.emailService.sendWelcomeEmail(customer.email)

      return { customer, order }
    })
  }
}

5. CQRS (Command Query Responsibility Segregation)h3

Problem: Same data model used for both reads and writes, causing performance issues.

Solution: Separate read and write models with different optimization strategies.

// Write side - Commands
class CreateOrderCommand {
  constructor(
    public customerId: number,
    public items: OrderItem[],
    public shippingAddress: Address
  ) {}
}

// Read side - Queries
class GetOrderHistoryQuery {
  constructor(
    public customerId: number,
    public page: number = 1,
    public limit: number = 20
  ) {}
}

// Command handler (write)
class CreateOrderHandler {
  constructor(
    private orderRepo: IOrderRepository,
    private productRepo: IProductRepository,
    private eventPublisher: IEventPublisher
  ) {}

  async handle(command: CreateOrderCommand): Promise<Order> {
    // Validate inventory
    for (const item of command.items) {
      const product = await this.productRepo.findById(item.productId)
      if (product.stockQuantity < item.quantity) {
        throw new Error(`Insufficient stock for ${product.name}`)
      }
    }

    // Create order
    const order = new Order(command.customerId, command.items, command.shippingAddress)
    const savedOrder = await this.orderRepo.save(order)

    // Publish domain event
    await this.eventPublisher.publish(new OrderCreatedEvent(savedOrder))

    return savedOrder
  }
}

// Query handler (read)
class GetOrderHistoryHandler {
  constructor(private readDb: ReadDatabase) {}

  async handle(query: GetOrderHistoryQuery): Promise<OrderSummary[]> {
    const offset = (query.page - 1) * query.limit

    return await this.readDb.query(
      `
      SELECT o.id, o.total_amount, o.status, o.created_at,
             COUNT(oi.id) as item_count
      FROM orders o
      LEFT JOIN order_items oi ON o.id = oi.order_id
      WHERE o.customer_id = $1
      GROUP BY o.id, o.total_amount, o.status, o.created_at
      ORDER BY o.created_at DESC
      LIMIT $2 OFFSET $3
    `,
      [query.customerId, query.limit, offset]
    )
  }
}

Advanced Patternsh2

6. Event Sourcingh3

Problem: Traditional CRUD operations lose historical data and make auditing difficult.

Solution: Store state changes as a sequence of events.

// Domain events
class OrderPlacedEvent {
  constructor(
    public orderId: string,
    public customerId: string,
    public items: OrderItem[],
    public timestamp: Date
  ) {}
}

class OrderShippedEvent {
  constructor(
    public orderId: string,
    public trackingNumber: string,
    public shippedAt: Date
  ) {}
}

class OrderCancelledEvent {
  constructor(
    public orderId: string,
    public reason: string,
    public cancelledAt: Date
  ) {}
}

// Event store
class EventStore {
  async saveEvents(events: DomainEvent[]): Promise<void> {
    for (const event of events) {
      await this.db.query('INSERT INTO events (aggregate_id, event_type, event_data, timestamp) VALUES ($1, $2, $3, $4)', [
        event.aggregateId,
        event.constructor.name,
        JSON.stringify(event),
        event.timestamp,
      ])
    }
  }

  async getEventsForAggregate(aggregateId: string): Promise<DomainEvent[]> {
    const result = await this.db.query('SELECT event_data, event_type FROM events WHERE aggregate_id = $1 ORDER BY timestamp', [
      aggregateId,
    ])

    return result.rows.map((row) => {
      // Deserialize event based on event_type
      return this.deserializeEvent(row.event_type, JSON.parse(row.event_data))
    })
  }
}

// Aggregate root
class Order extends AggregateRoot {
  private status: OrderStatus = OrderStatus.PENDING
  private items: OrderItem[] = []
  private events: DomainEvent[] = []

  placeOrder(customerId: string, items: OrderItem[]): void {
    if (this.status !== OrderStatus.PENDING) {
      throw new Error('Order already placed')
    }

    this.status = OrderStatus.PLACED
    this.items = items

    const event = new OrderPlacedEvent(this.id, customerId, items, new Date())
    this.events.push(event)
  }

  shipOrder(trackingNumber: string): void {
    if (this.status !== OrderStatus.PLACED) {
      throw new Error('Can only ship placed orders')
    }

    this.status = OrderStatus.SHIPPED

    const event = new OrderShippedEvent(this.id, trackingNumber, new Date())
    this.events.push(event)
  }

  cancelOrder(reason: string): void {
    if (![OrderStatus.PENDING, OrderStatus.PLACED].includes(this.status)) {
      throw new Error('Can only cancel pending or placed orders')
    }

    this.status = OrderStatus.CANCELLED

    const event = new OrderCancelledEvent(this.id, reason, new Date())
    this.events.push(event)
  }

  getUncommittedEvents(): DomainEvent[] {
    return [...this.events]
  }

  markEventsAsCommitted(): void {
    this.events = []
  }
}

7. Database Sharding Patternh3

Problem: Single database becomes a bottleneck for high-traffic applications.

Solution: Distribute data across multiple databases.

class DatabaseRouter {
  private shards: Map<string, Database> = new Map()
  private shardCount = 4

  constructor() {
    // Initialize shard connections
    for (let i = 0; i < this.shardCount; i++) {
      this.shards.set(`shard_${i}`, new Database(`shard_${i}_connection`))
    }
  }

  getShardForUser(userId: string): Database {
    const shardIndex = this.getShardIndex(userId)
    return this.shards.get(`shard_${shardIndex}`)!
  }

  private getShardIndex(key: string): number {
    // Consistent hashing for even distribution
    let hash = 0
    for (let i = 0; i < key.length; i++) {
      const char = key.charCodeAt(i)
      hash = (hash << 5) - hash + char
      hash = hash & hash // Convert to 32-bit integer
    }
    return Math.abs(hash) % this.shardCount
  }

  async saveUser(user: User): Promise<void> {
    const shard = this.getShardForUser(user.id)
    await shard.save('users', user)
  }

  async getUser(userId: string): Promise<User | null> {
    const shard = this.getShardForUser(userId)
    return await shard.findById('users', userId)
  }
}

Performance Optimization Patternsh2

8. Materialized View Patternh3

Problem: Complex queries with aggregations are slow on large datasets.

Solution: Pre-compute and store aggregated data.

-- Base sales table
CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    product_id INTEGER NOT NULL,
    customer_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    sale_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Materialized view for dashboard analytics
CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT
    sale_date,
    COUNT(*) as total_orders,
    SUM(quantity * unit_price) as total_revenue,
    AVG(quantity * unit_price) as average_order_value,
    COUNT(DISTINCT customer_id) as unique_customers
FROM sales
GROUP BY sale_date
ORDER BY sale_date DESC;

-- Refresh materialized view periodically
REFRESH MATERIALIZED VIEW daily_sales_summary;

-- Query dashboard data efficiently
SELECT * FROM daily_sales_summary WHERE sale_date >= CURRENT_DATE - INTERVAL '30 days';

9. Database Connection Poolingh3

Problem: Creating new database connections for each request is expensive.

Solution: Reuse database connections from a pool.

import { Pool } from 'pg'

class DatabaseConnectionManager {
  private pool: Pool

  constructor() {
    this.pool = new Pool({
      host: process.env.DB_HOST,
      port: parseInt(process.env.DB_PORT || '5432'),
      database: process.env.DB_NAME,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      max: 20, // Maximum number of connections
      min: 5, // Minimum number of connections
      idleTimeoutMillis: 30000, // Close idle connections after 30 seconds
      connectionTimeoutMillis: 2000, // Return error after 2 seconds if connection could not be established
    })

    // Handle pool events
    this.pool.on('connect', (client) => {
      console.log('New client connected to the database')
    })

    this.pool.on('error', (err, client) => {
      console.error('Unexpected error on idle client', err)
    })
  }

  async query(text: string, params?: any[]): Promise<any> {
    const client = await this.pool.connect()

    try {
      const start = Date.now()
      const result = await client.query(text, params)
      const duration = Date.now() - start

      console.log('Executed query', { text, duration, rows: result.rowCount })
      return result
    } catch (error) {
      console.error('Database query error:', error)
      throw error
    } finally {
      client.release()
    }
  }

  async getClient(): Promise<any> {
    return await this.pool.connect()
  }

  async close(): Promise<void> {
    await this.pool.end()
  }
}

10. Read/Write Splittingh3

Problem: Write operations block read operations in single database scenarios.

Solution: Separate read and write databases.

class ReadWriteDatabaseManager {
  private readDatabases: Database[] = []
  private writeDatabase: Database
  private readIndex = 0

  constructor(readConnectionStrings: string[], writeConnectionString: string) {
    // Initialize read databases
    this.readDatabases = readConnectionStrings.map((connStr) => new Database(connStr))

    // Initialize write database
    this.writeDatabase = new Database(writeConnectionString)
  }

  // Route reads to read replicas (round-robin)
  getReadDatabase(): Database {
    const db = this.readDatabases[this.readIndex % this.readDatabases.length]
    this.readIndex++
    return db
  }

  // All writes go to primary database
  getWriteDatabase(): Database {
    return this.writeDatabase
  }

  // Read operations
  async query(text: string, params?: any[]): Promise<any> {
    const db = this.getReadDatabase()
    return await db.query(text, params)
  }

  // Write operations
  async execute(text: string, params?: any[]): Promise<any> {
    return await this.writeDatabase.query(text, params)
  }
}

Best Practices for Implementationh2

1. Choose the Right Database Engineh3

Relational Databases (SQL):

PostgreSQL: Advanced features, JSON support
MySQL: Simplicity, wide adoption
SQLite: Embedded, serverless

NoSQL Databases:

MongoDB: Document-oriented, flexible schema
Redis: In-memory, high performance
Elasticsearch: Search and analytics

2. Indexing Strategyh3

-- Single column index
CREATE INDEX idx_users_email ON users(email);

-- Composite index for multiple columns
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date DESC);

-- Partial index for better performance
CREATE INDEX idx_active_users ON users(email) WHERE active = true;

-- Covering index (includes query data)
CREATE INDEX idx_user_profiles ON users(email, first_name, last_name) WHERE active = true;

3. Migration Strategyh3

// Migration files for version control
class CreateUsersTable {
  async up(db: Database): Promise<void> {
    await db.query(`
      CREATE TABLE users (
        id SERIAL PRIMARY KEY,
        email VARCHAR(255) UNIQUE NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )
    `)

    await db.query(`
      CREATE INDEX idx_users_email ON users(email)
    `)
  }

  async down(db: Database): Promise<void> {
    await db.query('DROP INDEX IF EXISTS idx_users_email')
    await db.query('DROP TABLE IF EXISTS users')
  }
}

Common Anti-Patterns to Avoidh2

1. God Object Tablesh3

❌ Don’t create tables with 50+ columns - split into related tables

2. EAV (Entity-Attribute-Value)h3

❌ Don’t store flexible attributes as key-value pairs - use JSONB or separate tables

3. Over-normalizationh3

❌ Don’t normalize to 6NF for simple applications - balance between performance and maintainability

4. No Constraintsh3

❌ Don’t skip foreign key constraints - ensure referential integrity

Conclusionh2

Database design patterns are tools, not rigid rules. The key is understanding your specific use case and applying patterns that solve your particular problems. Always measure performance impact and be willing to refactor as requirements evolve.

Remember: premature optimization is the root of all evil, but poor database design is the root of all performance problems.

What database challenges are you currently facing? Which patterns have worked well in your projects? Share your experiences in the comments!