Back to Blog
Data Modeling14 min readFebruary 25, 2026

Database Schema Design: A Practical Guide

Normalization, denormalization, indexing strategies, and schema patterns for OLTP and OLAP databases.

Schema design is a product decision

Your database schema isn't just a technical artifact — it shapes query performance, application complexity, and how easily your system evolves. A schema optimized for writes (OLTP) looks very different from one optimized for reads (OLAP). Understanding these trade-offs upfront saves months of painful migrations later.

Normalization fundamentals

Normal forms reduce data redundancy and prevent update anomalies:

  • 1NF: Atomic values only — no arrays or nested objects in cells.
  • 2NF: Every non-key column depends on the entire primary key (eliminates partial dependencies).
  • 3NF: No transitive dependencies — non-key columns depend only on the primary key, not on other non-key columns.

For OLTP systems, 3NF is the sweet spot. Over-normalizing (BCNF, 4NF) adds JOIN complexity for diminishing returns.

When to denormalize

Analytics workloads benefit from denormalization — pre-computed JOINs, aggregation columns, and wide tables that reduce query complexity. Star schema and snowflake schema are the two dominant patterns for data warehouses.

Denormalize when:

  • Read performance matters more than write consistency
  • Queries always join the same tables
  • Your warehouse engine (Snowflake, BigQuery) handles wide tables efficiently
  • ETL/ELT can handle the transformation overhead

Build your architecture diagram now

Datadef generates professional diagrams with AI — 2,000+ cloud icons, column-level data lineage, and an MCP server your coding agent can drive.

Try free — no sign-upAI Diagram Generator

Indexing strategies

B-tree indexes

Default index type in PostgreSQL, MySQL, and SQL Server. Optimal for equality and range queries. Create indexes on foreign keys, frequently-filtered columns, and ORDER BY targets.

Composite indexes

Multi-column indexes follow the leftmost-prefix rule. Index (country, city, zipcode) supports queries filtering by country alone, country+city, or all three — but not city alone.

Partial and expression indexes

PostgreSQL's partial indexes (WHERE active = true) and expression indexes (LOWER(email)) dramatically reduce index size and improve performance for filtered or transformed queries.

Schema patterns for common scenarios

  • Multi-tenancy: Shared tables with tenant_id (simple, scalable) vs schema-per-tenant (isolation) vs database-per-tenant (maximum isolation, ops overhead).
  • Soft deletes: Add deleted_at timestamp instead of hard DELETE. Filter with WHERE deleted_at IS NULL. Partial indexes keep queries fast.
  • Audit trails: Separate audit table with trigger-based change logging, or use event sourcing patterns.
  • Polymorphic associations: Avoid record_type + record_id anti-pattern. Use separate join tables or table inheritance instead.

Visualizing your schema

Entity-relationship diagrams (ERDs) should be living documents, not one-time design artifacts. Datadef lets you diagram your database schema with AI, connect tables with join relationships, and track column-level lineage downstream into your analytics layer.