System Design Series Part 12: Low-Level Design — Data Modeling & Schema Design

Low-Level Design (LLD) focuses on the internal workings of individual components—class diagrams, method signatures, data structures, and algorithms. While High-Level Design (HLD) answers "what components do we need?", LLD answers "how do we implement each component?"

                        
                        Key Insight: Great system architects understand both HLD and LLD. HLD without LLD leads to architectures that can't be implemented; LLD without HLD leads to code that doesn't fit together.
                    

HLD vs LLD Comparison

Aspect	High-Level Design (HLD)	Low-Level Design (LLD)
Focus	System architecture, components	Class design, methods, algorithms
Abstraction	High (boxes and arrows)	Low (code-level detail)
Audience	Architects, product managers	Developers, code reviewers
Artifacts	Architecture diagrams, data flow	Class diagrams, sequence diagrams
Questions	"How do components interact?"	"How is this class implemented?"

Data Modeling & Schema Design

Good data modeling is essential for LLD. It determines how data is stored, accessed, and maintained. Key considerations:

Entity relationships: One-to-one, one-to-many, many-to-many
Data integrity: Constraints, foreign keys, cascades
Query patterns: Design schema around common queries
Scalability: Plan for data growth and access patterns

Entity-relationship diagram showing one-to-one, one-to-many, and many-to-many relationships with foreign key constraints — Common entity relationship types in data modeling: one-to-one, one-to-many, and many-to-many with integrity constraints

Normalization vs Denormalization

Aspect	Normalized	Denormalized
Data duplication	Minimal	Intentional redundancy
Write performance	Fast (single update)	Slower (multiple updates)
Read performance	Requires JOINs	Fast (no JOINs)
Data consistency	Easy to maintain	Must sync duplicates
Use case	OLTP, write-heavy	OLAP, read-heavy

Example: E-commerce Schema

-- Normalized Schema (3NF)
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(id),
    created_at TIMESTAMP DEFAULT NOW(),
    status VARCHAR(20) DEFAULT 'pending'
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

CREATE TABLE order_items (
    order_id INT REFERENCES orders(id),
    product_id INT REFERENCES products(id),
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,  -- Denormalized: price at time of order
    PRIMARY KEY (order_id, product_id)
);

-- Denormalized for reporting (materialized view)
CREATE MATERIALIZED VIEW order_summary AS
SELECT 
    o.id AS order_id,
    c.name AS customer_name,
    c.email AS customer_email,
    o.created_at,
    o.status,
    SUM(oi.quantity * oi.unit_price) AS total_amount,
    COUNT(oi.product_id) AS item_count
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY o.id, c.name, c.email, o.created_at, o.status;

Indexing Strategies

Index Types and When to Use Them

-- B-Tree Index (default) - Equality and range queries
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_created ON orders(created_at);

-- Composite Index - Multi-column queries
-- Order matters! (customer_id, status) supports:
--   WHERE customer_id = ?
--   WHERE customer_id = ? AND status = ?
-- But NOT: WHERE status = ?
CREATE INDEX idx_orders_cust_status ON orders(customer_id, status);

-- Partial Index - Index subset of rows
CREATE INDEX idx_pending_orders ON orders(created_at)
WHERE status = 'pending';

-- Expression Index - Index computed values
CREATE INDEX idx_orders_date ON orders(DATE(created_at));

-- Full-text Index - Text search
CREATE INDEX idx_products_search ON products 
USING gin(to_tsvector('english', name || ' ' || description));

-- Hash Index - Equality only (no range)
CREATE INDEX idx_users_email ON users USING hash(email);

                        
                        Index Guidelines: Index columns in WHERE, JOIN, ORDER BY clauses. Avoid over-indexing (slows writes). Monitor query plans with EXPLAIN ANALYZE.
                    

Next Steps

You now have a solid grounding in data modeling and schema design for system architecture! Continue to Part 13 to explore Distributed Systems Deep Dive, covering consensus algorithms, distributed coordination, and distributed file systems.

Low-Level Design (LLD) Document Generator

Document your class design, design patterns, data models, and API contracts at the component level. Download as Word, Excel, or PDF.

Draft auto-saved

All data stays in your browser. Nothing is sent to or stored on any server.

Component / Module Name *

Implementation Language

Concurrency / Thread Safety

Design Patterns Overview

Author Name

System Design Series Part 12: Low-Level Design — Data Modeling & Schema Design

Table of Contents

Low-Level Design vs High-Level Design

System Design Mastery

Introduction to System Design

Scalability Fundamentals

Load Balancing & Caching

Database Design & Sharding

Microservices Architecture

API Design & REST/GraphQL

Message Queues & Event-Driven

CAP Theorem & Consistency

Rate Limiting & Security

Monitoring & Observability

Real-World Case Studies

Data Modeling & Schema Design

Distributed Systems Deep Dive

Authentication & Security

Questions & Trade-offs

HLD vs LLD Comparison

Data Modeling & Schema Design

Normalization vs Denormalization

Example: E-commerce Schema

Indexing Strategies

Index Types and When to Use Them

Next Steps

Low-Level Design (LLD) Document Generator

Continue the Series

Part 13: Distributed Systems Deep Dive

Part 11: Real-World Case Studies

Part 14: Authentication & Security