My Skill Lab
Programming & Web DevelopmentIntermediate

Database Design and SQL for Developers

Schemas, normalisation, indexes, transactions and ORMs.

Taught by Sagar Khadka · Full-Stack Developer

What you'll learn

  • Design a relational schema from requirements with correct primary keys, foreign keys and junction tables
  • Normalise tables to third normal form and justify any deliberate denormalisation
  • Enforce data integrity with NOT NULL, UNIQUE, CHECK and foreign-key constraints and the right column types
  • Add B-tree, composite and partial indexes and confirm their effect with EXPLAIN ANALYZE
  • Use transactions and row-level locking to prevent lost updates and overselling
  • Ship zero-downtime migrations and fix N+1 query problems in an ORM

About this course

Database Design and SQL for Developers is for software engineers who can write basic SELECT queries but want to design schemas that stay fast and correct as an application grows. Working in PostgreSQL, you will model entities and relationships, choose primary and foreign keys, normalise tables to third normal form and know when to denormalise on purpose. You will enforce business rules with constraints and sensible data types, add the right indexes and read EXPLAIN ANALYZE output, and use transactions, isolation levels and row locks to prevent race conditions such as overselling stock. The course finishes with zero-downtime schema migrations and the ORM pitfalls behind slow pages, including the N+1 query problem. Examples use PostgreSQL 17 or 18 running in Docker, with ORM snippets in Django and notes for Rails and Prisma. You should know one backend language and basic SQL.

Course content

7 lessons
  1. 1How Applications Use Relational DatabasesWhat a relational database does for your app, plus a working PostgreSQL setup with your first tables.Preview
  2. 2Modelling Data: Entities, Keys and RelationshipsTurn requirements into entities, pick primary keys and model one-to-many and many-to-many links.
  3. 3Normalisation Without the JargonRemove duplicated data with first, second and third normal form, and denormalise only on purpose.
  4. 4Constraints and Data Types That Protect Your DataPick correct column types and let NOT NULL, UNIQUE, CHECK and foreign keys enforce business rules.
  5. 5Indexes and Query PerformanceHow B-tree indexes work, composite column order, partial indexes and reading EXPLAIN ANALYZE.
  6. 6Transactions, Isolation and LockingGroup changes atomically, understand isolation levels and prevent race conditions with row locks.
  7. 7Migrations and ORMs in ProductionChange live schemas safely with expand and contract, and avoid the ORM mistakes behind slow pages.