Module 1 · The Data Layer · Unit 03

Databases I — SQL vs NoSQL & Schema Design

"Relational or non-relational?" is one of the most common deep-dive forks. Getting it right — with reasons, not vibes — anchors the rest of your design.

⏱ 30 min Prereq: Units 01–02 You'll be able to: pick a store and defend it, then model the data

By the end of this unit

Explain what relational vs non-relational actually buy you (not just "SQL good").
Map the main NoSQL families to the access patterns they're built for.
Reason about ACID vs BASE and when each is acceptable.
Sketch a schema from requirements, including the index that makes the hot query fast.

The real distinction

The interview cliché is "SQL = structured, NoSQL = flexible." The useful framing is about what guarantees and access patterns the engine optimizes for. Relational databases give you a rigid schema, multi-row transactions, and a powerful query planner that can join across tables. Non-relational stores trade some of that away to get horizontal scale and a data layout matched to one access pattern.

Relational (Postgres, MySQL)

Strong schema + constraints; data integrity by default.
ACID transactions across rows/tables.
Flexible queries & joins after the fact.
Scales up easily; scaling out (sharding) is manual work.
Default choice when relationships and correctness matter.

Non-relational (NoSQL)

Schema-flexible; denormalized for a known query.
Built to scale out across many nodes.
Often eventual consistency, tunable per call.
Joins are your job (in app code) — model for the read.
Default when you need massive scale on a simple, known pattern.

Say this in the interview "I'll start with Postgres because the data is relational and I want transactional integrity for X. If the ____ table becomes a write/scale bottleneck, I'd move that specific access pattern to a NoSQL store — e.g., the feed or the metrics — rather than abandoning SQL wholesale." Nuanced, hybrid answers score higher than dogma.

The NoSQL families

Family	Examples	Shines at
Key–value	DynamoDB, Redis	O(1) lookups by key: sessions, carts, feeds, caches.
Wide-column	Cassandra, Bigtable	Massive write throughput, time-series, per-partition queries.
Document	MongoDB, Couchbase	Nested/variable objects read as a unit: catalogs, profiles.
Graph	Neo4j, Neptune	Relationship-heavy traversals: social graph, fraud rings.

Model for the read In NoSQL you design the table around the query you must serve fast, then accept duplicating data. "How will this be read?" comes before "how is it stored?" — the reverse of relational modeling.

ACID vs BASE

ACID

Atomicity · Consistency · Isolation · Durability. Transactions either fully happen or not at all, leaving the DB valid. Non-negotiable for money, inventory, anything where a half-write is a bug.

BASE

Basically Available · Soft state · Eventually consistent. Favors availability and scale; accepts that replicas converge over time. Fine for likes, view counts, feeds, where a brief stale read is harmless.

You'll formalize the tradeoff behind BASE in Unit 05 (CAP). For now: match the guarantee to the cost of being wrong.

Schema design, fast

From requirements → entities → relationships → indexes. For a photo app:

users(id PK, handle, name, created_at)

posts(id PK, user_id FK, media_url, caption, created_at)

follows(follower_id, followee_id, PRIMARY KEY(follower_id, followee_id))

likes(user_id, post_id, PRIMARY KEY(user_id, post_id))

The hot query — "latest posts by people I follow" — needs an index on posts(user_id, created_at DESC). Always name the index that makes your headline query fast. Note media_url points at an object store (Unit 11); the database holds metadata, not blobs.

Normalization vs denormalization — when to break the rules

Normalize (no duplicated data) to keep writes simple and avoid update anomalies — the relational default. Denormalize (copy data into the row that reads it) to avoid expensive joins on the read path at scale. Example: store like_count on the post row instead of COUNT(*)-ing the likes table on every feed render. The cost is keeping the counter in sync on every like/unlike — a classic consistency tradeoff you should name out loud.

Quick check

You're designing a ledger for account balances and transfers. Which store and why?

Relational + ACID. Money demands atomic, isolated transactions: the debit and credit succeed together or not at all, and no concurrent reader sees a half-completed transfer. Eventual consistency here is a correctness bug, not a performance tradeoff.

Quick check

A logging/metrics system ingests 500k events/sec, queried by time range per service. Best fit?

Wide-column. The pattern is enormous append-only writes plus range scans within a partition — exactly what LSM-tree, wide-column stores are built for. A single relational node would fall over on write volume.

Practice before you move on

For a ride-hailing app, decide the store for each: (a) user accounts & payments, (b) live driver locations updated every few seconds, (c) trip history for analytics. Justify each in one sentence naming the access pattern. (Hints: ACID; high-write geospatial KV; columnar/warehouse.)

Finished this 30-min unit?