Phase 4: Databases & SQL for AI/ML · ~35 minutes · SQL · JSON · SQLite

NoSQL and Schema Design

Reading one customer's data is one line in the document model, and a genuine hassle in the relational one. Summing across every customer's orders is the exact opposite. Same data, two models, two different real costs.

Hiring signal: Justifies a schema choice from actual access patterns, not from which one felt like less setup

What you will learn

Explain document/key-value stores as a real alternative, not a strict replacement, for relational tables
Explain when schema flexibility earns its complexity vs. when it just defers a problem
Explain normalization vs. denormalization as a real tradeoff, not a rule
Justify a schema choice from a dataset's actual access patterns

Introduction

Type: Learn Languages: SQL, JSON Prerequisites: Lesson 04 (Window Functions) Time: ~35 minutes

Objective

Learning objectives

Explain document/key-value stores as a real alternative, not a strict replacement, for relational tables
Explain when schema flexibility earns its complexity vs. when it just defers a problem
Explain normalization vs. denormalization as a real tradeoff, not a rule
Justify a schema choice from a dataset's actual access patterns

What you're building

Using this phase's customers/orders data (or your own of similar shape):

Build the denormalized document version — one JSON document per customer, orders nested inside, following this lesson's pattern
Write and run the "easy" query (fetch one customer's full profile including nested data) in both models, and confirm both work
Write and run the "hard" query (aggregate/sum across all customers' orders) in both models, and record which one required meaningfully more complex SQL
Write schema_tradeoff.txt describing a real application's actual access pattern (your own invention) and justifying, from that pattern specifically — not general preference — which model fits better

An application's primary workload is: generating daily reports that aggregate total sales by product category across the entire business, run every morning. Individual-customer lookups are rare. Which model better fits this specific access pattern?

This is the mirror image of this lesson's single-user-profile predict block. The dominant, described access pattern here is exactly the 'hard' query category from this lesson — aggregating across many records — while the document model's actual strength (cheap single-record reads) is explicitly described as rare in this workload. Applying the same reasoning as before, just with the access pattern flipped, correctly points to the relational model as the better fit here, using GROUP BY exactly as demonstrated throughout this phase, not because relational is generally superior.

Unlock the full lesson

You've read the first 2 sections. The rest of this lesson covers The Problem, Check Yourself, Key Terms & Next — plus a hands-on lab, quiz, and project artifact.

Create a free account to unlock Phase 0 and Phase 1 of every course — no credit card.