Data Modeling: Structuring Information for Analysis

Why This Matters

Bad schema:

orders: id, customer_name, customer_email, product_name, 
        product_price, quantity, total

Problems:
– Customer data duplicated in every order
– Update customer email → must update 1000 order rows
– Product price change → old orders show wrong price
– Inconsistencies inevitable

Good schema:

customers: id, name, email
products: id, name, price
orders: id, customer_id, product_id, quantity, total

Benefits:
– Single source of truth for customers/products
– Updates happen once
– Consistency guaranteed

This is data modeling—organizing data to prevent problems.

---

What You’ll Learn

1. What normalization means (eliminating redundancy)
2. The three normal forms (1NF, 2NF, 3NF)
3. When to denormalize (performance trade-offs)
4. Common modeling patterns (one-to-many, many-to-many)

---

First Normal Form (1NF): Atomic Values

Violation Example

CREATE TABLE students (
    id INT,
    name VARCHAR(100),
    courses VARCHAR(500)  -- "Math, Physics, Chemistry"
);

Problems:
– Can’t query “students taking Physics” efficiently
– Can’t enforce course existence
– Awkward parsing required

Fixed: One Value Per Cell

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE enrollments (
    student_id INT,
    course VARCHAR(100),
    FOREIGN KEY (student_id) REFERENCES students(id)
);

Benefits:
– Query students in specific course: simple WHERE clause
– Each enrollment is independent row
– Can add enrollment details (grade, date)

Rule: No repeating groups, each cell contains single value.

---

Second Normal Form (2NF): No Partial Dependencies

Violation Example

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    product_name VARCHAR(100),  -- Depends only on product_id!
    product_price DECIMAL,      -- Depends only on product_id!
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

Problem: product_name and product_price depend on product_id alone, not the full key (order_id, product_id).

Consequences:
– Product name duplicated across all orders
– Change product name → update 1000 rows
– Inconsistency risk

Fixed: Separate Product Table

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL
);

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

Rule: No non-key column depends on only part of the primary key.

---

Third Normal Form (3NF): No Transitive Dependencies

Violation Example

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100),
    department_location VARCHAR(100)  -- Depends on department!
);

Problem: department_location depends on department, which depends on id → transitive dependency.

Consequence: Update department location → must update all employees in that department.

Fixed: Separate Department Table

CREATE TABLE departments (
    name VARCHAR(100) PRIMARY KEY,
    location VARCHAR(100)
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100),
    FOREIGN KEY (department) REFERENCES departments(name)
);

Rule: No non-key column depends on another non-key column.

---

Relationship Patterns

One-to-Many

Example: One customer, many orders.

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total DECIMAL,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Pattern: Foreign key in “many” side points to “one” side.

Many-to-Many

Example: Students enroll in courses, courses have multiple students.

Wrong (can’t represent with single foreign key):

-- Can't do this
CREATE TABLE students (
    id INT PRIMARY KEY,
    course_id INT  -- Only one course?!
);

Right: Junction table.

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE courses (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    grade VARCHAR(2),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

Pattern: Junction table with two foreign keys.

One-to-One

Example: User has one profile.

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(100),
    password_hash VARCHAR(255)
);

CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,
    bio TEXT,
    avatar_url VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

When to use: Separate frequently-accessed data from rarely-accessed data.

---

When to Denormalize

Normalized: Multiple Joins

-- Get order with customer name and product details
SELECT o.id, c.name, p.name, oi.quantity, p.price
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id;

Cost: 3 joins per query → slow for large tables.

Denormalized: Redundant Data

-- Copy customer/product info into order_items
CREATE TABLE order_items_denormalized (
    order_id INT,
    product_id INT,
    product_name VARCHAR(100),  -- Redundant
    product_price DECIMAL,      -- Redundant
    quantity INT
);

-- Query is simple
SELECT * FROM order_items_denormalized WHERE order_id = 123;

Benefit: Fast reads (no joins).
Cost:
– Data duplication
– Update anomalies (product name changes → must update all order items)
– Inconsistency risk

When to Denormalize

Read-heavy workloads:
– Analytics dashboards
– Reporting systems
– Caches

NOT for:
– Transactional systems (OLTP)
– Data with frequent updates
– When consistency is critical

---

Practical Example: E-Commerce Schema

Normalized Design

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE
);

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL,
    stock_quantity INT
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    order_date TIMESTAMP,
    status VARCHAR(50),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    price_at_purchase DECIMAL,  -- Snapshot price
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

Key decision: price_at_purchase in order_items.
Why?: Product price changes, but historical orders show original price.
This is intentional denormalization for business requirement.

---

Cross-Domain Connections

Code: Data Structures

From CODE-100.2: Data Structures:

Database schema = choosing data structure for persistence.
– Table = list of dictionaries
– Foreign key = pointer/reference
– Index = hash table for fast lookup

Mathematics: Set Theory

From MATH-200.3: Set Theory:

Relationships are set operations:
– JOIN = Cartesian product + filter
– UNION = Set union
– INTERSECT = Set intersection

---

Pattern Passport

Pattern Observed: HIERARCHICAL STRUCTURE + REPRESENTATION TRADE-OFF

How It Appears Here:
– Normalization = Eliminate redundancy via hierarchy (entities → relationships)
– Denormalization = Trade redundancy for query performance
– Foreign keys = References creating relationships

Representation:
– Tables: Collections of rows (entities)
– Columns: Attributes of entities
– Foreign keys: Relationships between entities

Transformation Rules:
– 1NF: Atomic values
– 2NF: No partial dependencies
– 3NF: No transitive dependencies
– Denormalization: Intentional redundancy for performance

Assumptions:
– Data has identifiable entities and relationships
– Consistency matters (for normalized designs)
– Query patterns known (for denormalization decisions)

Failure Conditions:
1. Update anomalies: Changing one fact requires many row updates
2. Inconsistency: Same fact stored differently in different places
3. Performance: Excessive joins slow queries
4. Complexity: Over-normalized schema hard to query

Related Disciplines:
– Code CODE-100.2: In-memory data organization
– Math MATH-200.3: Set operations

Next Learning Steps:
1. DATA-100.3: SQL Fundamentals — Querying normalized data
2. DATA-200.1: Indexing Strategies — Making queries fast
3. DATA-200.3: Data Pipelines — ETL and transformations

---

Summary: Organization Prevents Problems

Data modeling = designing schema to ensure consistency and performance.

Three key insights:

1. Normalization eliminates redundancy: Single source of truth
2. Relationships via foreign keys: Link entities without duplication
3. Denormalization is trade-off: Speed vs. consistency

Normal forms:
– 1NF: Atomic values
– 2NF: No partial key dependencies
– 3NF: No transitive dependencies

When to denormalize:
– Read-heavy workloads
– Known query patterns
– Performance critical
– Consistency less critical

The value: Good schema design prevents data quality disasters, makes queries efficient, enables system scalability.

---

Exercises

1. Normalize: Given books(id, title, author_name, author_birthdate, publisher_name, publisher_city), design normalized schema.

2. Identify violation: What normal form is violated? employees(id, name, project, project_budget, project_manager)

3. Many-to-many: Design schema for “authors write books, books have multiple authors.”

4. Denormalize trade-off: For analytics dashboard showing daily sales by product category, would you normalize or denormalize? Why?

---

Revision History

2026-07-30: Substantially revised with concrete examples, normal forms explained, denormalization trade-offs.

2024-09-10: Originally published.

---

Reproducible Code

Available at:
– Code/schema_examples.sql
– validation/test_normalization.py

cd Categories/06-Data-and-Microsoft-Fabric/100.2-Data-Modeling
python validation/test_normalization.py