Skip to content
notes.vinhnq.com

Search

System DesignMid23 min read

Requirements analysis and data design through a hotel booking example

Review 15 cards
On this page

Backend practice series: 1. Requirements and data design · 2. APIs and overbooking · 3. Interview review · 4. Recursion and sorting · 5. SQL exercises · 6. SQL on the E-Learning database

The instructor’s worked example is a brief of only a few lines: “users search for hotels by location, number of guests and dates; view a hotel’s details and its room types; then make a booking” (a tiny Booking.com). This post turns that vague brief into a verifiable spec and a data design, then applies the same method to the E-Learning pet project. The API list, the overbooking problem, the implementation plan and the testing strategy are in part 2; the exercise walkthroughs and interview review are in part 3, part 4 and part 5.

Note: the hotel system is not the course’s pet project. The pet project learners build themselves is the online-course (E-Learning) platform: the “Hello course online project” homework, the schema in practice session 2 and the endpoints all revolve around it. The hotel example teaches the method; section 3 applies it to E-Learning.

1. Requirements analysis: turning vague words into a spec

Key idea: don’t open the IDE yet. List what’s unclear, fix the scope, write BDD acceptance criteria and put numbers on the NFRs, so the requirements can be reviewed and turned straight into tests.

1.1 The big picture

Key idea: two clients (web and mobile) call the same Spring Boot backend, which stores its data in MySQL.

flowchart LR
  Web[Web frontend] --> API[Backend<br/>Spring Boot]
  Mobile[Mobile app] --> API
  API --> DB[(MySQL)]

Three core features:

  1. Search hotels by location, guests per room, check-in date and check-out date.
  2. View a hotel, including its room types.
  3. Book a room.

1.2 What does a senior do with a brief like this?

Key idea: list what’s unclear and ask (the product owner, a BA). For a pet project, make the call yourself and write the assumption down.

The areas I’d ask about, each of which changes the schema or the flows:

  • Search: what “location” means, whether “guests” is per room or per party, sorting and pagination.
  • Pricing: whether prices change by date, currency, taxes and fees.
  • Booking: online payment, how long a room is held, whether login is required, cancellations and refunds.
  • Scale and time: how many hotels, how many req/s, which time zone dates are in.

The most important question, and the one most often skipped: “What happens when two people try to book the last room at the same time?” The answer shapes the whole data design later on.

Open questions and the assumptions I chose for the pet project
Area Question Assumption I chose for the pet project
Search Is “location” a city, a district, or coordinates plus a radius? City (exact, case-insensitive match). Radius search comes later.
Search Is “guests” per room or for the whole party? Can you book several rooms? sleeps = max guests per room; one booking can reserve several rooms of the same type.
Search How are results sorted? Paginated? By lowest price ascending by default; 20 results per page.
Pricing Does the price change by date (weekends, peak season)? Yes, prices are stored per night.
Pricing Currency? Taxes and fees? One currency (VND); a flat percentage service fee; coupons give a percentage or a fixed amount off.
Booking Online payment? How long do we hold a room before payment? Phase 1: pay at the hotel or a simulated payment; a PENDING booking expires after 15 minutes if not confirmed.
Booking Do guests need to log in? Yes; the contact details can differ from the account holder (booking for someone else).
Booking Cancellations? Refunds? Free cancellation up to 24 hours before check-in, in phase 2.
Scale How many hotels, how many searches per second? 10,000 hotels, 50 search req/s at peak, 5 bookings/s.
Time Which time zone are check-in dates in? The hotel’s local date, as LocalDate, ISO format yyyy-MM-dd.

1.3 Scope

Key idea: write down both what’s in and what’s out of scope. The out-of-scope list prevents scope creep (requirements that keep growing) and is what you point to when someone asks “why don’t we have X yet?”.

  • In scope: search, hotel details, booking, coupons, hotel reviews, basic hotel data management (admin).
  • Out of scope for phase 1: real payments, cancellations and refunds, multiple currencies, map search, personalised recommendations.

1.4 User stories and acceptance criteria (BDD)

Key idea: write acceptance criteria as Given / When / Then (BDD: describing behaviour through concrete scenarios) so requirements are verifiable and later turn straight into tests. Each story covers the happy path, edge cases and errors.

Story 3: Book a room matters most, because it has concurrent bookings and retried requests:

Scenario: Successful booking
  Given Deluxe has at least 2 rooms free every night from 2026-12-01 to 2026-12-03
  When I POST /api/v1/bookings for 2 Deluxe rooms for those 2 nights
  Then the response is 201 with a booking id, status PENDING and a total including fees and discounts
  And the free rooms for each night drop by 2

Scenario: Not enough rooms
  Given Deluxe has only 1 room left on 2026-12-02
  When I book 2 rooms
  Then the response is 409 and no night's inventory is changed

Scenario: Two guests race for the last room
  Given Deluxe has exactly 1 room left
  When two requests for 1 room arrive at the same time
  Then exactly one gets 201 and the other gets 409

Scenario: The same request is resent (flaky network)
  Given I booked successfully with Idempotency-Key "abc"
  When I send the same request again with the same key
  Then I get the original booking back, and no second booking is created

Scenario: Coupon is used up or not applicable
  When I book with a coupon that has no uses left
  Then the response is 422 and no booking is created
Story 1 (search hotels) and Story 2 (view a hotel) in Gherkin

Story 1: Search hotels. As a guest, I want to find hotels with availability in a city for specific dates, so I can choose where to stay.

Scenario: Hotels with availability are found
  Given hotel "Lotus" in Hanoi has a Deluxe room type (sleeps = 2) with 3 rooms free every night from 2026-12-01 to 2026-12-03
  When I call GET /api/v1/hotels?city=hanoi&sleeps=2&checkin=2026-12-01&checkout=2026-12-03
  Then the 200 response contains "Lotus" with the lowest nightly price among matching room types

Scenario: A single sold-out night hides the hotel
  Given Lotus's Deluxe rooms are sold out on 2026-12-02
  When I search from 2026-12-01 to 2026-12-03
  Then "Lotus" is not listed (unless another matching room type is available)

Scenario: Invalid dates
  When I search with checkout <= checkin, a check-in in the past, or a stay longer than 30 nights
  Then the response is 400 with per-field error details

Story 2: View a hotel.

Scenario: The hotel exists
  When I call GET /api/v1/hotels/42
  Then the 200 response has the name, description, amenities, photos, address, rating and room types

Scenario: The hotel doesn't exist or is inactive
  When I call GET /api/v1/hotels/999
  Then the response is 404

1.5 Non-functional requirements: make them measurable

Key idea: “the system must be fast and reliable” is meaningless in a review. Each NFR (a quality requirement: speed, reliability, security…) needs a number and a way to check it.

Category Requirement How we verify it
Performance Search API p95 < 300 ms at 50 req/s with 10,000 hotels Load test (k6/Gatling) on seeded data
Performance Booking API p95 < 500 ms Load test
Correctness Never overbook: booked rooms per night ≤ total rooms Concurrency test + DB constraint
Correctness Booking is idempotent per Idempotency-Key for 24 h Integration test
Availability 99.9% per month (~43 minutes of downtime) Uptime monitoring
Security Only the booking owner (or an admin) can view a booking; contact details are never logged in plain text Authorisation tests, log review
Data A booking’s price is a snapshot from booking time and doesn’t change when the hotel changes its rates Unit + integration tests
Operations Every request has a trace id; metrics for successful and failed bookings Dashboard

p95 means 95% of requests are faster than the threshold. Idempotent means sending the same request several times still produces only one result.

2. Data design

Key idea: pull the nouns out of the requirements as entities, then review what will break in production. The biggest changes: inventory and prices per night, and bookings with a status and a price snapshot.

2.1 Entities and relationships

Key idea: seven entities: Hotel, Location, RoomType, User, Booking, Coupon, Review. Booking is the centre, linking a user to a room type.

  • Hotel: name, description, amenities, star rating/score, photos, status.
  • Location: street, city, province, country, postal code, nearby landmarks. One-to-one with Hotel.
  • RoomType: name, description, photos, amenities, max guests (sleeps), price. Hotel 1–N RoomType.
  • User: name, email, phone, country.
  • Booking: who booked, hotel, room type, check-in/check-out dates, nights, number of rooms, nightly price at booking time, original price, fee, total discount, final price, payment method, contact details. User 1–N Booking.
  • Coupon: type (percentage or fixed amount), value, conditions, remaining quantity. Booking N–M Coupon through a booking_coupon junction table.
  • Review: author, hotel, room type, score, comment. Hotel 1–N Review, User 1–N Review.

The course’s original design already makes some good decisions worth highlighting:

  • The booking stores the nightly price and each price component, instead of joining to the room type’s current price.
  • Contact details are separate from the user, so you can book on behalf of someone else.
  • Hotel has a version column plus audit columns (created_at/by, updated_at/by).

2.2 Design review: what breaks in production?

Key idea: this is the part a senior adds. I’d propose five changes; the most important replaces the available_rooms column with a per-night inventory table.

(2) Bookings have no status. You need a status: PENDING → CONFIRMED → CHECKED_IN → COMPLETED, or CANCELLED/EXPIRED. Without it you can’t expire held rooms, cancel, or enforce “only guests who stayed can review”.

(4) Reviews should be tied to a booking. Add a unique booking_id so each stay gets at most one review and fake reviews are blocked.

2.3 ERD

Key idea: the ERD after the five changes: ROOM_INVENTORY, HOTEL_IMAGE and BOOKING_COUPON are added, and Review points to Booking.

erDiagram
  HOTEL ||--|| LOCATION : "located at"
  HOTEL ||--o{ ROOM_TYPE : "has"
  ROOM_TYPE ||--o{ ROOM_INVENTORY : "per night"
  HOTEL ||--o{ HOTEL_IMAGE : "has"
  USER ||--o{ BOOKING : "makes"
  HOTEL ||--o{ BOOKING : "receives"
  ROOM_TYPE ||--o{ BOOKING : "is booked in"
  BOOKING ||--o{ BOOKING_COUPON : "applies"
  COUPON ||--o{ BOOKING_COUPON : "used in"
  USER ||--o{ REVIEW : "writes"
  HOTEL ||--o{ REVIEW : "is reviewed in"
  BOOKING ||--o| REVIEW : "results in"

  HOTEL {
    bigint id PK
    varchar name
    text description
    decimal rating
    varchar status
    int version
    datetime created_at
    varchar created_by
    datetime updated_at
    varchar updated_by
  }
  LOCATION {
    bigint hotel_id PK, FK
    varchar street
    varchar city
    varchar province
    varchar country
    varchar postal_code
    varchar nearby
  }
  ROOM_TYPE {
    bigint id PK
    bigint hotel_id FK
    varchar name
    int sleeps
    decimal base_price
  }
  ROOM_INVENTORY {
    bigint room_type_id PK, FK
    date stay_date PK
    int total_rooms
    int booked_rooms
    decimal price
  }
  USER {
    bigint id PK
    varchar name
    varchar email UK
    varchar phone
    varchar country
  }
  BOOKING {
    bigint id PK
    bigint user_id FK
    bigint hotel_id FK
    bigint room_type_id FK
    date checkin_date
    date checkout_date
    int nights
    int quantity
    decimal original_price
    decimal fee
    decimal discount_total
    decimal final_price
    varchar status
    varchar payment_method
    varchar contact_name
    varchar contact_phone
    varchar contact_email
    varchar idempotency_key UK
    datetime created_at
  }
  COUPON {
    bigint id PK
    varchar code UK
    varchar coupon_type
    decimal discount_value
    decimal min_order
    int remaining
    datetime valid_until
  }
  BOOKING_COUPON {
    bigint booking_id PK, FK
    bigint coupon_id PK, FK
    decimal discount_applied
  }
  REVIEW {
    bigint id PK
    bigint hotel_id FK
    bigint user_id FK
    bigint booking_id FK, UK
    tinyint rating
    text comment
    datetime created_at
  }
  HOTEL_IMAGE {
    bigint id PK
    bigint hotel_id FK
    varchar url
    int sort_order
  }

The booking no longer needs a single “nightly price” column, because the price can differ per night. To show the breakdown, add a booking_night(booking_id, stay_date, price) snapshot table.

2.4 DDL (Flyway migration V1__init.sql, trimmed)

Key idea: push the important rules down into the database as constraints: the composite primary key (room_type_id, stay_date), a CHECK against overbooking, and a unique (user_id, idempotency_key) against duplicate bookings.

Full DDL: hotel, location, room_type, room_inventory, booking
CREATE TABLE hotel (
  id          BIGINT PRIMARY KEY AUTO_INCREMENT,
  name        VARCHAR(200) NOT NULL,
  description TEXT,
  rating      DECIMAL(2,1),
  status      VARCHAR(20)  NOT NULL DEFAULT 'ACTIVE',
  version     INT          NOT NULL DEFAULT 0,
  created_at  DATETIME(6)  NOT NULL,
  created_by  VARCHAR(100),
  updated_at  DATETIME(6),
  updated_by  VARCHAR(100)
);

CREATE TABLE location (
  hotel_id    BIGINT PRIMARY KEY,
  street      VARCHAR(255),
  city        VARCHAR(100) NOT NULL,
  province    VARCHAR(100),
  country     CHAR(2)      NOT NULL,
  postal_code VARCHAR(20),
  nearby      VARCHAR(500),
  CONSTRAINT fk_location_hotel FOREIGN KEY (hotel_id) REFERENCES hotel(id),
  INDEX idx_location_city (city)
);

CREATE TABLE room_type (
  id         BIGINT PRIMARY KEY AUTO_INCREMENT,
  hotel_id   BIGINT       NOT NULL,
  name       VARCHAR(100) NOT NULL,
  sleeps     INT          NOT NULL CHECK (sleeps > 0),
  base_price DECIMAL(12,2) NOT NULL,
  CONSTRAINT fk_room_type_hotel FOREIGN KEY (hotel_id) REFERENCES hotel(id),
  INDEX idx_room_type_hotel_sleeps (hotel_id, sleeps)
);

CREATE TABLE room_inventory (
  room_type_id BIGINT NOT NULL,
  stay_date    DATE   NOT NULL,
  total_rooms  INT    NOT NULL,
  booked_rooms INT    NOT NULL DEFAULT 0,
  price        DECIMAL(12,2) NOT NULL,
  PRIMARY KEY (room_type_id, stay_date),
  CONSTRAINT fk_inventory_room_type FOREIGN KEY (room_type_id) REFERENCES room_type(id),
  CONSTRAINT chk_no_overbooking CHECK (booked_rooms BETWEEN 0 AND total_rooms)
);

CREATE TABLE booking (
  id              BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id         BIGINT NOT NULL,
  hotel_id        BIGINT NOT NULL,
  room_type_id    BIGINT NOT NULL,
  checkin_date    DATE   NOT NULL,
  checkout_date   DATE   NOT NULL,
  nights          INT    NOT NULL,
  quantity        INT    NOT NULL CHECK (quantity > 0),
  original_price  DECIMAL(12,2) NOT NULL,
  fee             DECIMAL(12,2) NOT NULL,
  discount_total  DECIMAL(12,2) NOT NULL DEFAULT 0,
  final_price     DECIMAL(12,2) NOT NULL,
  status          VARCHAR(20)   NOT NULL,
  payment_method  VARCHAR(20)   NOT NULL,
  contact_name    VARCHAR(200)  NOT NULL,
  contact_phone   VARCHAR(30)   NOT NULL,
  contact_email   VARCHAR(200)  NOT NULL,
  idempotency_key VARCHAR(64)   NOT NULL,
  created_at      DATETIME(6)   NOT NULL,
  expires_at      DATETIME(6),
  CONSTRAINT uk_booking_idem UNIQUE (user_id, idempotency_key),
  CONSTRAINT chk_dates CHECK (checkout_date > checkin_date),
  INDEX idx_booking_user (user_id, created_at),
  INDEX idx_booking_status_expires (status, expires_at)
);

3. Applying the method to the E-Learning pet project

Key idea: the method is exactly the same as for the hotel: open questions, scope, BDD, measurable NFRs, and only then design. The easiest place to go wrong is keeping enrolment status in step with per-lesson progress.

The schema the course hands out: learners, teachers and admins in separate tables; course → chapter → lesson; enrolments with a rating/review; per-lesson START/PROCESSING/DONE progress. It is analysed and queried in SQL exercises on the E-Learning database; the endpoint list is in Designing REST APIs — Exercise 2; the step-by-step ERD is in Databases: ERDs, foreign keys, JOINs and GROUP BY.

The main open questions: how many teachers a course has, whether one person can learn and teach, what “lesson completed” means, when enrolment status changes, who can rate, and how progress is recomputed when lessons are added or hidden.

Open questions and assumptions for E-Learning
Topic Question Assumption for the pet project
Teachers Can a course have several instructors? (the conceptual diagram says N–M, the physical schema says 1–N) 1–N: one teacher per course
Accounts Can one person both learn and teach? No; keep the three tables from the course’s schema and log it as design debt
“Lesson completed” How much of a video counts as DONE? Is opening a PDF enough? The learner clicks “Complete”; videos need at least 90% watched
Enrolment status When does it go START → PROCESSING → DONE? First lesson opened → PROCESSING; every ACTIVE lesson DONE → DONE
Ratings Can you rate before finishing? Can you change it? Only enrolled learners can rate; one 1–5 rating per enrolment, editable
Content changes How does the % change when a teacher adds or hides lessons? Computed over ACTIVE lessons; a DONE course is not pulled back
Locked accounts Can an INACTIVE learner still see their courses? They can’t log in; their learning data is kept

BDD for the main flow:

Scenario: A learner enrols in a course
  Given learner "an" is ACTIVE and course "Java Core" is ACTIVE
  When "an" calls POST /api/v1/courses/12/enrollments
  Then the response is 201 and the enrolment has status START

Scenario: Enrolling twice
  Given "an" is already enrolled in course 12
  When "an" calls POST /api/v1/courses/12/enrollments again
  Then the response is 409 and no second enrolment exists

Scenario: Finishing the last lesson completes the course
  Given course 12 has 3 ACTIVE lessons and "an" has 2 of them DONE
  When "an" calls PUT /api/v1/me/courses/12/lessons/7/progress with {"status": "DONE"}
  Then lesson 7's progress is DONE and the enrolment in course 12 becomes DONE

Scenario: A lesson from another course can't be marked
  Given lesson 99 belongs to course 15, which "an" is not enrolled in
  When "an" calls PUT /api/v1/me/courses/12/lessons/99/progress
  Then the response is 404 and no progress record is created

Measurable NFRs: the “my courses” page at p95 < 300 ms for a learner with 50 enrolments; progress updates at p95 < 150 ms; resending the same progress request never creates a duplicate record (idempotent).

Exercises

Exercise 1: Database design for a messaging app

Task (paraphrased): Design the database for a chat app where users can message each other one-to-one or create group chat rooms.

Trick: treat a one-to-one chat as a conversation too, just one with exactly two members. Then you only need one message table.

erDiagram
  USERS ||--o{ CONVERSATION_MEMBER : "joins"
  CONVERSATION ||--o{ CONVERSATION_MEMBER : "has"
  CONVERSATION ||--o{ MESSAGE : "contains"
  USERS ||--o{ MESSAGE : "sends"
  MESSAGE ||--o{ MESSAGE : "replies to"
  USERS {
    bigint id PK
    varchar username UK
    varchar display_name
  }
  CONVERSATION {
    bigint id PK
    varchar type "DIRECT or GROUP"
    varchar title "null for DIRECT"
    varchar direct_key UK "minUserId:maxUserId, DIRECT only"
    bigint created_by FK
    datetime created_at
    bigint last_message_id
  }
  CONVERSATION_MEMBER {
    bigint conversation_id PK, FK
    bigint user_id PK, FK
    varchar role "OWNER, ADMIN, MEMBER"
    datetime joined_at
    bigint last_read_message_id
    boolean muted
  }
  MESSAGE {
    bigint id PK
    bigint conversation_id FK
    bigint sender_id FK
    text content
    varchar content_type "TEXT, IMAGE, FILE"
    bigint reply_to_id FK
    datetime created_at
    datetime edited_at
    datetime deleted_at
  }
Solution: DDL, message and unread queries, and the reasoning
CREATE TABLE conversation (
  id              BIGINT PRIMARY KEY AUTO_INCREMENT,
  type            VARCHAR(10) NOT NULL,
  title           VARCHAR(200),
  direct_key      VARCHAR(50) UNIQUE,      -- e.g. "12:57": prevents two duplicate 1-1 chats
  created_by      BIGINT NOT NULL,
  created_at      DATETIME(6) NOT NULL,
  last_message_id BIGINT
);

CREATE TABLE message (
  id              BIGINT PRIMARY KEY AUTO_INCREMENT,
  conversation_id BIGINT NOT NULL,
  sender_id       BIGINT NOT NULL,
  content         TEXT,
  content_type    VARCHAR(10) NOT NULL DEFAULT 'TEXT',
  reply_to_id     BIGINT,
  created_at      DATETIME(6) NOT NULL,
  edited_at       DATETIME(6),
  deleted_at      DATETIME(6),
  INDEX idx_message_conv_id (conversation_id, id)   -- load the latest messages, keyset pagination
);

-- Load 50 messages before message :beforeId (keyset pagination, no OFFSET)
SELECT id, sender_id, content, created_at
FROM message
WHERE conversation_id = :conv AND id < :beforeId AND deleted_at IS NULL
ORDER BY id DESC
LIMIT 50;

-- Unread count per conversation for the current user
SELECT m.conversation_id, COUNT(*) AS unread
FROM conversation_member cm
JOIN message m ON m.conversation_id = cm.conversation_id
              AND m.id > COALESCE(cm.last_read_message_id, 0)
              AND m.sender_id <> cm.user_id
WHERE cm.user_id = :me
GROUP BY m.conversation_id;

The reasoning behind each decision:

  • Soft delete (deleted_at) lets you show “message was unsent” and supports moderation.
  • At large scale (billions of messages), the message table is usually partitioned or sharded by conversation_id, or moved to a wide-column store such as Cassandra, because access is always per conversation and by time.

Remember

  • List five open questions for the “book a room” brief, and say which one shapes the data design.
  • Write an NFR with a number, a load condition and a way to verify it, and explain why p95 beats the average.
  • Explain why inventory is stored per room type × night, and why a booking stores a price snapshot.
  • Name the constraints that act as safety nets: the overbooking CHECK, the unique idempotency key, the unique direct_key.
  • Explain how to keep E-Learning enrolment status consistent with per-lesson progress.

Interview questions

Junior

Why should a booking store its price instead of joining to the room type’s current price?

Because prices change over time, and a booking is a settled transaction.

The guest pays what was agreed at booking time, and invoices and reconciliation rely on that number. Joining to the current price would make old bookings “change price” whenever the hotel updates its rates.

How do you represent the many-to-many relationship between Booking and Coupon in a relational database?

With a junction table booking_coupon(booking_id, coupon_id) whose composite primary key is made of two foreign keys.

The junction table can also hold data about the relationship itself, such as the discount applied to that booking.

Why do money columns use DECIMAL instead of FLOAT/DOUBLE?

Because FLOAT/DOUBLE are binary floating-point numbers that can’t represent many decimal fractions exactly (0.1 + 0.2 isn’t 0.3).

Errors accumulate across calculations and break reconciliation. DECIMAL(p, s) stores exact decimals with a fixed number of digits. In Java, use BigDecimal with an explicit RoundingMode, or store integers in the smallest unit (cents, dong).

Mid

Why store inventory per room type per night instead of an available_rooms column?

Because availability differs from night to night; a single number can’t answer “is a room free from date A to date B?”.

  • Without it, a booking this month would eat into another month’s rooms.
  • A room_inventory(room_type_id, stay_date) table lets you check and decrement each night with one conditional update, add CHECK (booked_rooms <= total_rooms) as a safety net, and keep the nightly price there too.
  • The costs: you have to pre-generate rows for future dates (a job that creates inventory a few months ahead), and the table is bigger: 10,000 hotels × a few room types × 365 nights is in the tens of millions of rows, which a composite primary key handles fine.
“The system must be fast”: how do you turn a non-functional requirement like that into something verifiable?

Attach a number, a load condition and a way to measure it, for example “search API p95 < 300 ms at 50 req/s with 10,000 hotels, measured with k6 on seeded data”.

Use percentiles rather than averages, because averages hide the slow tail that real users hit. Do the same for availability (99.9% per month), correctness (no overbooking, checked by a concurrency test and a constraint), security and operations. An NFR with no way to measure it can’t be reviewed or signed off.

What would you ask before designing the E-Learning pet project?

The things that change the schema or the flows; where nobody can answer, pick an assumption, write it down and turn it into BDD.

  • One teacher per course or several; whether one person can both learn and teach.
  • What “lesson completed” means for a video or a PDF; when an enrolment moves START → PROCESSING → DONE.
  • Who can rate and whether ratings can be edited; how the progress percentage changes when lessons are added or hidden.

An assumption written as a BDD scenario means a later change breaks a test straight away.

Senior

How do you turn a vague requirement like “users can book a room” into a spec?

Actors and goals → open questions → in/out of scope → Given/When/Then acceptance criteria → measurable NFRs.

  • Open questions: payment, how long rooms are held, cancellation, multiple rooms, time zones, concurrent bookings.
  • Given/When/Then covers the happy path, edge cases and errors.
  • NFRs: p95 latency, load, zero overbooking, availability.
  • Anything that can’t be answered right away becomes a documented assumption with an owner to confirm it.

The result is something reviewable that maps directly to tests.

Design the database for a chat app with both one-to-one and group chats. What changes at billions of messages?

Treat a one-to-one chat as a conversation with exactly two members, so three tables are enough: conversation, conversation_member, message; at very large scale, split by conversation_id.

  • Prevent duplicate one-to-one chats with a unique direct_key (“smaller id:larger id”).
  • Store read state as last_read_message_id on the member row instead of a per-message read table.
  • Load messages with keyset pagination on an (conversation_id, id) index.
  • At scale: partition or shard by conversation_id, since every access goes through one conversation; or move message to a wide-column store like Cassandra with conversation_id plus a time bucket as the partition key so partitions don’t grow forever, and Snowflake-style message ids so messages still sort by time without a global auto-increment.

Useful phrases for interviews

  • “Before I write any code, I list the open questions and write down my assumptions.”
  • “Let me put a number on that: by ‘fast’ we mean a p95 under 300 milliseconds at 50 requests per second.”
  • “The key invariant here is that booked rooms never exceed total rooms for any night.”
  • “The booking stores a price snapshot, because rates change over time but the agreed price shouldn’t.”
  • “I model a one-to-one chat as a conversation with exactly two members, so there’s only one message table.”
  • “Inventory lives per room type per night, because availability differs from one night to the next.”
  • “Money goes into DECIMAL columns and BigDecimal in Java, never floating point.”
  • “Anything we deliberately leave out goes on an explicit out-of-scope list, so nobody is surprised later.”

Notes based on the Fundamentals of Backend course (Ronin Engineer), rewritten in my own words.