Stage 2 of 9 · 6 lessons · ~2 weeks

Relational databases: the sensible default

PostgreSQL and MySQL store related records in tables with keys, constraints and transactions. For most apps one of them is the right first choice, and JSON columns cover the parts that do not fit a fixed shape.

Start: What a relational database gives you

Lessons· one lesson for each topic, in this order

Do this

Install PostgreSQL locally, in Docker, or create a free managed project.
Create customers, products and orders tables with primary and foreign keys.
Add a jsonb attributes column to products and fill it for a few rows.
Write a join, a JSON filter and a GROUP BY total, and save them in a .sql file.
Trigger a foreign key error on purpose and read it.
Add one migration that changes the schema, and commit it to Git.

Start with· follow one of these

CoursePaid
Grokking Relational Database Design and Modeling
Design Gurus · Intermediate · $98 one-time
Data modelling, ER diagrams, keys and normalisation for the relational default, with worked designs
GuideFree
PostgreSQL Tutorial
PostgreSQL Global Development Group · Beginner
The short official tutorial: create a database, tables and your first queries in an evening

Practise

db<>fiddle
Run SQL on PostgreSQL, MySQL or SQLite in the browser and compare how each handles JSON
SQLBolt
Free interactive lessons if your SQL needs warming up before the hands-on task

Keep handy

PostgreSQL Documentation
The JSON types chapter: jsonb operators, containment and GIN indexes
Supabase Documentation
Managed PostgreSQL with auth, storage and an API, useful when you want Postgres without running a server

Also worth your time

JSON Types (opens in a new tab)Read
json versus jsonb, the operators and how to index JSON for fast lookups
Sefism SQL and Databases RoadmapGo deeper
The full Sefism roadmap for SQL: queries, schema design, transactions, indexes and SQL from code

Before moving on: A PostgreSQL (or MySQL) database with three related tables, keys and constraints, a JSON column for flexible attributes, and saved queries for a join, a JSON filter and a daily total.

Tauseef's advice

If you are unsure, start with PostgreSQL and stay there until a real need pushes you out. Most projects I review that run two or three databases would be simpler and more reliable with one. When you want depth, the SQL and databases roadmap goes all the way from queries to query plans.