Data Modeling

A team builds the reservation system from requirements and scope. They split the reservations table across 8 database partitions by a hash of the reservation id, so rows spread perfectly evenly. Then two diners book table 7 at 19:30 at the same moment. Their two new reservations hash to different partitions, each partition checks only its own rows, both find no conflict, and both commit. The table is double-booked, the one thing the requirements forbade.

The partitioning was wrong for this system because nobody first asked which queries run, and which of them must see each other's writes.

#Access patterns first

Entities are the easy part: restaurants, tables, time slots, reservations, diners. Draw them in a minute. Then list every way the system reads and writes them, with a rough rate, before choosing a single key. The rates come from the estimates (20 million searches and 1 million bookings a day, so about 1,000 searches and 50 bookings a second at peak).

Read the right-hand column: almost everything starts from a restaurant, one pattern starts from a diner, and the last belongs in a search index, not this database.

#A worked schema

restaurants (restaurant_id PK, name, city, cuisine, timezone)
 
tables      (restaurant_id, table_id, seats,
             PRIMARY KEY (restaurant_id, table_id))
 
slots       (restaurant_id, slot_date, slot_time, table_id, seats,
             reservation_id NULL,          -- NULL means free
             PRIMARY KEY (restaurant_id, slot_date, slot_time, table_id))
 
reservations (restaurant_id, reservation_id, table_id, slot_date, slot_time,
              user_id, party_size, status, created_at,
              PRIMARY KEY (restaurant_id, reservation_id))
 
user_reservations (user_id, slot_date, slot_time, reservation_id,
                   restaurant_name,        -- copied on purpose
                   PRIMARY KEY (user_id, slot_date, slot_time, reservation_id))

Why each piece is there:

• A row per slot. "Is table 7 free at 19:30?" becomes one row, and booking becomes a conditional update: set reservation_id only where it is still NULL. If zero rows change, someone else won, and the API returns 409. The database does the check and the write as one step, so there is no race.
• Every restaurant table starts with restaurant_id. The slot update and the reservation insert share a partition key, so they commit in one local transaction, and an open-slots query reads one contiguous range.
• user_reservations repeats data. The diner's page needs reservations sorted by time with the restaurant's name. Keyed by user_id, that is one range read with no join.

Size it. Slots for 50,000 restaurants × 20 tables × 8 sittings a day × 60 days ahead = 480 million rows; at about 50 bytes each, 24 GB. Reservations at 1 million a day × 365 × 200 bytes = 73 GB a year. Both fit one server, and about 1,050 operations a second at peak suits one primary with read replicas. You may never shard. Pick the partition key anyway: if you ever do, it decides which transactions stay on one machine.

#Normalize, then copy on purpose

Normalized means each fact lives in one place: the restaurant's name only in restaurants. Updates are simple and nothing disagrees, but reads join. Denormalized means you copy facts next to where they are read, so reads are one lookup, and every change must reach every copy.

The rule: start normalized and denormalize one access pattern at a time, each with a named reason and a named way to keep the copy in step.

#Choosing the partition key

When data outgrows one machine, the partition key decides which machine holds each row (the mechanics are in sharding and partitioning). A good key puts everything a frequent query or a transaction needs on one partition, and spreads load evenly. Those two goals pull against each other.

What would change the choice: if one restaurant could take thousands of bookings a second (a stadium, say), its partition would run hot and you would split its slots by date or section. At 50 bookings a second for the whole system, that is not this problem.

#How a booking flows through the model

#In the interview

Spend about 3 minutes here, as in the framework, and let the access patterns pick the store: single-key lookups suggest a key-value store, transactions and constraints suggest a relational database (see choosing a database). Here that is a relational database, because a booking is a conditional update plus an insert in one transaction. Phrases that show judgment:

• "Before picking keys, here are the five queries and how often each runs."
• "Booking must be atomic with the availability check, so both live under the same partition key."
• "The diner's list is a denormalized view, updated from events. It can lag a few seconds; the booking itself cannot."

Follow-ups you should expect:

• "Why not a secondary index on user_id?" On one node, that is simpler and fine. On a partitioned store, a global secondary index is a separately partitioned copy. Depending on the store it updates asynchronously (the same lag, with less control) or in a cross-partition transaction (slower bookings).
• "How do you handle time zones?" Store the slot in the restaurant's local date and time plus its time zone, since that is how diners and staff think about it.

#Common mistakes

#What to practice next

See how indexes make each access pattern fast in Indexing, then practice the whole module on Design Ticket Booking, where the same no-double-booking rule shapes the data.