Data modeling series: 1. The data modeling process · 2. JSON columns, online DDL, data types and views · 3. Case studies: translations, ordering, calendars and reports
This post applies the process from part 1 to six small problems that come up a lot in senior interviews, where the “right” answer lives in the trade-offs rather than in the syntax. Each case study follows the rhythm problem → options → choice → where it breaks, with SQL run on PostgreSQL 16 (a few bits on MySQL 8.4); the numbers come from my lab, so yours will differ, but the ratios hold. Worth reading first: Indexes, EXPLAIN, N+1 and pagination and Index types beyond the B-tree (GIN, GiST, exclusion constraints).
At a glance:
flowchart LR
CS[Case studies] --> T["Translations: JSON or table"]
CS --> K["Drag-drop: gaps / fractional"]
CS --> H["Homestay: one row per day"]
CS --> R["Recurring: local + IANA + RRULE"]
CS --> TG["Tags: N–N table"]
CS --> CL["Clicks: per-minute rollup"]
J{"JSON field in WHERE?"} -->|yes| COL[Make it a column]
J -->|no| KJ[Keep JSON]
1. Multilingual content
Key idea: a JSON column {"vi": ..., "en": ...} is enough when translations are only displayed; once there is a translation workflow (reviews, tracking stale copies), use a separate translation table.
Problem. Job titles are shown in several languages. Adding a language must not change the schema, a missing translation falls back to a default language, and users can search by the translated name. Assume 200,000 rows, 2–3 languages each, and far more reads than writes.
The slides suggest a JSON column, and the lab confirms it works: CHECK (title ? 'en') guarantees the fallback exists, and each searchable language gets an expression index (an index on an expression such as title->>'vi').
The three options, lab SQL, index sizes and the MySQL version
| Option | Adding a language | Reading one row | Searching a translation | Constraints |
|---|---|---|---|---|
A. JSON column {"vi": ..., "en": ...} |
No schema change | No JOIN | Expression index or GIN | CHECK only |
B. Translation table (entity_id, locale, value) |
No schema change | JOIN, fallback needs DISTINCT ON |
One (locale, value) index |
NOT NULL, FK, metadata |
| C. One column per language | Schema change | Simplest | Plain index | Full |
CREATE TABLE job_title (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
code text NOT NULL UNIQUE,
title jsonb NOT NULL CHECK (title ? 'en') -- the fallback must always exist
);
SELECT code, coalesce(title->>'ja', title->>'en') FROM job_title WHERE id IN (1, 3);
-- job-1 | Engineer 1 job-3 | エンジニア 3
CREATE INDEX job_title_vi ON job_title ((title->>'vi')); -- 6.2 MB
CREATE INDEX job_title_gin ON job_title USING gin (title jsonb_path_ops); -- 25 MB
title->>'vi' = ...used the expression index,title @> '{"vi": ...}'used the GIN index, and an insert withoutenwas rejected by the CHECK.- Option B on the same data: 466,666 rows, 60 MB (A plus its two indexes: 68 MB). The fallback is written as
DISTINCT ON (j.id) ... ORDER BY j.id, (t.locale = 'ja') DESC. - MySQL 8.4 indexes a JSON field through a generated column
AS (title->>'$.vi') STOREDor a functional index((CAST(title->>'$.en' AS CHAR(200)) COLLATE utf8mb4_bin)). In the lab, a plainWHERE title->>'$.en' = ...still used the functional index.
2. Drag-and-drop ordering in a to-do list
Key idea: number positions with gaps so each drag writes one row; when a gap runs out, renumber the whole list or use string keys, not floating point.
Problem. Users reorder tasks by dragging. Reading in order must be cheap (ORDER BY on an index), and each drag should write one row. Assume 10–1,000 tasks per list.
With positions 1, 2, 3, inserting in the middle shifts every row after it. The slides number positions with gaps (1000, 1500, 2000, 3000) and give the new item the midpoint (upper + lower) / 2; when there is no room left (1000 and 1001) they switch to floating point. The Java lab: there are only 43 distinct double midpoints between 1000 and 1001 (53 between 1000 and 2000), because each halving uses up one of the 52 mantissa bits.
| Option | Writes per drag | When it runs out | Notes |
|---|---|---|---|
| Integers with gaps + renumbering | 1 row | Renumber the whole list | Fine for small lists |
| String keys (fractional indexing, LexoRank style) | 1 row | Never, keys just grow | Needs the "C" collation |
Linked list (prev_id) |
2–3 rows | Not applicable | Reading in order needs recursion |
String keys (fractional indexing): the position is a string compared lexicographically, and between any two strings there is always a third. It’s like inserting page “12a” between pages 12 and 13 of a notebook. Keys grow if you keep inserting at the same spot, so you need a rebalancing job.
Renumbering in one UPDATE, the Java string-key code and the test results
Renumbering is a single UPDATE ... FROM (SELECT id, row_number() OVER (ORDER BY pos) * 1000 ...) after a SELECT ... FOR UPDATE on the list. The UNIQUE (list_id, pos) constraint is declared DEFERRABLE INITIALLY DEFERRED so it tolerates temporary duplicates mid-statement (lab: 1000, 1001, 1250, 2000 became 1000, 2000, 3000, 4000).
String keys, a Java 21 implementation with base-36 digits:
static final String DIGITS = "0123456789abcdefghijklmnopqrstuvwxyz";
/** A key strictly between a and b; a == null means the start, b == null the end. */
static String between(String a, String b) {
if (a == null) a = "";
if (b != null && a.compareTo(b) >= 0) throw new IllegalArgumentException(a + " >= " + b);
StringBuilder out = new StringBuilder();
for (int i = 0; ; i++) {
int lo = i < a.length() ? DIGITS.indexOf(a.charAt(i)) : 0;
int hi = (b != null && i < b.length()) ? DIGITS.indexOf(b.charAt(i)) : DIGITS.length();
if (hi - lo > 1) return out.append(DIGITS.charAt((lo + hi) / 2)).toString();
out.append(DIGITS.charAt(lo));
if (hi > lo) b = null; // we are already below b's prefix
}
}
// between(null, null) = "i", between("i", "r") = "m", between("i", "j") = "ii", between("z", null) = "zi"
- The last digit is always the midpoint of a gap of width ≥ 2, so it is never
'0', which means you can always insert before any key. - 100,000 inserts at random positions kept the order intact, with a longest key of 8 characters.
- 10,000 inserts at the same spot pushed the key to 2,001 characters. That is why real LexoRank implementations still have a rebalancing job for keys that grow too long.
3. Homestay availability calendar
Key idea: one row per (homestay, date) holding price and status; booking is one conditional UPDATE on status = 'OPEN', then a check that the updated row count equals the number of nights.
Problem. Each homestay has a per-day calendar: is it free, and at what price (weekends and holidays cost more). Main queries: “which homestays are free for 3 nights from 24 December” and “book those 3 nights”. Assume 10,000 homestays and bookings up to a year ahead.
The slides pre-generate 365 rows when a homestay is created, so repricing a day touches exactly one row. The table homestay_availability(homestay_id, stay_date, price, status, booking_id) has primary key (homestay_id, stay_date), with status in OPEN | BOOKED | BLOCKED.
Finding free homestays: the primary key leads with homestay_id, so it can’t help (Parallel Seq Scan, 139 ms); a partial covering index holding only free nights brings it down to 5 ms. Booking:
UPDATE homestay_availability SET status = 'BOOKED', booking_id = 101
WHERE homestay_id = 42 AND stay_date >= '2026-12-24' AND stay_date < '2026-12-27'
AND status = 'OPEN'; -- must be exactly 3 rows, otherwise ROLLBACK
Homestay lab: the window job, the free-night index, sizing and the three-session test
-- daily job that extends the booking window; safe to run any number of times
INSERT INTO homestay_availability (homestay_id, stay_date, price)
SELECT h.id, d::date, h.base_price
FROM homestay h, generate_series(current_date, current_date + 365, interval '1 day') d
ON CONFLICT (homestay_id, stay_date) DO NOTHING;
CREATE INDEX ha_open_by_date ON homestay_availability (stay_date, homestay_id)
INCLUDE (price) WHERE status = 'OPEN';
SELECT homestay_id, sum(price) AS total
FROM homestay_availability
WHERE stay_date >= '2026-12-24' AND stay_date < '2026-12-27' AND status = 'OPEN'
GROUP BY homestay_id HAVING count(*) = 3 LIMIT 20;
-- Index Only Scan using ha_open_by_date, 5 ms
Sizing: 10,000 × 365 = 3.65 million rows, 367 MB; at 100,000 homestays that is about 3.6 GB, so past dates should move to a history table.
The lab wraps the booking in a DO block with GET DIAGNOSTICS n = ROW_COUNT and raises an exception when it isn’t 3.
- Session 1 books 24–26 December, sleeps 2 seconds, then commits.
- Session 2 books the same three nights, waits on the row locks, then re-evaluates
status = 'OPEN'against the new row versions and updates 0 rows. - Session 3 books 26–28 December (one night overlapping), updates 2 rows, sees
2 <> 3and rolls back; the 27th and 28th stayOPEN.
In Java: compare the return value of executeUpdate, and throw if it’s wrong so @Transactional rolls back.
4. Recurring calendar events
Key idea: store the recurrence rule in local time together with an IANA time zone id, then pre-generate occurrences for a window as timestamptz rows so the weekly query is a range scan.
Problem. Events have a start and end time and may repeat daily or weekly; the main screen is “my events this week”. Users live in different time zones, and events may repeat forever.
The slides propose pre-generating time slots into a time_slots table (local start, local end, time zone). Three things are missing:
- The recurrence rule (iCalendar RRULE, e.g.
'FREQ=WEEKLY;BYDAY=MO') so you can keep generating once the window runs out. - Exceptions: a moved or cancelled occurrence.
- Daylight saving time (DST): a zone’s offset changes twice a year.
The DST trap. A meeting at 09:00 Berlin time every Monday from 15 March 2027; Berlin switches to summer time on 28 March 2027.
-- Wrong: add 7 days to a UTC instant
SELECT ts AT TIME ZONE 'Europe/Berlin'
FROM generate_series(timestamptz '2027-03-15 08:00+00', timestamptz '2027-04-05 08:00+00', interval '7 days') ts;
-- 09:00, 09:00, 10:00, 10:00 ← drifts to 10 o'clock from 29 March
-- Right: add 7 days in local wall-clock time, then convert to UTC
SELECT l AT TIME ZONE 'Europe/Berlin'
FROM generate_series(timestamp '2027-03-15 09:00', timestamp '2027-04-05 09:00', interval '7 days') l;
-- 08:00Z, 08:00Z, 07:00Z, 07:00Z ← always 09:00 in Berlin
The same schedule in Asia/Ho_Chi_Minh (no DST) is always 02:00Z, so the bug never shows up if you only test from Vietnam.
The series schema, non-existent local times, exceptions and a two-sided weekly query
The recurring_event table holds the series definition: tz (an IANA id such as 'Europe/Berlin'), first_local_start timestamp (local time, no offset), duration, rrule and materialized_until. Generated occurrences live in:
CREATE TABLE event_occurrence (
event_id bigint NOT NULL REFERENCES recurring_event(id),
original_local_start timestamp NOT NULL, -- identity of the occurrence in the series
start_at timestamptz NOT NULL,
end_at timestamptz NOT NULL,
owner_id bigint NOT NULL,
status text NOT NULL DEFAULT 'ACTIVE', -- ACTIVE | CANCELLED
is_exception boolean NOT NULL DEFAULT false,
PRIMARY KEY (event_id, original_local_start)
);
CREATE INDEX occ_owner_time ON event_occurrence (owner_id, start_at);
Non-existent times. 02:30 on 28 March doesn’t exist in Berlin; PostgreSQL maps it to 01:30Z (03:30 summer time).
Exceptions. Moving the 5 April occurrence to Tuesday 14:00 means changing start_at, keeping original_local_start and setting is_exception; cancelling 12 April means status = 'CANCELLED'. The generation job (a 6–12 month window, recorded in materialized_until) uses ON CONFLICT ... DO NOTHING, so it never overwrites exceptions. Editing “this and following occurrences” splits the series in two.
The weekly query (the week is in the viewer’s time zone):
SELECT * FROM event_occurrence
WHERE owner_id = 7 AND status = 'ACTIVE'
AND start_at < timestamp '2027-04-12 00:00' AT TIME ZONE 'Asia/Ho_Chi_Minh'
AND end_at > timestamp '2027-04-05 00:00' AT TIME ZONE 'Asia/Ho_Chi_Minh'
AND start_at >= timestamp '2027-04-05 00:00' AT TIME ZONE 'Asia/Ho_Chi_Minh' - interval '1 day';
With only the overlap condition, the Index Cond contains just start_at <, so it rescans every past occurrence. The last line adds a lower bound of “week start minus the maximum event length” so the range scan has two ends.
5. Tagging
Key idea: the N–M table post_tag is the source of truth because it has foreign keys and cheap renames; a tags array with GIN only wins when tags are nearly immutable and reading a post’s tags dominates.
Problem. A post has many tags. Two queries: (1) the tags of a post, (2) the posts with a tag; (1) runs far more often. Assume 300,000 posts, 2,000 tags, 4 tags per post, and one very popular tag (20% of posts).
The slides give two answers: an N–M table post_tag, and (on PostgreSQL) a tags text[] column with GIN (an inverted index: each value points back to the rows that contain it). The lab built both on the same data: reading a post’s tags took 0.01 ms (array) versus 0.07 ms (N–M); renaming a popular tag took 297 ms (array, rewriting 60,472 rows) versus 2 ms (N–M, one row). GIN only pays off when filtering on several tags.
Measurements for N–M versus array + GIN on 300,000 posts
| Query | N–M (post_tag + tag) |
tags array + GIN |
|---|---|---|
| Tags of post 4242 | Index Only Scan + Nested Loop, 0.07 ms | One row by PK, 0.01 ms |
| Newest 20 posts with a rare tag (626 posts) | 1.3 ms | 1.1 ms |
| Count posts with both of two tags | Two JOINs or HAVING |
tags @> ARRAY['tag1','tag777'], Bitmap Index Scan, 0.3 ms |
| Rename a popular tag (60,472 posts) | Update 1 tag row, 2 ms |
Rewrite 60,472 post rows, 297 ms |
| Size | post_tag 146 MB |
GIN 4.3 MB (+ arrays inside post) |
For a rare tag with ORDER BY created_at DESC LIMIT 20, the planner skipped GIN: it walked the created_at index backwards and filtered the arrays (discarding 8,660 rows).
6. Reporting: total clicks in the last day
Key idea: don’t run count(*) every time the dashboard opens; read from a per-minute rollup table and accept up to a minute of delay in exchange for a ~1 ms read.
Problem. A dashboard shows total clicks over the last 24 hours; response time and accuracy pull in opposite directions. Assume 2 million clicks a day, 3 days kept (6 million rows), and a dashboard opened hundreds of times a minute.
In the lab, count(*) took 180–270 ms and grows with traffic; summing 1,440 rollup rows took just 1.2 ms. The other options (materialized view, stream processing, estimates from statistics) are in the folded table below.
The rollup job must be idempotent (re-running it doesn’t skew the numbers): every minute it recomputes the last 5 minutes and overwrites them, which also catches late clicks.
Comparing five approaches, the per-minute rollup SQL and materialized views
| Approach | Lab | Accuracy | Data delay |
|---|---|---|---|
Indexed count(*) over the range |
180–270 ms, grows with traffic | Exact | None |
| Per-minute rollup table (upsert, cron) | Summing 1,440 rows: 1.2 ms | Exact up to the last closed minute | Up to 1 minute |
| Hourly materialized view | Refresh 351 ms, read 0.2 ms | Up to the last refresh | Refresh schedule |
| Stream processing (Kafka Streams, Flink) | Not run | Depends on semantics | Seconds |
| Estimate from statistics | Practically free | Depends on how fresh the stats are | Until the next ANALYZE |
CREATE TABLE click_per_minute (minute timestamptz PRIMARY KEY, clicks bigint NOT NULL);
-- runs every minute, idempotent: re-aggregate the last 5 minutes to catch late clicks
INSERT INTO click_per_minute
SELECT date_trunc('minute', clicked_at), count(*) FROM click
WHERE clicked_at >= :current_minute - interval '5 minutes' AND clicked_at < :current_minute
GROUP BY 1
ON CONFLICT (minute) DO UPDATE SET clicks = EXCLUDED.clicks;
SELECT sum(clicks) FROM click_per_minute WHERE minute >= now() - interval '24 hours';
REFRESH MATERIALIZED VIEW CONCURRENTLY (which doesn’t block readers) needs a unique index; MySQL has no materialized views at all.
Recap: what “leverage JSON” should really mean
Key idea: JSON fits data that is read together, lightly constrained and changes shape often; data that needs constraints, JOINs or renames belongs in columns or tables.
The slides end with three points: leverage JSON; use normalization and denormalization; pre-generate time slots when you need to query by time. The last two hold up, the first needs narrowing.
- Good fit for JSON: displayed translations, extension attributes, partner payloads.
- Poor fit: tags with a lifecycle, money, workflow state (they need partial updates and constraints).
- Once a JSON field shows up in a
WHERE, it needs an expression index or a generated column, and it’s worth asking why it isn’t a column yet.
Exercises
Exercise 1: Drag and drop on a multi-column Kanban board
Task. Cards live in columns (To do, Doing, Done) and can be dragged within a column or across columns, safely when two people drag at the same time.
Solution 1: string ranks, a version column and a unique constraint
CREATE TABLE card (
id bigint PRIMARY KEY,
column_id bigint NOT NULL,
rank text COLLATE "C" NOT NULL,
version int NOT NULL DEFAULT 0,
UNIQUE (column_id, rank)
);
-- card 9 moves to column 3, between cards ranked 'i' and 'r'; the client saw version 5
UPDATE card SET column_id = 3, rank = :newRank, version = version + 1
WHERE id = 9 AND version = 5; -- 0 rows: someone else already moved the card
- The server computes
newRank = between("i", "r")from the two neighbouring cards itself instead of trusting a rank sent by the client. versionstops two people dragging the same card.UNIQUE (column_id, rank)stops two cards landing in the same gap (recompute and retry once).- A background job rebalances columns whose ranks grow beyond 20 characters.
Exercise 2: Meeting-room booking without overlaps
Task. Rooms are booked for arbitrary time ranges, with no overlap against other bookings of the same room; cancelled bookings don’t count.
Solution 2: tstzrange and an exclusion constraint
There are no per-day prices, only overlaps to prevent, so use ranges and an exclusion constraint:
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE room_booking (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
room_id bigint NOT NULL,
during tstzrange NOT NULL,
status text NOT NULL DEFAULT 'ACTIVE',
EXCLUDE USING gist (room_id WITH =, during WITH &&) WHERE (status = 'ACTIVE')
);
INSERT INTO room_booking (room_id, during) VALUES (1, '[2027-01-05 09:00+07, 2027-01-05 10:00+07)');
INSERT INTO room_booking (room_id, during) VALUES (1, '[2027-01-05 09:30+07, 2027-01-05 11:00+07)');
-- ERROR 23P01: conflicting key value violates exclusion constraint
- In the lab, a 10:00–11:00 booking went straight in thanks to the half-open
[)range, and an overlapping booking markedCANCELLEDwas accepted too. - The application catches SQLSTATE
23P01and returns 409 Conflict. - On MySQL: lock the
roomrow withSELECT ... FOR UPDATE, check for overlaps, then insert.
Remember
- Know when to pick a JSON column and when to pick a translation table for multilingual content.
- Explain why floats don’t save drag-and-drop ordering, and name the two alternatives.
- Write a multi-night booking that is safe under concurrency, and explain why the row count must be checked.
- Explain why recurring events must be expanded in local time with an IANA time zone id.
- Tell total clicks (an additive counter) apart from distinct users (HyperLogLog).
Interview questions
Junior
How do you store multilingual content?
Either a JSON column of translations in the same table or a translation table (entity_id, locale, value); either way, always have a fallback language.
JSON column: no JOIN, no schema change for a new language. Translation table: full constraints, one index for every language.
Why number positions with gaps (1000, 2000) instead of 1, 2, 3?
So inserting in the middle updates a single row (the midpoint) instead of shifting every row after it.
When the gap runs out, renumber or switch to string keys.
Why not compute total clicks with count(*) every time the dashboard opens?
Because the cost grows with traffic (about 200 ms for 2 million clicks in the lab) and the dashboard is opened constantly.
With per-minute pre-aggregation each read sums only 1,440 rows, at the price of up to a minute of delay.
Mid
How do you book 3 homestay nights without double booking?
One conditional UPDATE with status = 'OPEN' over the three dates inside a transaction, then check that exactly 3 rows were updated, and roll back otherwise.
A competing transaction waits on the row locks, re-evaluates the condition and updates nothing.
How do you store the times of a recurring event?
Store the local time, an IANA time zone id and the RRULE; generate occurrences for a time window as rows with start_at timestamptz for querying.
Don’t store a fixed offset, because DST changes the offset between occurrences.
What are the downsides of a tag array with GIN compared with an N–M table?
No foreign key, renaming a tag rewrites every row that has it (297 ms versus 2 ms in the lab), GIN slows writes, and it ties you to PostgreSQL.
It fits when tags are nearly immutable and reads dominate.
Senior
The slides switch the position column to float when integers run out. How would you review that?
A float adds only about 40–50 halvings at the same spot (52 mantissa bits), after which the order breaks silently.
I’d propose detecting the collision and renumbering in a transaction, or string keys in the “C” collation with a unique constraint and a rebalancing job.
Homestay availability: one row per day or date ranges?
One row per day when price and status vary by day and you need a month view; ranges with an exclusion constraint when the main job is preventing overlaps.
Per-day rows cost rows and need a job to extend the window. Ranges (PostgreSQL) pre-generate nothing, but working out free days is harder.
How would you count distinct users who clicked in the last 30 days with billions of clicks?
HyperLogLog: one sketch of a few KB per day, and merging 30 sketches gives an estimate within about 1%.
Exact distinct counts can’t be added across days and need memory proportional to the number of users. If exactness is required, run a batch job.
When do you choose a JSON column instead of adding a table or columns?
When the data is read together, lightly constrained and changes shape often.
Any field that starts showing up in WHERE, JOINs, constraints or separate updates gets promoted to a column or a table; a generated column or expression index is the stopgap.
Useful phrases for interviews
- “The slide’s answer works, but let me state the numbers I’m assuming first: two hundred thousand rows, mostly reads.”
- “A JSON column is fine for display-only translations; once there’s a translation workflow, I’d move to a translation table.”
- “Floats only buy you about fifty midpoints, so I’d use fractional indexing with a rebalancing job.”
- “Booking is one conditional update, and the row count must equal the number of nights or we roll back.”
- “Recurring events are defined in local time, so I store the IANA zone and expand occurrences in wall-clock time.”
- “Total clicks is an additive counter; HyperLogLog is for distinct counts.”
- “I’d serve the dashboard from a per-minute rollup and accept up to a minute of staleness.”
- “When a JSON field starts appearing in WHERE clauses, it’s telling us it wants to be a column.”
Notes based on the Advanced Backend course (Ronin Engineer), rewritten in my own words.