advanced~4h

Database Design — Normalization, Denormalization & Multi-Tenant

Database design determines the structure of your data, which affects data integrity, query performance, and maintainability. Good design starts with normalization and strategically denormalizes for pe

Every table you'll ever design is a tradeoff between normalization (less duplication, more joins) and denormalization (more duplication, fewer joins) — and for any real SaaS product, on top of that, you have to decide how multiple customers' data shares (or doesn't share) the same schema. These decisions are made once, early, and are expensive to reverse later.

A poorly normalized schema duplicates data everywhere, making updates error-prone (change a customer's address in one row, forget the other ten); an over-normalized schema requires so many joins that even simple queries become slow. Multi-tenant systems add a third dimension: how do you keep one tenant's data invisible to another, without paying for a fully separate database per tenant?

Normalization — organizing data to minimize duplication, typically by following normal forms (1NF, 2NF, 3NF). Denormalization — deliberately duplicating data to avoid expensive joins, trading storage and update complexity for read speed. Multi-tenant patterns: shared schema with a tenant_id column (cheapest, least isolated), schema-per-tenant (moderate isolation), database-per-tenant (most isolated, most operational overhead).

Normal Forms

1NF (First Normal Form) — atomic values; no repeating groups; each column has one value.
2NF (Second Normal Form) — 1NF + every non-key column depends on the WHOLE primary key (relevant for composite keys).
3NF (Third Normal Form) — 2NF + no transitive dependencies (non-key column depends only on the key, not on other non-key columns).
BCNF (Boyce-Codd Normal Form) — stricter 3NF.
4NF — no multi-valued dependencies.
5NF — no join dependencies.

Most production databases aim for 3NF. Denormalization is used strategically for read performance.

Multi-tenant database patterns

Separate Database per tenant — maximum isolation; expensive; hard to manage at scale.
Shared Database, Separate Schema — good isolation; schema per tenant; moderate complexity.
Shared Database, Shared Schema — single schema, tenant_id column; most scalable; least isolation; most common for SaaS.

Normalization process

1NF: Ensure atomic values — split 'Engineering, Marketing' into rows.

2NF: Eliminate partial dependencies — if PK is (order_id, product_id), product_name should be in products table, not orders (depends only on product_id, not the composite key).

3NF: Eliminate transitive dependencies — if employee has dept_id and dept_name, dept_name depends on dept_id (not emp_id). Move to departments table.

Multi-tenant Row-Level Security (PostgreSQL)

Every table has tenant_id column.
Create PostgreSQL POLICY: CREATE POLICY tenant_isolation ON orders USING (tenant_id = current_setting('app.current_tenant')::UUID).
Application sets: SET LOCAL app.current_tenant = 'tenant-uuid' at session start.
All queries automatically filtered by tenant — even if developer forgets WHERE tenant_id.

Step 1: Start with a conceptual model (entities and relationships).

Step 2: Apply 1NF — one value per cell, no arrays in columns.

Step 3: Apply 2NF — move partial dependencies to their own tables.

Step 4: Apply 3NF — move transitive dependencies to their own tables.

Step 5: Add foreign keys for referential integrity.

Step 6: Profile queries — identify read-heavy patterns that need optimization.

Step 7: Strategically denormalize: add redundant columns, materialized views, or summary tables.

Step 8: For multi-tenant: choose the right isolation model and implement RLS if needed.

Design for 3NF first — normalize completely, then denormalize based on measured performance needs.
Never store derived/calculated values in normalized schema — compute on query. Exception: denormalization.
Store price snapshots in order_items — product price changes over time; snapshot the price at order time.
Use UUIDs for multi-tenant primary keys — prevents ID guessing across tenants.
Row-Level Security (PostgreSQL RLS) — enforce tenant isolation at database level.
Index tenant_id as leftmost column in composite indexes for multi-tenant schemas.
Use separate schemas for medium isolation without separate database overhead.
Materialized views for complex cross-table aggregates — refresh on schedule.
Always add created_at, updated_at TIMESTAMP columns to every table.
Design for soft-delete: is_deleted boolean + deleted_at timestamp (don't actually delete).

Storing arrays/lists in a column (1NF violation) — use separate table or JSONB if truly variable.
Duplicate data without sync mechanism — denormalized columns that get out of sync.
Missing created_at/updated_at — adds retroactively is painful; always include from start.
No soft-delete strategy — hard deletes lose data; audit trails require soft delete.
Multi-tenant data leakage — forgetting WHERE tenant_id in queries. Fix: RLS.
Integer primary keys across tenants — IDs from different tenants could collide or leak.
Too many tables — extreme normalization creates queries requiring 15 JOINs.
Storing business logic in column names — having 'q1_sales', 'q2_sales' columns instead of a date dimension.

Denormalize hot-path columns: add customer_name to orders table to avoid JOIN for list views.
Materialized views for complex reports: CREATE MATERIALIZED VIEW monthly_revenue AS ... ; REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue.
Summary tables: pre-aggregate order totals by day/week/month; updated by triggers or batch jobs.
Partition multi-tenant tables: PARTITION BY HASH (tenant_id) or LIST (tenant_id for small tenants).
Read replica: route reporting queries to read replica; write to primary.
JSONB for variable attributes: products with different specs stored in a JSONB column (avoids EAV anti-pattern).

In a shared-schema multi-tenant design, EVERY query touching tenant data must filter by tenant_id — a single missed filter in one code path is a direct cross-tenant data leak. Enforce this structurally (row-level security policies, a query-building layer that can't omit the filter) rather than trusting every developer to remember it every time.

In shared-schema multi-tenant systems, monitor for queries missing a tenant filter (via query logging/static analysis) as a standing security check, not a one-time review — this is exactly the kind of mistake that's invisible until it causes an actual data leak.

Schema migrations: use Flyway or Liquibase for all schema changes.
Audit tables: separate audit_log table for all DML; track user_id, changed_at, old_value, new_value.
Soft deletes with partial index: CREATE INDEX ON orders(id) WHERE NOT is_deleted — exclude deleted rows from index.
RLS testing: include tenant isolation tests in your test suite; test with two different tenant IDs.
Cross-tenant reporting: use superuser role that bypasses RLS; never give to application user.

Take the orders_unnormalized table and convert it to 3NF. Identify each dependency, create the necessary tables, and insert the data.
Implement multi-tenant orders using the shared-schema pattern. Create RLS policies. Test that tenant A cannot see tenant B's orders using different session settings.
Implement strategic denormalization: add a total_amount cached column to the orders table. Create a trigger that updates it whenever order_items are modified.
Design a schema for a blog platform (posts, comments, tags, users) in 3NF. Then add appropriate indexes for the most common query patterns.

1NF: atomic values. 2NF: no partial key dependencies. 3NF: no transitive dependencies.
Normalize first, then strategically denormalize measured bottlenecks.
Snapshot prices in order_items — product prices change; historical orders must be accurate.
Multi-tenant patterns: Separate DB (strong isolation, expensive) → Separate Schema → Shared Schema + RLS (scalable, SaaS-friendly).
Row-Level Security (PostgreSQL RLS): automatic tenant data isolation at database level.
Materialized views: pre-compute expensive aggregates; refresh on schedule.
Add created_at, updated_at, soft delete (is_deleted) to every table from day one.

Want a visual for this concept?

Generate a diagram tailored to “Database Design — Normalization, Denormalization & Multi-Tenant” — the AI picks whichever visual (flowchart, comparison, sequence, etc.) best fits.

Practice quiz

Next Step

Continue to PostgreSQL Specifics — MVCC, JSONB & VACUUM →← Back to all SQL chapters