RizTech Academy logo
RizTech Academy

Database Foundation

SQL, documents, caches and the rest — how to choose a database, and how to use the ones you chose.

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 starts by laying out the whole landscape — relational, document, key-value, wide-column, graph, search and time-series — with a named example of each and the shape of problem it exists for. Then it goes deep, one family at a time.

The bulk is SQL on PostgreSQL, because that is what most applications should start with and what most interviews ask about: queries, joins, schema design, indexes, isolation levels and the concurrency bugs that only appear under load. Then MongoDB in real depth, then Redis in real depth, because a production web application usually has two databases and almost nobody teaches the second one.

A whole module is about the decision itself — twelve scenarios worked through, with the advantages and the disadvantages of each option stated honestly rather than a fashion recommended.

You finish able to design a schema, explain why a slow query is slow, use a cache without creating a correctness bug, and justify the database you picked to somebody who disagrees.

Who this is for

  • Anyone heading into backend or full-stack work
  • Developers who can write basic SQL but have never designed a schema
  • Anyone who has been told to "just use MongoDB" and wants to know whether that was right
  • 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 pgAdminMongoDBRedisDocker

By the end you can

  • Name the seven database families and the problem each one exists for
  • 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, and pick an isolation level knowing what it allows
  • Recognise and fix a lost update, a deadlock and an N+1
  • Read an EXPLAIN plan and explain why a query is slow
  • Explain what a B-tree index is, and what it costs on every write
  • Model, query, aggregate and index documents in MongoDB
  • Use Redis data structures for caching, sessions, rate limiting and leaderboards
  • Say when a graph, search, wide-column or time-series database is the right answer
  • Choose a database for a given scenario and defend the choice, including its costs
  • Run more than one database in one application and keep them in step
Reference code

Stuck? Compare your work

cinema-booking — the Pune Cinemas ticket-booking data layer the capstone builds: PostgreSQL as the source of truth (movies, shows, seats, bookings, with the UNIQUE (show_id, seat_id) constraint that makes double-booking impossible, atomically, under concurrency) plus Redis for the transient work (seat holds with a TTL, an availability cache with delete-on-write, and an atomic Lua rate limiter). Verified end to end against real PostgreSQL 16 + Redis 7 — a one-command script runs 24 checks, including the double-booking guarantee both sequentially and in a true concurrent race.

It is there for when you are stuck, not to start from. Type the code yourself first — copying it will teach you very little.

View on GitHub
Syllabus

14 modules, 77 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

    Databases, and the Shapes They Come In

    What a database gives you that a file does not — and the seven families that exist, with what each one is actually for.

    1. What a database actually is15 min
    2. Why not just use files or a spreadsheet20 min
    3. The seven families, and the problem each one solves40 min
    4. The relational model in ten minutes20 min
    5. ACID, BASE and CAP: the promises a database makes35 min
    6. PostgreSQL, MySQL, SQLite — and why to start here25 min
    7. Installing PostgreSQL and connecting to it25 min
  2. 2

    Your First Queries

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

    1. SELECT: asking for columns20 min
    2. WHERE: filtering rows25 min
    3. ORDER BY, LIMIT and paging through results20 min
    4. NULL, and why it is neither zero nor empty25 min
    5. Pattern matching, ranges and IN20 min
  3. 3

    Grouping and Aggregating

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

    1. COUNT, SUM, AVG, MIN and MAX20 min
    2. GROUP BY: the concept that trips everyone up30 min
    3. HAVING versus WHERE20 min
    4. DISTINCT, and counting things correctly20 min
  4. 4

    Designing Tables

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

    1. CREATE TABLE and choosing data types25 min
    2. Primary keys, and why not to use a business value25 min
    3. NOT NULL, UNIQUE, CHECK and DEFAULT25 min
    4. Text, numbers, money and dates without regret30 min
    5. ALTER TABLE, and changing a live schema safely25 min
  5. 5

    Relationships and Joins

    How separate tables become one answer.

    1. Foreign keys and referential integrity25 min
    2. INNER JOIN25 min
    3. LEFT, RIGHT and FULL joins30 min
    4. Many-to-many, and the join table30 min
    5. Subqueries and self joins30 min
  6. 6

    Writes, Transactions and Concurrency

    Writing data, and what happens when two people write at the same moment.

    1. INSERT, and inserting many rows at once20 min
    2. UPDATE and DELETE, and the WHERE you must not forget25 min
    3. Transactions: all or nothing30 min
    4. Isolation levels, and the anomalies each one allows40 min
    5. The lost update, demonstrated and then fixed35 min
    6. Locks, deadlocks, and reading a deadlock report35 min
    7. Upserts with ON CONFLICT20 min
  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 query25 min
    2. Reading EXPLAIN and EXPLAIN ANALYZE30 min
    3. What an index actually is: B-trees, hashes and GIN40 min
    4. Adding indexes, and what they cost on every write30 min
    5. Choosing the right index, and the ones never used30 min
    6. The N+1 problem, the most common performance bug there is25 min
  8. 8

    Schema Design in Practice

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

    1. Normalisation explained without the jargon30 min
    2. When to denormalise on purpose25 min
    3. Modelling a real domain from start to finish40 min
    4. Migrations: changing a schema without downtime30 min
    5. Schema mistakes that are expensive to undo25 min
  9. 9

    Databases from Application Code

    Connecting a real program to a real database, safely.

    1. Connecting from Python and from Node25 min
    2. SQL injection, and the one habit that prevents it30 min
    3. Connection pooling, and why it matters sooner than you think25 min
    4. ORMs: what they do for you, and what they hide30 min
    5. Seed data and throwaway test databases20 min
  10. 10

    Document Databases in Depth

    MongoDB properly: modelling, querying, aggregating and indexing — and the decisions that differ from SQL.

    1. The document model, and how it really differs30 min
    2. Embed or reference: the modelling decision40 min
    3. Querying: find, filters and projections35 min
    4. The aggregation pipeline40 min
    5. Indexes, and why the same rules apply30 min
    6. Transactions, write concerns and what you give up35 min
    7. Where documents genuinely win, and where it is fashion30 min
  11. 11

    Key-Value Stores and Caching

    Redis: the second database almost every real web application has, and the data structures that make it useful.

    1. Why a cache, and what it costs you30 min
    2. Strings, hashes, lists, sets and sorted sets40 min
    3. Cache-aside, write-through, and cache invalidation35 min
    4. Sessions, rate limiting and leaderboards35 min
    5. TTLs, eviction policies and running out of memory30 min
    6. Persistence, and what a cache must never be25 min
  12. 12

    The Other Families

    Wide-column, graph, search, time-series and vector — what each is for, and how you would recognise the need.

    1. Wide-column stores: Cassandra and the write-heavy case35 min
    2. Graph databases: when the relationships are the data35 min
    3. Search engines: Elasticsearch, and why LIKE is not search35 min
    4. Time-series databases: metrics, and why they are different30 min
    5. Vector databases and embeddings30 min
  13. 13

    Choosing, and Using More Than One

    The decision itself: scenario by scenario, with the advantages and the disadvantages stated honestly.

    1. How to actually choose, and the questions that decide it40 min
    2. Twelve real scenarios, worked through45 min
    3. The honest trade-off table35 min
    4. Using more than one, and keeping them in step40 min
    5. What it costs to migrate later30 min
  14. 14

    Capstone: Design a Real Data Layer

    Design, build and query the data layer behind a working application — using more than one database, because real ones do.

    1. The brief, and the data it has to hold25 min
    2. Designing the schema, and justifying every choice45 min
    3. Building it and loading real data45 min
    4. Writing the queries the application needs45 min
    5. Adding the cache, and proving it helped40 min
Previous courseWeb UI Development FoundationNext courseTypeScript Foundation

Learning this with a mentor is faster

Going through a course like this one is how you qualify for our six-month internship, for selected students, mostly from Pune. Clear the interview and you build real-world projects with my mentorship.

About the internshipAll courses