API Design for Complex Schemas: Trees, Polymorphism, Bookings

Map self-referencing trees, polymorphic links, computed fields and history to an API, then design every endpoint of a thirteen-table ticket-booking schema.

Mapping a complex database schema to an API: common questions

How do you return a tree from a self-referencing table?

Send a flat list with parent_id for large trees, a depth-limited nested tree for menus, or a single node with its path for detail pages. Read it with a recursive query.

What is an aggregate resource?

One API resource built from several tables that are read and written together, such as a booking with its items, tickets and payment. It is created in one transaction and returned as one nested object.

Related chapters

Keep learning

Overview

Ready to draw a menu. Cap the depth (and the size): a tree 10 levels deep is a slow query and a huge response. The server reads it with one recursive query (WITH RECURSIVE) or a stored path column. For a detail page: the breadcrumb “Music › Concerts › Rock” comes ready, children are counted, not sent. Lab: one categories table points at itself. Three ways to send it Flat list with parent_id Nested tree One node with its path Gold, Silver The price of each section for each event. One active hold per (event, seat), enforced by a unique index. Unique (event_id, seat_id): a seat is sold once. venues 1 ──< sections 1 ──< seats venues 1 ──< events events N >──< N sections via event_sections users 1 ──< holds 1 ──< hold_seats >── seats users 1 ──< bookings 1 ──< booking_items >── seats bookings 1 ──< payments 1 ──< refunds booking_items 1 ── 1 tickets events (by public_id) ⋈ venues ⋈ event_sections → min / max price 1 query with 2 joins and a GROUP BY Cache for minutes at the CDN: it changes rarely. The seat map is a link, not embedded: it is big and changes every second. seats ⋈ sections (layout) ⋈ event_sections (price) LEFT JOIN booking_items → sold LEFT JOIN hold_seats ⋈ holds (not expired) → held 1 heavy query over 6 tables (or 1 read from a precomputed view) Seconds only. For a big on-sale, keep a precomputed seat-state view (or cache) per event, updated on every hold and booking. Three tables (seats, booking_items, hold_seats) collapse into one word per seat: free, held, sold. Seat IDs are the human labels (A1), not seat bigints. check seats not sold / not held INSERT holds INSERT hold_seats (unique per event + seat) 3–4 statements in one transaction Never cache writes. hold_seats is a join table: the API shows a hold with a seats array. lock the hold, check not expired INSERT bookings (idempotency_key unique) INSERT booking_items (prices from event_sections) DELETE hold + hold_seats INSERT payments (pending) 6 statements in ONE transaction One API call, six tables, one transaction: either everything is written or nothing. The same Idempotency-Key returns the same booking instead of a second one (the patterns chapter). Prices are copied into booking_items, so a later price change never changes an old booking. ⋈ events ⋈ booking_items ⋈ seats ⋈ sections ⋈ tickets ⋈ payments (latest) 2–3 queries (booking + its items in one, payment in another) Private: no shared cache. ETag for repeat views. A booking is an aggregate: one resource built from seven tables. Ownership check in the WHERE clause: another user’s booking ID returns 404 (BOLA, the OWASP chapter). provider_ref and internal statuses stay inside. ⋈ booking_items ⋈ tickets ⋈ events (starts_at > now) ⋈ venues ⋈ seats ORDER BY starts_at, cursor page 1 query, 5 joins, cursor-paged A screen-shaped resource: the wallet needs tickets, so the API serves tickets, even though no single table holds this view. Cursor paging on (starts_at, ticket id), as in the patterns chapter. check booking is yours + refundable INSERT refunds UPDATE tickets → void then call the payment provider (async) 4 statements in a transaction, then an async job 202, not 201: the money moves later, through the provider (the async chapter). Seats become free again because the seat map no longer finds a live booking item for them. Lab: pick an endpoint and see which tables it touches tables touched database work needs a transaction

Mapping a complex schema: trees, polymorphic links and a full booking system

The last chapter mapped one table, then one-to-one, one-to-many and many-to-many. Real schemas add tables that point at themselves, links that can point at different tables, numbers that are computed rather than stored, and history. Then they combine everything. This chapter takes each of those in turn, and finishes with a thirteen-table ticket-booking schema and every endpoint mapped onto it.

Advanced: a table that points at itself

Categories have sub-categories, comments have replies, employees have managers. One table holds them all, with a parent_id that points back into the same table. The API must decide how much of the tree to send at once:

Advanced: one link, several possible tables

Reviews can be written about an event or a venue. There are two common ways to store that: Type + ID columns (polymorphic) One nullable FK per kind reviews(event_id NULL, venue_id NULL) plus a check that exactly one is set. Real foreign keys, but a new column for each new kind. Sound was perfect Whichever way the table goes, the API looks the same: reviews are a sub-collection of each thing that can be reviewed, and each review says what it is about with a typed subject. The storage choice can change later without breaking anyone.

Advanced: numbers that no column holds

Compute on read always right Keep a counter column Cache or read model A small precomputed table or cache per event, refreshed on every change. The standard answer for a flash sale. The API field is the same in all three: "seats_left": 12. That is the point of the mapping layer: the client gets a ready-to-use number (the payload chapter’s rule), and the server is free to change how it is produced as traffic grows.

Very complex: a full ticket-booking schema

Now put it all together. Venues have sections, sections have seats. Each event sets a price per section. A buyer holds seats for ten minutes, books them, pays, and gets one ticket per seat. Bookings can be refunded. Thirteen tables: Thirteen tables of a ticket-booking system venues → sections → seats, venues → events, bookings → items, bookings → payments → refunds. events ↔ sections (event_sections, with a price), holds ↔ seats (hold_seats), bookings ↔ seats (booking_items). booking_items → tickets: each sold seat gets exactly one ticket. Rules in the schema Unique (event, seat) in booking_items and hold_seats: the database itself stops double booking.

Every endpoint, mapped onto the tables

The API does not have thirteen resources. It has the ones buyers think in: events, seat maps, holds, bookings, tickets and refunds. Pick an endpoint to see which tables light up, what runs in the database, and what the caller gets back:

Which tables become resources?

In the API events, venues Resources with their own IDs Things people search for, link to and share. A resource built from 7 tables (an aggregate) One thing to the buyer, many rows to the database. Read and written as one unit. A short-lived resource It has an ID, an expiry and a state the client must track. A resource, and the /me/tickets collection Folded into booking.items Only meaningful inside its booking. hold_seats, event_sections Join tables: their columns become fields on the relationship. sections, seats Labels inside the seat map ("Gold A3") Buyers pick seats, they do not browse a seats table. payments, refunds provider_ref and raw provider statuses stay inside.

Traps when mapping a big schema

One endpoint per table Thirteen CRUD endpoints make every client join tables over the network, and every schema change breaks them. N+1 on the server Loading 20 bookings and then their items one by one. Use joins or one IN (…) query per level. Leaking keys and joins Bigint IDs, join tables and status codes in responses lock the schema forever. Writes across tables without a transaction A crash between INSERT bookings and DELETE hold leaves a seat booked AND held. One API call, one transaction. Mass assignment Deep nesting

In the interview

The rest of the API path builds on this: pagination and idempotency for the list and booking endpoints, and the OWASP chapter for the ownership checks.

More courses

Jobs, linked to lessons — Browse jobs where every requirement links to the chapter that teaches it. See how ready you are, how long the gap takes to close, and which listings look stale.
Match a job post to courses — Paste a job post. Each skill is matched to a track here and put in learning order, foundations first.
React from open source with patterns — Patterns extracted from Material UI, Radix, React Aria, React Hook Form, Redux Toolkit, TanStack, and Next.js — not slides about hooks.
Database in depth — How a query becomes work. Indexes, isolation, locks, and the plans that explain why a page is slow — not an ORM tour.
Build a real API with NestJS — One ticket-booking API, built in order: a plain Node server first, then NestJS modules, validation, a real database, and the features real clients lean on.
Microservices — Microservice patterns one diagram at a time: how to split a monolith, how services talk, and how data stays consistent without distributed transactions.
Kafka — A beginner-to-senior path through event logs, reliable delivery, production operations, and disaster recovery.
AI engineering for software engineers — Eight free reads, in order, for software engineers moving into AI: when to build an agent, how to build one, how to test it, and how the model works underneath.
Frontend system design — How a UI is sliced when it is a product, not a page: ownership of state, cache, routes, and what happens when the network lies.
TypeScript in depth — The type system as a design tool. Inference, narrowing, generics, and the errors that mean the model is wrong — not a catalog of utility types.
CSS in depth — See the box, then the page. Visual chapters from the box model through Flexbox surprises, Grid, selectors, and units, then backgrounds, shapes, and visual effects, each with a playground.
Accessibility — Foundations first (WCAG in plain words, semantic HTML, lists, alt text), then hands-on HTML labs, a full pass of WAI-ARIA 1.3, and a final fix-the-page challenge.
Docker — What an image is, why layers cache, and how containers find each other on a network.
Redis — Fifteen Redis patterns, one picture each: cache, session, lock, counter, rate limiter, queue, and when Redis is the wrong tool.
Kubernetes — Kubernetes from zero to operators: why it exists, Pods, Deployments and Services, then configuration, networking, storage, scheduling, scaling and security.