Database Design & Normalization

Build efficient, maintainable schemas

Why Database Design Matters

Bad database design creates headaches: duplicate data, update anomalies, slow queries, and data inconsistencies. Good design prevents these problems from the start. Normalization is the process of organizing data to reduce redundancy and improve integrity. Learn these principles now, and you'll build databases that scale gracefully and remain maintainable for years.

The Problem: A Poorly Designed Table

Let's start with a bad design to understand what we're trying to fix.

Everything in one table:

orders (BAD DESIGN):
┌────┬─────────────┬──────────────────┬──────────────┬────────┬────────┐
│ id │ customer    │ customer_email   │ product_name │ price  │ qty    │
├────┼─────────────┼──────────────────┼──────────────┼────────┼────────┤
│ 1  │ Alice Smith │ alice@email.com  │ Laptop       │ 999.99 │ 1      │
│ 2  │ Alice Smith │ alice@email.com  │ Mouse        │ 29.99  │ 2      │
│ 3  │ Bob Jones   │ bob@email.com    │ Laptop       │ 999.99 │ 1      │
│ 4  │ Alice Smith │ alice@email.com  │ Keyboard     │ 79.99  │ 1      │
└────┴─────────────┴──────────────────┴──────────────┴────────┴────────┘

Problems with This Design

Data Redundancy: Alice's name and email repeated 3 times
Update Anomaly: If Alice changes email, must update 3 rows
Insert Anomaly: Can't add a customer without an order
Delete Anomaly: Delete Bob's order = lose Bob's info
Wasted Space: Same data stored multiple times

What is Normalization?

Normalization is splitting data into multiple related tables to eliminate redundancy. There are different "normal forms" (1NF, 2NF, 3NF, BCNF), each solving specific problems.

The Goal

Each piece of information should be stored once and only once, in the most logical place. Related data is then connected through foreign keys.

First Normal Form (1NF): Atomic Values

Rule: Each cell must contain a single, indivisible value. No lists, no multiple values in one field. Problem solved: Repeating groups, non-unique data.

Violates 1NF

Multiple phone numbers in one cell.

customers (BAD):
┌────┬────────┬──────────────────────────┐
│ id │ name   │ phone_numbers            │
├────┼────────┼──────────────────────────┤
│ 1  │ Alice  │ 555-1234, 555-5678       │ ← Multiple values!
│ 2  │ Bob    │ 555-9999                 │
└────┴────────┴──────────────────────────┘

Fixed: 1NF Compliant

Separate row for each phone number.

customers:
┌────┬────────┐
│ id │ name   │
├────┼────────┤
│ 1  │ Alice  │
│ 2  │ Bob    │
└────┴────────┘

customer_phones:
┌────┬─────────────┬──────────────┐
│ id │ customer_id │ phone_number │
├────┼─────────────┼──────────────┤
│ 1  │ 1           │ 555-1234     │
│ 2  │ 1           │ 555-5678     │
│ 3  │ 2           │ 555-9999     │
└────┴─────────────┴──────────────┘

Now each cell contains exactly one value.

Second Normal Form (2NF): No Partial Dependencies

Rule: Must be in 1NF, and all non-key columns must depend on the entire primary key (not just part of it). Applies to composite keys. Problem solved: Data redundancy in composite keys.

Violates 2NF

Product price depends only on product_id, not the full key.

order_items (BAD):
Primary Key: (order_id, product_id)
┌──────────┬────────────┬──────────────┬────────┬─────┐
│ order_id │ product_id │ product_name │ price  │ qty │
├──────────┼────────────┼──────────────┼────────┼─────┤
│ 1        │ 10         │ Laptop       │ 999.99 │ 1   │
│ 1        │ 20         │ Mouse        │ 29.99  │ 2   │
│ 2        │ 10         │ Laptop       │ 999.99 │ 1   │
└──────────┴────────────┴──────────────┴────────┴─────┘

Problem: product_name and price only depend on product_id,
not on the full composite key (order_id + product_id)

Fixed: 2NF Compliant

Separate product details into their own table.

products:
┌────────────┬──────────────┬────────┐
│ product_id │ product_name │ price  │
├────────────┼──────────────┼────────┤
│ 10         │ Laptop       │ 999.99 │
│ 20         │ Mouse        │ 29.99  │
└────────────┴──────────────┴────────┘

order_items:
┌──────────┬────────────┬─────┐
│ order_id │ product_id │ qty │
├──────────┼────────────┼─────┤
│ 1        │ 10         │ 1   │
│ 1        │ 20         │ 2   │
│ 2        │ 10         │ 1   │
└──────────┴────────────┴─────┘

Now product info is stored once, referenced by foreign key.

Third Normal Form (3NF): No Transitive Dependencies

Rule: Must be in 2NF, and non-key columns must depend directly on the primary key, not on other non-key columns. Problem solved: Data dependency on non-key attributes.

Violates 3NF

Department location depends on department, not directly on employee_id.

employees (BAD):
┌─────────────┬────────┬─────────────┬───────────────────┐
│ employee_id │ name   │ department  │ dept_location     │
├─────────────┼────────┼─────────────┼───────────────────┤
│ 1           │ Alice  │ Engineering │ Building A        │
│ 2           │ Bob    │ Engineering │ Building A        │
│ 3           │ Charlie│ Sales       │ Building B        │
└─────────────┴────────┴─────────────┴───────────────────┘

Problem: dept_location depends on department, 
not directly on employee_id (transitive dependency)

Fixed: 3NF Compliant

Department details in separate table.

departments:
┌────────────────┬─────────────┬──────────┐
│ department_id  │ dept_name   │ location │
├────────────────┼─────────────┼──────────┤
│ 1              │ Engineering │ Bldg A   │
│ 2              │ Sales       │ Bldg B   │
└────────────────┴─────────────┴──────────┘

employees:
┌─────────────┬─────────┬───────────────┐
│ employee_id │ name    │ department_id │
├─────────────┼─────────┼───────────────┤
│ 1           │ Alice   │ 1             │
│ 2           │ Bob     │ 1             │
│ 3           │ Charlie │ 2             │
└─────────────┴─────────┴───────────────┘

If Engineering moves to Building C, update one row, not two.

Boyce-Codd Normal Form (BCNF): Stricter 3NF

Rule: Every determinant must be a candidate key. BCNF is slightly stricter than 3NF and handles edge cases where 3NF isn't enough. Problem solved: Anomalies from multiple overlapping keys.

Violates BCNF (but satisfies 3NF)

A professor teaches one subject, but a subject can be taught by multiple professors. For simplicity using `professor` but should be `professor_id` via a `professor` table.

teaching_assignments (BAD):
┌────────────┬─────────────┬───────────┐
│ student_id │ subject     │ professor │
├────────────┼─────────────┼───────────┤
│ 1          │ Math        │ Dr. Smith │
│ 1          │ Physics     │ Dr. Jones │
│ 2          │ Math        │ Dr. Smith │
│ 3          │ Math        │ Dr. Smith │
└────────────┴─────────────┴───────────┘

Primary Key: (student_id, subject)
Problem: professor depends on subject (not the full key)
But professor → subject (Dr. Smith always teaches Math)
This dependency isn't captured by the key

Fixed: BCNF Compliant

Separate professor-subject relationship.

professor_subjects:
┌─────────────┬─────────┐
│ professor   │ subject │
├─────────────┼─────────┤
│ Dr. Smith   │ Math    │
│ Dr. Jones   │ Physics │
└─────────────┴─────────┘

student_enrollments:
┌────────────┬─────────────┐
│ student_id │ professor   │
├────────────┼─────────────┤
│ 1          │ Dr. Smith   │
│ 1          │ Dr. Jones   │
│ 2          │ Dr. Smith   │
│ 3          │ Dr. Smith   │
└────────────┴─────────────┘

BCNF vs 3NF: Most tables that are in 3NF are also in BCNF. BCNF matters when you have overlapping candidate keys or complex functional dependencies.

Fourth Normal Form (4NF): No Multi-Valued Dependencies

Rule: No multi-valued dependencies. A table shouldn't store two or more independent many-to-many relationships. Problem solved: Multi-valued facts independent of each other.

Violates 4NF

Employee skills and employee languages are independent of each other.

employee_attributes (BAD):
┌─────────────┬───────────┬──────────┐
│ employee_id │ skill     │ language │
├─────────────┼───────────┼──────────┤
│ 1           │ Python    │ English  │
│ 1           │ Python    │ Spanish  │
│ 1           │ SQL       │ English  │
│ 1           │ SQL       │ Spanish  │
└─────────────┴───────────┴──────────┘

Problem: Creating redundant combinations
If Alice knows Python and SQL, and speaks English and Spanish,
we get 2 × 2 = 4 rows for independent facts

Fixed: 4NF Compliant

Separate independent relationships into their own tables.

employee_skills:
┌─────────────┬───────────┐
│ employee_id │ skill     │
├─────────────┼───────────┤
│ 1           │ Python    │
│ 1           │ SQL       │
└─────────────┴───────────┘

employee_languages:
┌─────────────┬──────────┐
│ employee_id │ language │
├─────────────┼──────────┤
│ 1           │ English  │
│ 1           │ Spanish  │
└─────────────┴──────────┘

Now 2 skills + 2 languages = 4 rows total (not 4 in one table)

When 4NF Matters

Whenever you have independent many-to-many relationships for the same entity. Example: products with multiple categories AND multiple suppliers.

Fifth Normal Form (5NF): No Join Dependencies

Rule: Table cannot be decomposed into smaller tables without losing information. Also called "Project-Join Normal Form" (PJ/NF). Rarely needed in practice. It's designed to eliminate redundancy in relational databases that arises from complex, multi-way relationships (join dependencies) that cannot be reduced to simple binary relationships. A table is in 5NF if it is in 4NF and every join dependency in the table is implied by its candidate keys.

Violates 5NF

Agent sells product in region, but relationships are complex.

agent_product_region (BAD):
┌───────┬─────────┬────────┐
│ agent │ product │ region │
├───────┼─────────┼────────┤
│ Alice │ Laptop  │ West   │
│ Alice │ Mouse   │ West   │
│ Bob   │ Laptop  │ East   │
└───────┴─────────┴────────┘

Business Rules:
- Alice can sell Laptop and Mouse
- Alice works in West region
- Bob can sell Laptop
- Bob works in East region

But table stores ALL combinations, creating redundancy

Fixed: 5NF Compliant

Break into three binary relationships.

agent_products:
┌───────┬─────────┐
│ agent │ product │
├───────┼─────────┤
│ Alice │ Laptop  │
│ Alice │ Mouse   │
│ Bob   │ Laptop  │
└───────┴─────────┘

agent_regions:
┌───────┬────────┐
│ agent │ region │
├───────┼────────┤
│ Alice │ West   │
│ Bob   │ East   │
└───────┴────────┘

product_regions:
┌─────────┬────────┐
│ product │ region │
├─────────┼────────┤
│ Laptop  │ West   │
│ Laptop  │ East   │
│ Mouse   │ West   │
└─────────┴────────┘

To find "Can Alice sell Laptop in West?":
JOIN all three tables

Practical Note: 5NF is rarely applied in real-world databases. It's often too complex and the performance cost of multiple joins outweighs the benefits. Stop at BCNF or 4NF for most applications.

Which Normal Form Should You Use?

For Most Applications: 3NF

3NF eliminates most redundancy and is easy to understand. It's the sweet spot for 99% of business applications.

When to Use BCNF

When you have overlapping candidate keys or complex functional dependencies. Academic databases, scheduling systems.

When to Use 4NF

When you have multiple independent multi-valued facts about the same entity. Employee skills + languages, product categories + suppliers.

Rarely Use 5NF

Only for highly complex relationships with join dependencies. Most developers never need 5NF in their entire career.

Rule of Thumb: Normalize to 3NF by default. Only go further if you have a specific problem that BCNF/4NF solves. Always measure performance before and after, over-normalization can hurt query speed.

Complete Example: E-commerce Schema

Let's normalize our original bad design completely.

Step 1: Identify Entities

Entities: Customers, Products, Orders
Relationships: Customers place Orders, Orders contain Products

Step 2: Create Tables

customers:
┌─────────────┬─────────────┬──────────────────┐
│ customer_id │ name        │ email            │
├─────────────┼─────────────┼──────────────────┤
│ 1           │ Alice Smith │ alice@email.com  │
│ 2           │ Bob Jones   │ bob@email.com    │
└─────────────┴─────────────┴──────────────────┘

Customer info stored once

products:
┌────────────┬──────────┬────────┐
│ product_id │ name     │ price  │
├────────────┼──────────┼────────┤
│ 1          │ Laptop   │ 999.99 │
│ 2          │ Mouse    │ 29.99  │
│ 3          │ Keyboard │ 79.99  │
└────────────┴──────────┴────────┘

Product info stored once

orders:
┌──────────┬─────────────┬────────────┬────────┐
│ order_id │ customer_id │ order_date │ total  │
├──────────┼─────────────┼────────────┼────────┤
│ 1        │ 1           │ 2024-01-15 │ 1059.97│
│ 2        │ 2           │ 2024-01-16 │ 999.99 │
│ 3        │ 1           │ 2024-01-20 │ 79.99  │
└──────────┴─────────────┴────────────┴────────┘

Links customer to order

order_items:
┌──────────────┬──────────┬────────────┬─────────┬────────┐
│ order_item_id│ order_id │ product_id │ qty     │ price  │
├──────────────┼──────────┼────────────┼─────────┼────────┤
│ 1            │ 1        │ 1          │ 1       │ 999.99 │
│ 2            │ 1        │ 2          │ 2       │ 29.99  │
│ 3            │ 2        │ 1          │ 1       │ 999.99 │
│ 4            │ 3        │ 3          │ 1       │ 79.99  │
└──────────────┴──────────┴────────────┴─────────┴────────┘

Junction table for many-to-many relationship. Stores price at purchase time.

Benefits: Customer info stored once, product info stored once, no redundancy, easy to update, maintains data integrity.

Creating the Normalized Schema

Here's how to create our normalized e-commerce database.

Customers Table

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL
);

Products Table

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

Orders Table

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date DATE NOT NULL,
    total DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Order Items Table

CREATE TABLE order_items (
    order_item_id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    qty INTEGER NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

When to Denormalize

Sometimes breaking normalization rules is intentional for performance.

✅ When to Denormalize

Read-heavy systems: Avoid expensive joins on frequent queries
Reporting/analytics: Pre-compute aggregates for dashboards
Caching: Store calculated values to avoid recalculating
Performance bottlenecks: When joins are measurably slow

Example: Storing Order Total

Instead of calculating SUM(price * qty) every time:

orders:
┌──────────┬─────────────┬────────┐
│ order_id │ customer_id │ total  │ ← Denormalized (calculated value)
├──────────┼─────────────┼────────┤
│ 1        │ 1           │ 1059.97│
│ 2        │ 2           │ 999.99 │
└──────────┴─────────────┴────────┘

Trade-off: Faster reads, but must update total when order items change

Important: Denormalize only after measuring performance issues. Start normalized, denormalize selectively when needed.

Database Design Best Practices

✅ Use Descriptive Names

Tables: plural nouns (customers, orders). Columns: clear, unambiguous (customer_id not just id, created_at not date).

✅ Always Use Primary Keys

Every table needs a primary key. Prefer surrogate keys (auto-increment integers or UUIDs) over natural keys.

✅ Define Foreign Key Constraints

Always declare foreign key relationships. They enforce referential integrity automatically.

✅ Use NOT NULL Wisely

Mark required fields as NOT NULL. Don't allow NULL for fields that should always have values (email, created_at).

✅ Index Foreign Keys

Foreign key columns should almost always have indexes for fast joins and lookups.

✅ Choose Appropriate Data Types

Use the smallest data type that fits. Don't use VARCHAR(255) for everything. Use INTEGER for whole numbers, DECIMAL for money, DATE/TIMESTAMP for dates.

Common Design Patterns

One-to-Many: Orders → Order Items

One order has many items.

orders (1)          order_items (Many)
┌──────────┐        ┌────────────┬──────────┐
│ order_id │───────<│ order_id   │          │
│ ...      │        │ product_id │          │
└──────────┘        └────────────┴──────────┘

Foreign key in order_items points to orders

Many-to-Many: Students ↔ Courses

Students take many courses, courses have many students. Requires junction table.

students (Many)    enrollments (Junction)    courses (Many)
┌────────────┐     ┌────────────┬───────────┐  ┌───────────┐
│ student_id │────<│ student_id │ course_id │>─│ course_id │
│ name       │     │ grade      │           │  │ name      │
└────────────┘     └────────────┴───────────┘  └───────────┘

enrollments connects students to courses

Self-Referencing: Employee → Manager

Table references itself (employees have managers who are also employees).

employees:
┌─────────────┬────────┬────────────┐
│ employee_id │ name   │ manager_id │──┐
├─────────────┼────────┼────────────┤  │
│ 1           │ Alice  │ NULL       │  │ (CEO)
│ 2           │ Bob    │ 1          │<─┘ (reports to Alice)
│ 3           │ Charlie│ 1          │<─┐ (reports to Alice)
└─────────────┴────────┴────────────┘  │
                         └─────────────┘
manager_id is foreign key to employee_id in same table

Audit Columns: Tracking Changes

Add these columns to track when and who modified records.

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    
    -- Audit columns
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by INTEGER,
    updated_by INTEGER
);

Standard Audit Columns

created_at: When record was created
updated_at: When record was last modified
created_by: User who created it (optional)
updated_by: User who last modified it (optional)

Soft Deletes: Never Lose Data

Instead of DELETE, mark records as deleted. Allows recovery and audit trail.

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    is_deleted BOOLEAN DEFAULT FALSE,
    deleted_at TIMESTAMP NULL
);

Using Soft Deletes

-- Instead of: DELETE FROM customers WHERE customer_id = 5;
UPDATE customers 
SET is_deleted = TRUE, deleted_at = CURRENT_TIMESTAMP
WHERE customer_id = 5;

Record still exists, just marked as deleted

-- Always filter out deleted records
SELECT * FROM customers 
WHERE is_deleted = FALSE;

Active customers only

Schema Evolution: Planning for Change

Databases evolve. Plan for adding columns, tables, and relationships over time.

Adding Columns Safely

-- Add nullable column (safe for existing data)
ALTER TABLE customers 
ADD COLUMN phone VARCHAR(20);

-- Add column with default (safe)
ALTER TABLE customers 
ADD COLUMN loyalty_points INTEGER DEFAULT 0;

Use Migrations

Track schema changes with migration files (numbered scripts). Recommended tool: Alembic (lightweight database migration tool for usage with the SQLAlchemy Database Toolkit for Python).

migrations/
  001_create_customers.sql
  002_create_products.sql
  003_create_orders.sql
  004_add_customer_phone.sql
  005_add_loyalty_points.sql

Each file: one change, with rollback script

Key Takeaways

Normalization eliminates redundancy - store each fact once
1NF: Atomic values only (no lists in cells)
2NF: No partial dependencies (applies to composite keys)
3NF: No transitive dependencies (non-keys depend on key directly)
Foreign keys enforce relationships - maintain referential integrity
Denormalize selectively - only for measured performance needs
Use surrogate keys - simple integers or UUIDs as primary keys
Add audit columns - created_at, updated_at for tracking
Consider soft deletes - mark deleted instead of removing
Good database design is like good architecture, invest time upfront, and it pays dividends forever

Back to "Aggregations & Grouping"

Database FundamentalsLesson 7

Next: "Indexes & Performance"