SQL vs NoSQL

Post four is deliberately code-heavy because intuition for SQL versus NoSQL comes from writing both, not reading about either. We take one concrete, familiar scenario — a blog with users and posts — and implement it twice: once relationally in Postgres, once as documents in MongoDB. The same requirements, expressed two ways, surface exactly where each model is comfortable and where it strains.

Work through the snippets in order. By the end you should be able to look at a new feature requirement and predict which shape will make it easy and which will make it awkward.

The scenario is intentionally minimal so the modeling differences aren't buried under domain complexity. Users write posts — a one-to-many relationship, the most common shape in real applications. In the relational world this is two tables linked by a foreign key. In the document world it's a single collection where each user document contains its posts.

That single decision — separate tables versus nested arrays — drives every query difference that follows. Watch how the same English question ('show me this user's posts,' 'rank posts globally') produces very different code depending on which shape you chose up front.

The relational schema declares two tables and the link between them. SERIAL gives each row an auto-incrementing id; PRIMARY KEY makes that id the unique handle; and the REFERENCES clause on posts.user_id is the foreign key that ties each post to exactly one user. NOT NULL constraints document and enforce required fields.

Notice what the database now guarantees for free: you cannot insert a post pointing at a user that doesn't exist, and you cannot create a user without a name. The structure is explicit and self-documenting — anyone reading this DDL understands the data model immediately, which is a real maintainability win.

This is the join that defines relational querying. The question — Ava's name plus all her post titles — spans two tables, so we match posts to users on the foreign key and filter to user 1. The engine does the matching; we just declare the relationship and the filter.

The key insight is that the post data was never copied into the user row. It lives once, in the posts table, and the join reassembles the combined view on demand. ORDER BY makes the result deterministic. This per-query join cost is the price of normalization, and for a single-user lookup it's small and well-indexed.

The MongoDB version inverts the design: posts are embedded as an array inside the user document. There are no two collections and no foreign key — the relationship is expressed by physical nesting. Inserting a user with their posts is a single operation producing a single self-contained object.

This is the document model's core bet: data that is read together should be stored together. For the access pattern 'show one user and everything about them,' this is ideal — one lookup returns the whole picture. The bet only pays off if that's genuinely how you read the data most of the time.

Reading is now trivial: findOne by _id returns the user and all embedded posts in a single lookup, no join required — the document model's headline advantage made concrete. Adding a post uses $push to append to the array in place, an atomic update on that one document.

This is genuinely elegant for the intended pattern and it's why document stores feel so productive early in a project. The cost is hidden here and surfaces later: because posts are scoped inside each user, any operation that needs to treat posts as a global collection has no direct path to them.

This diagram lays the two shapes side by side so the structural difference is unmistakable. On the SQL side, data is split into users and posts and linked by posts.user_id pointing at users.id, then joined at query time. On the Mongo side, posts live as an array inside the user, with no cross-document link, read in one shot.

The split-and-link design optimizes for flexibility — any new cross-cutting query is just a different join. The nested design optimizes for locality — everything about one entity is in one place. Neither is universally better; each is the right answer to a different dominant question.

This is the query that exposes the document model's weakness and SQL's strength: rank the top posts across all users. In SQL it's natural — posts are already a first-class table, so we join, order by likes, and limit. The relational shape made no assumption about whose posts we'd query, so a global ranking is just another query.

In MongoDB the posts are buried inside per-user arrays. To rank them globally you must $unwind every user's array into individual post records first, then group and sort — more work, and it touches every document. The embedded design optimized the single-user read at the direct expense of this cross-cutting one.

This slide names the general principle behind the two queries. The embedded model excels at 'show one entity and its children' and struggles with 'treat the children as a global set,' because retrieving them globally means unwinding every parent. The relational model is the mirror image: cross-cutting queries are natural joins, while even the simple single-user read requires a join.

The meta-lesson is that there's no free design. Each shape optimizes the access pattern you expect to run most and taxes the others. Good data modeling starts by honestly identifying your dominant read pattern, then choosing the shape that makes it cheap.

The closing tips convert the exercise into a reusable modeling heuristic. In SQL you model entities — the nouns of your domain — as tables and combine them with joins as questions demand. In MongoDB you model the read: embed data that is fetched together as a unit, and reference (store an id pointing elsewhere) data that is queried across the whole set.

The decisive factor is your access pattern, not aesthetics. Embed when data is read as a unit and rarely needed independently; reference when the same data is shared or queried globally. Get the dominant access pattern right and the rest of the design largely follows.

Post four turned the comparison tactile: the same blog modeled in Postgres and MongoDB, with the queries each shape makes easy and the queries each makes hard. You've now felt, not just read, why access pattern drives data modeling.

Post five closes the day with the failure modes — the specific mistakes that turn the right database into the wrong one. Picking NoSQL for scale you'll never reach, modeling relational data as documents, joining in application loops, ignoring the shard key, and treating 'schemaless' as 'no design needed.' Knowing these is what separates someone who has read about databases from someone who can run one in production.