Database Design: Denormalization, Indexing, Sharding, and Views

The second half of Chapter 5 in Data Engineering for Beginners by Chisom Nwokwu (ISBN 9781394325412) answers the question every data engineer eventually faces: your schema is clean, but your queries are slow. Now what?

Previous: Database Design Modeling | Next: Data Warehouses and Lakes

Denormalization: The Opposite Move

Normalization splits data into related tables to avoid duplication. Denormalization combines tables back together to speed up reads. You duplicate data on purpose.

The book shows a denormalized orders table with CustomerName, ProductName, and precomputed TotalPrice all in one row. No joins needed. Query logic gets simpler. Reports run faster.

Where does this make sense?

• Data warehouses and OLAP systems where read performance beats write efficiency
• Real-time apps like trading platforms or IoT where joins add latency
• Distributed systems where you want all related data on one node

The key insight: denormalization is a deliberate trade-off. You sacrifice some maintainability for speed.

Data Modeling Best Practices

Before jumping into optimization techniques, Nwokwu shares four modeling habits worth keeping:

Define the grain. What does one row represent? One transaction? One day of sales per store? You cannot mix grains in the same table. Get this wrong and every downstream report breaks.

Normalize now, denormalize later. Start clean. Split tables properly. Then, when real performance bottlenecks show up, denormalize in targeted spots. Do not skip straight to messy tables.

Choose the right data types. BIGINT instead of INT for IDs that will grow. DECIMAL instead of FLOAT for money. VARCHAR(255) instead of TEXT when lengths are predictable.

Use proper naming. Customers with first_name and account_id beats tbl001 with c1 and c2 every time. Consistency matters.

Database Optimization

The Sepora e-commerce story sets the stage. A holiday sale crashes the site because every product search scans the entire table. Sound familiar? The book walks through four optimization techniques.

Indexing

An index is a data structure that helps the database find rows fast without scanning everything. Create an index on product_name and searches go from slow full table scans to quick lookups.

CREATE INDEX idx_product_name ON products(name);

Indexes speed up reads but add overhead on writes. Use them on columns you filter or join frequently.

Partitioning

Partitioning splits one large table into smaller pieces within the same database server. Partition a sales table by year, and a query for 2021 only scans the 2021 partition. The library analogy (organizing books by section instead of one giant shelf) makes this easy to picture.

Sharding

Sharding goes further. Data gets split across multiple physical database servers. Each shard holds a portion of the data based on a sharding key like user_id or region.

The book shows hash-based sharding with simple Python logic: even user IDs go to shard1, odd IDs go to shard2. In production, a shard manager handles this routing automatically.

Partitioning vs sharding: partitioning stays on one server. Sharding spreads across many. Both break data into smaller chunks, but sharding is for when one server cannot handle the load anymore.

Views

Views are virtual tables built from SQL queries. Define a complex join once, query the view everywhere. They simplify code and add a security layer by hiding sensitive columns.

Materialized views go further by physically storing query results. Great for dashboards and reports that do not need real-time data. The trade-off: you need to refresh them when underlying data changes.

REFRESH MATERIALIZED VIEW materialized_view_name;

My Take

This half of the chapter is where theory meets production reality. Normalization keeps your data honest. Denormalization makes it fast. Indexing, partitioning, and sharding scale it up. Views make it manageable.

The “normalize now, denormalize later” advice is probably the most practical takeaway. I have seen teams denormalize too early and end up with unmaintainable schemas. Start clean, measure performance, then optimize where it actually hurts.

The Sepora story is relatable. Every growing platform hits that wall. Knowing which tool to reach for (index vs partition vs shard) is a real data engineering skill.

Chapter 5 as a whole gives you a complete picture of database design from modeling through optimization. Chapter 6 takes it bigger: data warehouses, lakes, and lakehouses.