The brief, and the data it has to hold

Thirteen modules of theory and practice come together here. Over the next five lessons you will design, build, query and cache the data layer of a real application — a cinema ticket-booking system — using two databases on purpose, because that is what real systems do. This lesson is the brief: the problem, the data it must hold, and the hard requirements that will drive every decision.

Read it the way you would read a brief from a client, then, before the next lesson, sketch your own schema. Comparing yours to the one we build is where the learning is.

The client and the problem

"Pune Cinemas" runs three screens and wants to sell tickets online. Right now it is a phone-and-paper operation and they are double-selling seats. They need a system where:

customers browse movies and their showtimes, and see how many seats are left;

a customer picks specific seats for a show and books them;

the same seat can never be sold twice for the same show — this is the whole point, the thing that is currently going wrong;

while a customer is choosing seats and paying, those seats are held for a few minutes so nobody else grabs them — but if they abandon checkout, the seats free up automatically;

they can see revenue per movie and how full each show is;

customers can search for a movie by title;

the seat-picking page is hit constantly and must be fast, even for a popular show.

Everything you need is in there. The engineering is in reading it precisely.

Reading the brief for what it demands

Go through it as module 13 taught — access patterns and hard requirements first.

The non-negotiable: no double-booking. "The same seat can never be sold twice for the same show." This is a correctness requirement, and correctness under concurrency is exactly what ACID and a UNIQUE constraint provide (modules 4 and 6). Two customers hitting "book seat A3" at the same millisecond must resolve to one success and one clean failure — never two bookings, never a half-booking. This single requirement means the source of truth must be a relational database with real constraints and transactions. It rules out "just use MongoDB" before we start.

Money is involved. Bookings have amounts; revenue is reported. Money needs ACID and integer minor units, never float (module 8's expensive mistake #1).

Relationships everywhere. Movies have shows; shows are on screens; screens have seats; bookings belong to customers and cover seats. This is a relational graph of entities that must stay consistent — foreign keys, not scattered documents.

A transient hold that expires on its own. "Held for a few minutes… free up automatically." This is not a booking — it is temporary, per-seat, and self-expiring. Storing it in PostgreSQL would mean a cleanup job deleting abandoned holds; a Redis key with a TTL does it for free (module 11). This is the requirement that makes the system genuinely polyglot.

"Hit constantly and must be fast." The seat-availability read happens on every page load for a popular show. A cache (module 11) in front of the availability query is the answer — with invalidation on every booking (module 11's delete-on-write).

Search by title. PostgreSQL full-text search (module 12) — no separate search engine needed at this scale.

The architecture that follows

The brief has, without naming them, specified a two-tier polyglot design (module 13):

PostgreSQL — the source of truth
  movies, screens, seats, shows, customers, bookings, booking_seats
  ACID transactions; the UNIQUE constraint that makes double-booking impossible; revenue; search

Redis — the fast, disposable layer
  seat holds (per-seat keys with a short TTL, auto-expiring)
  availability cache (per-show, short TTL, invalidated on booking)
  rate limiting (booking attempts per customer)

Apply the source-of-truth test from module 13: if Redis vanished, what happens? Some in-progress seat holds would be lost (customers re-pick) and the availability cache would be cold (rebuilt from PostgreSQL) — the app gets slower, not wrong. No booking, no payment, no seat assignment is lost, because all of that lives in PostgreSQL. That is exactly the property a healthy polyglot system has, and it is why the holds and cache belong in Redis and the bookings do not.

What each of the remaining lessons does

Design — the schema, and defending every choice: why booking_seats has that UNIQUE, why money is int paise, why seats are rows and not a count.
Build — create it, load realistic data, and write the booking as one atomic transaction.
Queries — the reads the application needs: availability, the showtime listing without an N+1, search, and revenue.
Cache — add the Redis layer: seat holds with TTL, the availability cache with invalidation, and a rate limiter — proving each helped.

Your task before the next lesson

Do not skip this — it is the exercise that matters most. Sketch the schema yourself. List the tables, their columns and types, the foreign keys, and — hardest — decide where and how you would enforce "no double-booking". Write down which database holds what. Then read the design lesson and compare. The gaps between your sketch and the built version are precisely the things this course was teaching, and you will learn more from one honest attempt than from reading the answer.

Check your work

The one non-negotiable requirement. No seat sold twice for the same show — a correctness requirement under concurrency.

What that requirement forces. A relational source of truth with ACID and a UNIQUE constraint; it rules out a document store as the primary.

Why money forces relational too. ACID for amounts and revenue, stored as integer paise, never float.

What the "held for a few minutes, frees up automatically" requirement points at. A Redis key with a TTL — self-expiring, transient, not a booking.

What makes the seat-availability read need a cache. It is hit on every page load for a popular show and must be fast.

Where search belongs at this scale. PostgreSQL full-text — no separate engine.

The two-tier architecture. PostgreSQL owns the truth (movies…bookings); Redis holds the disposable layer (holds, availability cache, rate limits).

The source-of-truth test applied. If Redis vanished, the app is slower (re-pick holds, cold cache), not wrong (no booking lost).

Practice

Re-read the brief and underline every hard requirement. Separate correctness requirements from performance ones.
List the entities (the nouns) and, for each, decide whether it is a thing or a fact about a thing.
Write the main access patterns — the queries and writes the application performs.
Decide, for each piece of data, whether it belongs in PostgreSQL or Redis, and why.
Write down exactly where and how you would prevent double-booking. Be specific about the mechanism.
Apply the source-of-truth test: list what is lost if Redis disappears, and confirm it is "slower, not wrong".
Sketch the full schema — tables, columns, types, foreign keys, constraints — before reading the next lesson.

Official documentation

PostgreSQL — Constraints — The UNIQUE and foreign-key constraints the correctness requirement needs.
PostgreSQL — Transactions — The atomicity the booking requires.
Redis — EXPIRE — The self-expiring hold.
Martin Fowler — Polyglot persistence — The two-tier design this brief implies.

Next: designing the schema, and defending every choice.