Data Modeling and Normalization: SQL Analysis Chapter 9

Why this chapter matters

Solid schema design prevents update anomalies and makes analytics easier to trust over time.

What you will learn

Define primary keys, foreign keys, and essential constraints.
Apply normalization principles to reduce redundant storage.
Balance normalized design with query readability and performance.

Understand the core ideas

Data modeling determines how easy it is to ask and answer questions later. In PostgreSQL, normalized schemas separate entities into focused tables connected by keys, which reduces duplication and update anomalies. A customer address copied into every order row may look convenient at first, but corrections become error prone when one customer has hundreds of orders. Instead, keep customer attributes in customers, transactional facts in orders, and line details in order_items, then enforce relationships with foreign keys. This design clarifies row grain for each table and helps analysts join data intentionally. Normalization does not mean every query is short, but it does mean facts have one authoritative home. Constraints such as NOT NULL, UNIQUE, and CHECK turn business rules into database guarantees, reducing silent data quality drift.

Good modeling balances integrity with practical query needs. Third normal form is a useful target, but decisions should consider read patterns, write patterns, and maintenance risk. In PostgreSQL analytics workflows, a normalized source of truth can feed denormalized reporting tables when needed, as long as the transformation is explicit and tested. Primary keys define table identity, foreign keys define valid relationships, and unique constraints protect natural keys like external ids. When a team can state grain for every table in one sentence, downstream SQL becomes more predictable. Model reviews should include sample joins and metric queries to ensure schema choices support real analysis tasks. The goal is not theoretical purity, it is durable correctness and understandable query paths as data volume and team size grow.

Key terms

primary key: A column or column set that uniquely identifies each row in a table.
foreign key: A constraint that requires values to match existing keys in a related table.
normalization: Structuring data to reduce redundancy and dependency anomalies across tables.
referential integrity: Guarantee that relationships between tables remain valid over inserts, updates, and deletes.

Normalize denormalized order data

Starting from a flat order export, design PostgreSQL tables customers, orders, and order_items. Preserve one row per customer in customers, one row per order in orders, and one row per line item in order_items.

Define table grains and keys: customers(customer_id PK), orders(order_id PK, customer_id FK), and order_items(order_id FK, line_number, PK(order_id, line_number)).
Move customer attributes out of orders into customers so updates occur in one place and no order-level duplicates persist.
Store order-level facts such as ordered_at and status only in orders, and line-level facts such as sku, quantity, and unit_price only in order_items.
Add constraints (NOT NULL, CHECK (quantity > 0), and foreign keys) to enforce valid records before analytics queries run.

Result:

CREATE TABLE customers (customer_id bigint PRIMARY KEY, email text UNIQUE NOT NULL); CREATE TABLE orders (order_id bigint PRIMARY KEY, customer_id bigint NOT NULL REFERENCES customers(customer_id), ordered_at timestamptz NOT NULL, status text NOT NULL); CREATE TABLE order_items (order_id bigint NOT NULL REFERENCES orders(order_id), line_number integer NOT NULL, sku text NOT NULL, quantity integer NOT NULL CHECK (quantity > 0), unit_price numeric(12,2) NOT NULL CHECK (unit_price >= 0), PRIMARY KEY (order_id, line_number));

establishes clean relational grain for reliable joins and metrics.

A common misconception

Claim: Normalization always makes analytics impossible without complex SQL.

Correction: Normalization may add joins, but it greatly improves correctness and maintainability. Reporting views or marts can simplify querying without abandoning a reliable normalized core.

Lessons in this chapter

Relational modeling basicsModel entities and relationships with clear table boundaries. Read the full guide →
Normal forms in practiceUse practical first, second, and third normal form checks.
Constraints as guardrailsEnforce invariants with NOT NULL, UNIQUE, CHECK, and foreign keys in PostgreSQL.
Schema choices and analyticsEvaluate how modeling decisions affect downstream query complexity.

Study task

Redesign a denormalized PostgreSQL orders sheet into customer, order, and order_item tables with keys and constraints.

Chapter checkpoint

Why is a foreign key useful in PostgreSQL table design?

A foreign key enforces referential integrity so related records cannot drift into invalid states.

Data Modeling and Normalization