SQL Data Modeling and Normalization for Cleaner Systems

Why normalization matters in operations

Northstar initially kept order and customer details in one wide table. Address changes and customer segment updates caused conflicting values across rows.

Splitting into customers, orders, and order_items reduced duplication and made updates safer because each business fact had one home.

Apply normal forms pragmatically

First normal form removes repeating groups, second normal form removes partial dependency on composite keys, and third normal form removes transitive dependency. These rules help detect risky table designs.

In analytics layers, selective denormalization can be useful, but operational source tables should stay clean first.

• Define natural versus surrogate keys intentionally.
• Use constraints to enforce data quality at write time.
• Document relationship cardinality for future developers.

Refactor duplicated customer attributes

Order rows repeated customer_email and customer_segment, causing inconsistent updates across historical records.

1. Create customers table with customer_id as primary key and one row per customer.
2. Move customer attributes to customers and keep customer_id on orders as foreign key.
3. Validate no orphan orders remain and update downstream joins.

Common mistakes

• Embedding repeated customer fields in every transaction row.
• Skipping foreign key constraints and relying on application code alone.
• Over-normalizing small lookup data without practical benefit.
• Changing key definitions without migration and backfill planning.

Try one

Sources

• PostgreSQL 18 Docs: Data DefinitionOfficial PostgreSQL DDL concepts, keys, constraints, and table design basics.