RizTech Academy logo
RizTech Academy

Database Foundation

SQL and schema design on PostgreSQL — the skill that outlasts every framework you will ever learn.

Frameworks come and go. The database outlives all of them, and a badly designed schema is the one mistake you cannot cheaply refactor your way out of.

This course teaches SQL on PostgreSQL, from your first SELECT through to schema design, indexes and transactions. It spends real time on modelling decisions, because a query you got wrong costs you an afternoon and a schema you got wrong costs you a year.

One module covers MongoDB: what document databases genuinely do better, and the far more common case where reaching for one is a mistake you will pay for later.

You finish able to design a normalised schema, write queries that do not fall over as data grows, and explain why a slow query is slow rather than guessing.

Who this is for

  • Anyone heading into backend or full-stack work
  • Developers who can write basic SQL but have never designed a schema
  • Self-taught developers whose data layer is whatever the ORM happened to do
  • Anyone taking the Full-Stack Web Development course, where this is assumed

What you need first

  • No programming experience is needed for the SQL modules.
  • The application module assumes basic Python or JavaScript — either foundation course here is more than enough.

Tools you will use

PostgreSQL 16psqlDBeaver or pgAdminMongoDBDocker

By the end you can

  • Write SELECT queries with filtering, grouping and every kind of JOIN
  • Design a normalised schema with the right keys and constraints
  • Choose data types deliberately instead of defaulting everything to text
  • Use transactions so a partial failure cannot corrupt your data
  • Read an EXPLAIN plan and work out why a query is slow
  • Add indexes that help, and recognise the ones that cost more than they save
  • Connect a database to application code without opening a SQL injection hole
  • Judge when a document database is right, and when it is fashion
Syllabus

11 modules, 52 lessons

Work through these in order. Each lesson is short enough to finish in one sitting and ends with something to build, not a quiz.

  1. 1

    Why Databases Exist

    What breaks when you keep data in files, and what a database gives you instead.

    1. What a database actually isComing soon
    2. Why not just use files or a spreadsheetComing soon
    3. The relational model in ten minutesComing soon
    4. PostgreSQL, MySQL, SQLite: which and whyComing soon
    5. Installing PostgreSQL and connecting to itComing soon
  2. 2

    Your First Queries

    Getting data back out, which is most of what you will ever do.

    1. SELECT: asking for columnsComing soon
    2. WHERE: filtering rowsComing soon
    3. ORDER BY, LIMIT and paging through resultsComing soon
    4. NULL, and why it is neither zero nor emptyComing soon
    5. Pattern matching, ranges and INComing soon
  3. 3

    Grouping and Aggregating

    Turning thousands of rows into the one number somebody asked for.

    1. COUNT, SUM, AVG, MIN and MAXComing soon
    2. GROUP BY: the concept that trips everyone upComing soon
    3. HAVING versus WHEREComing soon
    4. DISTINCT, and counting things correctlyComing soon
  4. 4

    Designing Tables

    Types, keys and constraints — the decisions that are expensive to change later.

    1. CREATE TABLE and choosing data typesComing soon
    2. Primary keys, and why not to use a business valueComing soon
    3. NOT NULL, UNIQUE, CHECK and DEFAULTComing soon
    4. Text, numbers, money and dates without regretComing soon
    5. ALTER TABLE, and changing a live schema safelyComing soon
  5. 5

    Relationships and Joins

    How separate tables become one answer.

    1. Foreign keys and referential integrityComing soon
    2. INNER JOINComing soon
    3. LEFT, RIGHT and FULL joinsComing soon
    4. Many-to-many, and the join tableComing soon
    5. Subqueries and self joinsComing soon
  6. 6

    Changing Data Safely

    Writes, and how not to lose data while doing them.

    1. INSERT, and inserting many rows at onceComing soon
    2. UPDATE and DELETE, and the WHERE you must not forgetComing soon
    3. Transactions: all or nothingComing soon
    4. Concurrency: what happens when two people write at onceComing soon
    5. Upserts with ON CONFLICTComing soon
  7. 7

    Making It Fast

    Why a query is slow, and what actually fixes it rather than what feels like it should.

    1. How the database decides to run your queryComing soon
    2. Reading EXPLAIN and EXPLAIN ANALYZEComing soon
    3. Indexes: what they are and what they costComing soon
    4. Choosing the right index, and the ones never usedComing soon
    5. The N+1 problem, the most common performance bug there isComing soon
  8. 8

    Schema Design in Practice

    Normalisation as a tool you reach for, not a rule you recite.

    1. Normalisation explained without the jargonComing soon
    2. When to denormalise on purposeComing soon
    3. Modelling a real domain from start to finishComing soon
    4. Migrations: changing a schema without downtimeComing soon
    5. Schema mistakes that are expensive to undoComing soon
  9. 9

    Databases from Application Code

    Connecting a real program to a real database, safely.

    1. Connecting from Python and from NodeComing soon
    2. SQL injection, and the one habit that prevents itComing soon
    3. Connection pooling, and why it matters sooner than you thinkComing soon
    4. ORMs: what they do for you, and what they hideComing soon
    5. Seed data and throwaway test databasesComing soon
  10. 10

    MongoDB: When Documents Beat Tables

    The honest case for a document database, and the more common case against.

    1. The document model, and how it really differsComing soon
    2. Querying MongoDBComing soon
    3. Where documents genuinely winComing soon
    4. Where reaching for MongoDB is a mistakeComing soon
  11. 11

    Capstone: Design a Real Schema

    Design, build and query the database behind a working application.

    1. The brief: a library lending systemComing soon
    2. Designing the schemaComing soon
    3. Building it and loading real dataComing soon
    4. Writing the queries the application needsComing soon
Previous courseWeb UI Development FoundationNext courseTypeScript Foundation

Learning this with a mentor is faster

Our internship programme is free and fully remote. You get this curriculum, real project work, and code review from working developers.

Apply for the internshipAll courses