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:
- Search hotels by location, guests per room, check-in date and check-out date.
- View a hotel, including its room types.
- 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_couponjunction 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
versioncolumn 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
messagetable is usually partitioned or sharded byconversation_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 uniquedirect_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, addCHECK (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_idon 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 movemessageto a wide-column store like Cassandra withconversation_idplus 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.