Database Design and Integration in Low-Level Design

Overview

Database design is the bridge between object-oriented class models and persistent storage. In Low-Level Design (LLD), every class diagram eventually needs a storage strategy - your domain objects must survive process restarts, scale across instances, and maintain consistency under concurrent access.

The mapping from class design to schema design is not always one-to-one. A single class may span multiple tables (normalization), multiple classes may collapse into one table (inheritance mapping), and relationships between objects translate into foreign keys, junction tables, or embedded documents. The decisions made at this layer directly impact query performance, data integrity, and system maintainability.

The translation process involves several key decisions: How do value objects become columns? How do entity relationships become foreign keys? How do aggregate boundaries define transaction scopes? A well-designed persistence layer preserves domain semantics while exploiting the strengths of the underlying storage engine.

Key principles guiding database design in LLD:

• Separation of concerns - Domain logic should not leak into persistence logic
• Schema reflects invariants - Constraints in your domain model become database constraints
• Performance is a design concern - Indexing, denormalization, and access patterns are first-class decisions
• Evolution is inevitable - Schema must support backward-compatible migrations
• Consistency boundaries - Aggregate roots in DDD map to transaction boundaries in the database
• Query patterns drive design - Know your access patterns before finalizing schema; design for the queries you will run, not just the data you will store

---

Normalization

Normalization eliminates redundancy and ensures data integrity through progressive decomposition of tables.

First Normal Form (1NF)

Every column contains atomic (indivisible) values. No repeating groups.

// Violates 1NF
| OrderID | Items              |
|---------|--------------------|
| 1       | Pen, Notebook, Bag |

// Satisfies 1NF
| OrderID | Item     |
|---------|----------|
| 1       | Pen      |
| 1       | Notebook |
| 1       | Bag      |

Second Normal Form (2NF)

Satisfies 1NF + every non-key attribute depends on the entire composite key (no partial dependencies).

// Violates 2NF (StudentName depends only on StudentID, not full key)
| StudentID | CourseID | StudentName | Grade |

// Fix: separate Student table
| StudentID | StudentName |
| StudentID | CourseID | Grade |

Third Normal Form (3NF)

Satisfies 2NF + no transitive dependencies (non-key attributes depend only on the primary key).

// Violates 3NF (CityName depends on ZipCode, not directly on EmployeeID)
| EmployeeID | ZipCode | CityName |

// Fix: separate Location table
| EmployeeID | ZipCode |
| ZipCode | CityName |

When to Denormalize

• Read-heavy workloads where JOIN cost dominates
• Reporting/analytics tables that tolerate stale data
• Caching frequently accessed aggregates (e.g., order totals)
• Microservice boundaries where cross-service JOINs are impossible

Comparison Table

---

Entity-Relationship Mapping

OOP relationships translate to relational structures through well-defined patterns:

Composition vs Aggregation in DB terms:

• Composition: child cannot exist without parent. Use ON DELETE CASCADE and non-nullable FK.
• Aggregation: child has independent lifecycle. Use nullable FK or ON DELETE SET NULL.

// Composition: OrderLine dies with Order
CREATE TABLE order_lines (
    id BIGINT PRIMARY KEY,
    order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL
);

// Aggregation: Player survives without Team
CREATE TABLE players (
    id BIGINT PRIMARY KEY,
    team_id BIGINT REFERENCES teams(id) ON DELETE SET NULL,
    name VARCHAR(100) NOT NULL
);

---

Inheritance Mapping Strategies

Single Table Inheritance (STI)

All subclasses share one table with a discriminator column.

+------------------------------------------+
|              vehicles                      |
+------------------------------------------+
| id | type  | make  | payload | seats     |
|----|-------|-------|---------|-----------|
| 1  | CAR   | Honda | NULL    | 5         |
| 2  | TRUCK | Ford  | 10000   | 2         |
+------------------------------------------+
     ^discriminator column

Table Per Class (Joined)

Each class gets its own table; subclass tables reference the parent via FK.

+----------------+       +----------------+       +----------------+
|   vehicles     |       |     cars       |       |    trucks      |
+----------------+       +----------------+       +----------------+
| id (PK)       |<------| id (FK→vehicles)|<-----| id (FK→vehicles)|
| make          |       | seats          |       | payload        |
| model         |       +----------------+       +----------------+
+----------------+

Table Per Concrete Class

Each concrete subclass gets a complete table with all inherited fields duplicated.

+--------------------+       +--------------------+
|       cars         |       |      trucks        |
+--------------------+       +--------------------+
| id    | make | seats|      | id  | make | payload|
+--------------------+       +--------------------+

Comparison Table

---

Indexing

Index Types

• B-tree - Default for most RDBMS. Supports range queries, sorting, equality. O(log n) lookups.
• Hash - Equality-only lookups. O(1) average. No range support. Used in memory stores.
• Composite - Multi-column index. Follows leftmost-prefix rule. INDEX(a, b, c) supports queries on (a), (a, b), (a, b, c) but not (b, c) alone.
• Covering Index - Contains all columns needed by a query, eliminating table lookups entirely.

When to Index

• Columns in WHERE, JOIN, ORDER BY, GROUP BY clauses
• Foreign key columns (critical for JOIN performance)
• High-cardinality columns (many distinct values)

Over-Indexing Costs

• Each index adds write overhead (INSERT/UPDATE/DELETE must maintain index)
• Storage cost grows linearly with index count
• Query planner may choose suboptimal index with too many options
• Rule of thumb: audit unused indexes quarterly

Query Optimization Pseudocode

function optimizeQuery(query):
    plan = database.explain(query)
    
    if plan.hasFullTableScan():
        columns = plan.getFilterColumns()
        if columns.cardinality > threshold:
            suggest("CREATE INDEX idx_name ON table(columns)")
    
    if plan.hasSortWithoutIndex():
        suggest("Add index on ORDER BY columns")
    
    if plan.accessesMoreColumnsThanNeeded():
        suggest("Create covering index including SELECT columns")
    
    if plan.joinUsesNestedLoop() AND joinColumn.hasNoIndex():
        suggest("Index the FK column used in JOIN")
    
    return plan.estimatedCost()

---

Repository Pattern

The Repository pattern abstracts data access behind a collection-like interface, decoupling domain logic from persistence mechanics.

Interface Definition

interface UserRepository:
    findById(id: UUID): User?
    findByEmail(email: String): User?
    findAll(criteria: SearchCriteria): List<User>
    save(user: User): User
    delete(user: User): void
    existsByEmail(email: String): boolean

Implementation

class PostgresUserRepository implements UserRepository:
    private connectionPool: ConnectionPool
    private mapper: UserRowMapper

    function findById(id: UUID): User?
        connection = connectionPool.acquire()
        try:
            result = connection.query(
                "SELECT * FROM users WHERE id = $1", [id]
            )
            return mapper.toDomain(result.firstOrNull())
        finally:
            connectionPool.release(connection)

    function save(user: User): User
        connection = connectionPool.acquire()
        try:
            row = mapper.toRow(user)
            if user.isNew():
                connection.query(
                    "INSERT INTO users (id, name, email) VALUES ($1, $2, $3)",
                    [row.id, row.name, row.email]
                )
            else:
                connection.query(
                    "UPDATE users SET name=$2, email=$3 WHERE id=$1",
                    [row.id, row.name, row.email]
                )
            return user
        finally:
            connectionPool.release(connection)

Benefits: testability (mock the interface), swappable backends, domain purity.

---

Data Access Patterns

When to use each:

• Active Record - Simple CRUD apps, prototypes, Rails/Django-style frameworks
• Data Mapper - Complex domain logic, DDD, when domain model diverges from schema
• Repository - Enterprise applications, when you need query encapsulation and unit-of-work patterns

---

Connection Pooling

Why Pool Connections?

Creating a database connection involves TCP handshake, authentication, SSL negotiation, and session setup - typically 20-100ms. Under load, creating connections per request exhausts database limits and adds latency.

How It Works

A pool maintains a set of pre-established connections. Threads borrow connections, use them, and return them. The pool handles lifecycle, health checks, and eviction.

Pool Sizing

Formula (from PostgreSQL wiki): pool_size = (core_count * 2) + effective_spindle_count

For most applications: start with 10-20 connections, load test, and adjust. Too large a pool causes contention at the database level (lock waits, context switching). Too small a pool causes request queuing at the application level.

Key configuration parameters:

• minimumIdle - Connections kept ready even under no load
• maximumPoolSize - Hard ceiling; exceeding this queues requests
• connectionTimeout - How long to wait for a connection before throwing
• idleTimeout - How long an unused connection stays in the pool
• maxLifetime - Maximum age of a connection (prevents stale connections after DB failover)

Monitoring metrics to track: pool utilization percentage, average wait time, connection creation rate, timeout count, and active vs idle ratio.

Pseudocode

class ConnectionPool:
    private available: Queue<Connection>
    private inUse: Set<Connection>
    private maxSize: int
    private mutex: Lock
    private condition: ConditionVariable

    function acquire(timeout: Duration): Connection
        mutex.lock()
        try:
            while available.isEmpty():
                if inUse.size() < maxSize:
                    conn = createNewConnection()
                    inUse.add(conn)
                    return conn
                if not condition.await(timeout):
                    throw TimeoutException("Pool exhausted")
            
            conn = available.dequeue()
            if not conn.isValid():
                conn.close()
                conn = createNewConnection()
            inUse.add(conn)
            return conn
        finally:
            mutex.unlock()

    function release(conn: Connection):
        mutex.lock()
        try:
            inUse.remove(conn)
            if conn.isValid():
                available.enqueue(conn)
            condition.signal()
        finally:
            mutex.unlock()

---

Transaction Management

ACID Properties

Isolation Levels

Most production systems use Read Committed (PostgreSQL default) or Repeatable Read (MySQL/InnoDB default).

Optimistic vs Pessimistic Locking

Pessimistic Locking - Acquire lock before reading. Blocks other transactions.

// SELECT ... FOR UPDATE
function transferFunds(fromId, toId, amount):
    BEGIN TRANSACTION
    fromAccount = SELECT * FROM accounts WHERE id = fromId FOR UPDATE
    toAccount = SELECT * FROM accounts WHERE id = toId FOR UPDATE
    
    fromAccount.balance -= amount
    toAccount.balance += amount
    UPDATE accounts ...
    COMMIT

Optimistic Locking - Read without locking, check version on write. Retry on conflict.

function updateProfile(userId, newData):
    user = SELECT * FROM users WHERE id = userId  // includes version column
    user.applyChanges(newData)
    
    result = UPDATE users SET ..., version = version + 1
             WHERE id = userId AND version = user.version
    
    if result.rowsAffected == 0:
        throw OptimisticLockException("Concurrent modification")

Choose optimistic when conflicts are rare (read-heavy). Choose pessimistic when conflicts are frequent or consequences are severe (financial transactions).

---

ORM Considerations

The N+1 Problem

// N+1: 1 query for orders + N queries for customers
orders = SELECT * FROM orders          // 1 query
for order in orders:
    order.customer = SELECT * FROM customers WHERE id = order.customer_id  // N queries

// Fix: eager loading with JOIN
orders = SELECT o.*, c.* FROM orders o
         JOIN customers c ON o.customer_id = c.id  // 1 query

Lazy vs Eager Loading

When to Use Raw Queries

• Complex aggregations or window functions the ORM cannot express
• Performance-critical paths where ORM overhead matters (object hydration cost)
• Bulk operations (batch inserts/updates of thousands of rows)
• Database-specific features (CTEs, lateral joins, full-text search, JSON operators)
• When the generated SQL is demonstrably suboptimal after EXPLAIN analysis
• Migrations and data backfill scripts where ORM lifecycle hooks add unwanted side effects

ORM Best Practices

• Always log generated SQL in development to catch N+1 and inefficient queries early
• Set fetch size limits - Never load unbounded result sets into memory
• Use projections/DTOs for read-only queries instead of full entity hydration
• Batch writes - Configure batch size for bulk inserts (e.g., hibernate.jdbc.batch_size=25)
• Avoid bidirectional relationships unless navigation from both sides is genuinely needed
• Second-level cache - Use for reference data (countries, currencies) but not for frequently-mutated entities

---

Real-World Examples

JPA/Hibernate Patterns

@Entity
@Table(name = "orders")
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name = "order_type")
public class Order {
    @Id @GeneratedValue
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "customer_id")
    private Customer customer;

    @OneToMany(mappedBy = "order", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<OrderLine> lines;

    @Version
    private Long version;  // optimistic locking
}

Connection Pool Libraries

Migration Tools

---

Constraints & Edge Cases

Schema Evolution

• Always use versioned migrations (never modify production schema manually)
• Additive changes (new columns, new tables) are safe
• Destructive changes (drop column, rename) require multi-phase deployment:
  1. Deploy code that handles both old and new schema
  2. Apply migration
  3. Deploy code that uses only new schema
  4. Clean up old column in a later migration

Backward Compatibility

• New nullable columns with defaults are backward-compatible
• Renaming columns breaks existing queries - use views as aliases during transition
• Changing column types requires careful casting and validation

Soft Deletes

// Add deleted_at column instead of removing rows
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP NULL;

// All queries must filter
SELECT * FROM users WHERE deleted_at IS NULL;

// Consider a partial index for performance
CREATE INDEX idx_active_users ON users(email) WHERE deleted_at IS NULL;

Trade-offs: simpler recovery, audit compliance, but query complexity increases and unique constraints need special handling (WHERE deleted_at IS NULL).

Audit Trails

• Event sourcing - Store every state change as an event; reconstruct current state by replaying
• Audit table - Trigger-based or application-level logging of changes to a separate table
• Temporal tables - SQL:2011 standard (supported in MariaDB, SQL Server) with system-versioned rows
• Change Data Capture (CDC) - Stream database changes to external systems (Debezium, AWS DMS)
• Include: who changed, what changed, when, previous value, new value, correlation/request ID

CREATE TABLE audit_log (
    id BIGSERIAL PRIMARY KEY,
    table_name VARCHAR(100) NOT NULL,
    record_id BIGINT NOT NULL,
    action VARCHAR(10) NOT NULL,  -- INSERT, UPDATE, DELETE
    changed_by VARCHAR(100) NOT NULL,
    changed_at TIMESTAMP DEFAULT NOW(),
    old_values JSONB,
    new_values JSONB,
    request_id UUID  -- for tracing
);

---

Interview Follow-ups

Q1: How would you handle a schema change that renames a heavily-used column in production?

A: Use a multi-phase approach: (1) Add new column, (2) backfill data, (3) deploy code reading from both columns, (4) switch writes to new column, (5) drop old column after verification period. This avoids downtime and maintains backward compatibility.

Q2: When would you choose optimistic over pessimistic locking?

A: Optimistic locking suits read-heavy workloads with rare conflicts - e-commerce product browsing, CMS content editing. Pessimistic locking is better for high-contention scenarios like seat booking, inventory decrement, or financial transfers where retry cost exceeds lock-wait cost.

Q3: How do you decide between Single Table Inheritance and Table Per Class?

A: Single Table when subclasses share most attributes and you query polymorphically often (fewer JOINs). Table Per Class when subclasses have many distinct attributes (avoids wide sparse tables) and you need strict NOT NULL constraints on subclass-specific fields.

Hints-Only Questions

H1: Your application experiences connection timeouts under load. The database shows only 50 active connections but your pool is sized at 100. What's happening?

Hints: Think about connection validation overhead. Consider whether connections are being held too long (transaction scope). Check if the pool's minimum idle setting is causing connection churn.

H2: A query that was fast with 10K rows becomes slow at 1M rows despite having an index. What could cause this?

Hints: Consider index selectivity - if the indexed column has low cardinality, the planner may prefer a sequential scan. Check if statistics are stale (ANALYZE). Consider whether the query returns too many rows for an index scan to be beneficial.

---

Counter Questions to Ask Interviewer

1. "What's the expected read-to-write ratio?" - Drives normalization vs denormalization decisions.
2. "Are there strict consistency requirements, or is eventual consistency acceptable?" - Determines isolation level and replication strategy.
3. "What's the expected data growth rate?" - Influences partitioning, archival, and indexing strategy.
4. "Will the schema need to support multi-tenancy?" - Impacts table design (shared schema vs schema-per-tenant vs database-per-tenant).
5. "Are there compliance requirements (GDPR, HIPAA) affecting data retention?" - Drives soft delete, encryption, and audit trail decisions.

---

References & Whitepapers

• Patterns of Enterprise Application Architecture - Martin Fowler. Defines Active Record, Data Mapper, Repository, Unit of Work, and Identity Map patterns.
• Database Internals - Alex Petrov. Deep coverage of B-tree variants, LSM trees, storage engines, and distributed database protocols.
• Designing Data-Intensive Applications - Martin Kleppmann. Covers replication, partitioning, transactions, and consistency models.
• SQL Antipatterns - Bill Karwin. Common schema design mistakes and their solutions.
• Use The Index, Luke (use-the-index-luke.com) - Comprehensive guide to SQL indexing and query optimization.
• PostgreSQL documentation on MVCC and isolation levels.
• HikariCP wiki on connection pool sizing and benchmarks.

---

Related Topics

• 01-clean-code-and-solid-principles.md
• 03-caching-strategies.md
• 04-api-design-and-contracts.md
• 05-error-handling-and-resilience.md
• 06-testing-strategies.md