System Design line, station 2 of 8Data line, station 2 of 6
SQL & Data Modeling
Design PostgreSQL tables that keep every fact in one place, write the SQL behind real reports, make queries fast with the right indexes, and keep data correct under concurrent writes and live schema changes.
- 6lessons
- 4 hto read and do
- Beginnerlevel
- 6podcast episodes
- 11checked exercises
- 1simulators
Tools you'll use in this course
The lessons open these with a template ready. They work for your own job too, no course needed.
About this course
Almost every app is a database with screens on top. When the tables are right, features are easy and reports agree; when they are wrong, every number becomes an argument. This course teaches the database skills that sit under every backend, report and dashboard, using PostgreSQL and the Prisma ORM, starting from your first CREATE TABLE.
You will model real data with keys, types and constraints, write the queries behind everyday reports (joins that keep the right rows, grouping, EXISTS, window functions and safe upserts), look inside a B-tree to choose indexes that actually help, read query plans to fix slow queries and N+1 loops, stop lost updates and overselling with transactions and locks, and change a busy schema without taking the app down.
Every lesson follows Mangrove Market, a fictional grocery chain with 320 stores, an app and 1,800 partner shops, so the tables and numbers stay concrete. Each lesson has checkpoints, SQL exercises that run on a real PostgreSQL engine in your browser, flashcards and a podcast episode. The course ends with a capstone that designs the Partner app's ordering database, and a final exam.
What you will be able to do
- Design normalized tables with the right keys, types (money in integer cents, moments in timestamptz) and constraints, in SQL and in Prisma
- Write the queries behind real reports: joins that keep the right rows, grouping, EXISTS, window functions and upserts that are safe to retry
- Choose indexes from the shape of a query, write queries an index can serve, and prove the gain with EXPLAIN
- Read a query plan from the bottom up, find the step that holds the time, and fix slow queries and N+1 loops
- Keep data correct under concurrent writes with conditional updates, row locks, version columns and the right isolation level
- Change a busy production schema without downtime using lock timeouts, expand and contract, and batched backfills
Syllabus
6 stops, about 4 h in total. Each stop fills in when you complete the lesson.
Modeling data in tables
Good tables make every later feature easier. Bad tables turn every report into an argument about which number is right. This first lesson of SQL & Data Modeling shows you how to split real grocery data into tables that keep each fact in one place, and how to write them in SQL and in Prisma. You need no database experience to start.
Data modelingNormalizationConstraintsPostgreSQL
SQL you'll actually use
An ORM covers the daily create, read and update work. The questions a business asks, such as "top sellers per store" or "which stores dropped this week", need real SQL. This lesson covers the SQL patterns behind almost every shop report, run against the tables from Modeling data in tables.
SQL queryingJoinsWindow functionsUpserts
Indexes from the inside
An index is the most powerful speed tool a database has, and also the most misused. This lesson opens one up, so you can see why one query drops from 34 ms to 2 ms while another ignores the index completely.
IndexingComposite indexesQuery tuning
Reading query plans and fixing slow queries
When a screen is slow, guessing wastes days. Postgres will tell you exactly how it ran a query, step by step, with the time and pages each step cost. This lesson teaches you to read that report and turn it into a fix.
Query tuningEXPLAINN+1 queries
Transactions, ACID, isolation and locks
Two cashiers sell the last two cartons at the same second. Both sales succeed, four cartons leave the shelf, and the stock count says zero when it should say minus two. This lesson shows exactly how that happens, and the small number of patterns that stop it.
TransactionsIsolation levelsConcurrency control
Changing the schema without downtime
A one-line ALTER TABLE can freeze every store's tills for minutes, even though it takes milliseconds on your laptop. This lesson shows why, which changes are safe on a busy table, and the expand-and-contract method for the ones that are not.
Schema migrationsZero-downtime deploysBackfills
Capstone: Design the Partner app's ordering database
A realistic project with a rubric and a model answer.
Final exam and certificate
10 questions drawn at random, 70% to pass.
Capstone project
Design the Partner app's ordering database
Mangrove Market is moving partner shop ordering out of a shared spreadsheet and into the Partner app. About 1,800 independent corner shops, such as Ravi's, order stock wholesale from 4 distribution centres (DCs); each order has several lines, prices are in cents, each shop has a credit limit for unpaid orders, and DC stock must be reserved when an order is confirmed, without ever promising cartons that are not there. Imran, the product manager, needs three reports for the wholesale team, and Ken, the engineering manager, wants the design to stay fast with a year of data and to survive its first schema change after launch. Build the schema, the reports, a safe order-placement transaction, the indexes with proof, and one zero-downtime migration plan.
You will hand in
- schema.sql: tables for partner shops (as site rows or their own table), articles, DC stock, orders and order lines, with primary and foreign keys, CHECK and UNIQUE constraints, money in integer cents, times in timestamptz, and an index on every foreign key; it must run on PostgreSQL 16 or newer (PGlite in the browser is fine)
- reports.sql: shops with no order in the last 14 days (including shops that never ordered), the top 5 products per DC in the last 30 days, and order value per week with the change against the week before, each with sample output
- place_order.sql (or a Prisma function): one transaction that checks the shop's credit limit, reserves stock with conditional updates in a consistent order, and creates the order and its lines, failing cleanly when stock or credit is short
- indexes.md: the index for each report and for the order screen, with EXPLAIN (ANALYZE, BUFFERS) before and after on generated data of at least 1 million order lines
- migration.md: a plan to add a required delivery_slot column to orders without downtime, as deploys and migration files
Check what you know
Already know some of this? Answer 6 quick questions, one from each lesson, and we will mark the lessons you can skim. It takes about 3 minutes and nothing is graded.
Questions
How long does the course take?
About 4 hours for the six lessons, including the checkpoints and practice tasks. The podcast episodes add about 75 minutes if you listen to all of them. Allow 2 to 6 hours more for the capstone project.
What do I need to know before I start?
No database experience is needed; the first lesson starts from CREATE TABLE. It helps to know what an app and an API are, which the How the Web Works course covers. The Prisma examples are in TypeScript, but you can skip them and still learn all the SQL.
Do I need to install PostgreSQL?
Not for the practice tasks. The SQL exercises run in your browser on PGlite, a real PostgreSQL engine compiled to WebAssembly. To follow the Build-it sections, install PostgreSQL 16 or newer, for example with Docker as the first lesson shows. Query plans and timings need a real server, because the browser engine reports them differently.
I am a product manager, not a developer. Is this course for me?
Yes. Each lesson has a Bring it to your team section with the questions to ask in a schema, report or migration review, and the red flags to watch for. Doing the SQL exercises will also let you answer your own data questions instead of waiting for a report.
Is the certificate free, and how does the final exam work?
Yes, the course, the exam and the certificate are free. Each attempt gives you 15 questions picked at random from the course's question bank, and you pass with 70% or more. If you do not pass, you can try again after 24 hours.
Is it really free?
Yes. Every lesson, podcast episode, exercise, quiz, final exam and certificate is free. The labs and their templates for real work are free too. Plus and Premium add every lab challenge, weekly extras and a mentor on top, and they never lock the tools or the courses.