Cosmic Module

D

Qubits of DPK

April 11, 2026

Core DBMS

Domain Understanding

Zomato is a food delivery platform connecting three entities: customers who order food, restaurants that prepare it, and delivery partners who bring it. The schema must handle the complete order lifecycle from cart to delivery, real-time location tracking, dynamic pricing, and the restaurant catalogue (menus with items and variants). It is one of the most interview-relevant schemas because it touches almost every schema design concept: self-referential relationships, soft deletes, state machines, denormalization, audit fields, and high-concurrency writes.
The read-to-write ratio is mixed. Menu reads are very frequent. Order writes are high-volume during meal hours. Location updates from delivery partners are extremely write-heavy (GPS ping every few seconds).

Entity Map

sql
QUBITS OF DPK
1customers ──< addresses                  ← saved delivery addresses
2restaurants ──< menu_categories            ← Starters, Main Course, Drinks
3menu_categories ──< menu_items              ← individual dishes
4menu_items ──< item_variants               ← sizes, spice levels, etc.
5customers ──< orders
6orders ──< order_items                    ← line items on the order
7orders ── delivery_partners               ← assigned after confirmation
8orders ──< order_status_history           ← complete audit trail of status changes
9delivery_partners ──< location_updates   ← GPS pings (extremely write-heavy)
10orders ── payments
11customers ──< reviews (restaurant)        ← only after delivery
12customers ──< reviews (delivery_partner)  ← separate review dimension

Full Schema with Comments

sql
QUBITS OF DPK
1CREATE TABLE customers (
2  id           BIGINT AUTO_INCREMENT PRIMARY KEY,
3  name         VARCHAR(255) NOT NULL,
4  email        VARCHAR(255) NOT NULL UNIQUE,
5  phone        CHAR(10) NOT NULL UNIQUE,
6  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
7  updated_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
8  deleted_at   DATETIME NULL
9);
10
11-- A customer can save multiple delivery addresses
12CREATE TABLE addresses (
13  id           BIGINT AUTO_INCREMENT PRIMARY KEY,
14  customer_id  BIGINT NOT NULL,
15  label        VARCHAR(50),
16  -- 'Home', 'Office', 'Other'
17  address_line VARCHAR(500) NOT NULL,
18  city         VARCHAR(100) NOT NULL,
19  pincode      CHAR(6) NOT NULL,
20  latitude     DECIMAL(9,6) NOT NULL,
21  longitude    DECIMAL(9,6) NOT NULL,
22  -- DECIMAL(9,6): supports coordinates like 19.076090, 72.877426
23  -- FLOAT would lose precision for precise geolocation
24  is_default   BOOLEAN NOT NULL DEFAULT FALSE,
25  deleted_at   DATETIME NULL,
26  FOREIGN KEY (customer_id) REFERENCES customers(id)
27);
28
29CREATE TABLE restaurants (
30  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
31  name          VARCHAR(255) NOT NULL,
32  phone         VARCHAR(20) NOT NULL,
33  email         VARCHAR(255) UNIQUE,
34  address_line  VARCHAR(500) NOT NULL,
35  city          VARCHAR(100) NOT NULL,
36  pincode       CHAR(6) NOT NULL,
37  latitude      DECIMAL(9,6) NOT NULL,
38  longitude     DECIMAL(9,6) NOT NULL,
39  cuisine_type  VARCHAR(100),
40  -- 'North Indian', 'Chinese', 'South Indian'
41  avg_rating    DECIMAL(3,2) DEFAULT 0.00,
42  -- Denormalized: precomputed average from reviews table
43  -- Updated by background job, not recomputed live on every request
44  is_open       BOOLEAN NOT NULL DEFAULT TRUE,
45  -- Current open/closed status (toggle by restaurant owner during the day)
46  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
47  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
48  deleted_at    DATETIME NULL
49);
50
51-- Each restaurant organises its menu into categories
52CREATE TABLE menu_categories (
53  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
54  restaurant_id BIGINT NOT NULL,
55  name          VARCHAR(100) NOT NULL,
56  -- 'Starters', 'Main Course', 'Breads', 'Drinks', 'Desserts'
57  display_order INT NOT NULL DEFAULT 0,
58  -- Controls the order categories appear on the menu page
59  is_active     BOOLEAN NOT NULL DEFAULT TRUE,
60  FOREIGN KEY (restaurant_id) REFERENCES restaurants(id)
61);
62
63CREATE TABLE menu_items (
64  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
65  category_id   BIGINT NOT NULL,
66  name          VARCHAR(255) NOT NULL,
67  description   TEXT,
68  base_price    DECIMAL(8,2) NOT NULL,
69  -- DECIMAL: price must be exact, not floating-point
70  is_veg        BOOLEAN NOT NULL,
71  -- TRUE = vegetarian (shown as green dot on Zomato)
72  is_available  BOOLEAN NOT NULL DEFAULT TRUE,
73  -- Can be toggled off when an item runs out
74  image_url     VARCHAR(500),
75  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
76  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
77  deleted_at    DATETIME NULL,
78  -- Soft delete: deleted items must remain for historical order integrity
79  FOREIGN KEY (category_id) REFERENCES menu_categories(id)
80);
81
82-- Variants of a menu item: sizes, spice levels, add-ons
83-- e.g. Butter Chicken: [Half, Full] or [Mild, Spicy]
84CREATE TABLE item_variants (
85  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
86  item_id     BIGINT NOT NULL,
87  name        VARCHAR(100) NOT NULL,
88  -- 'Half', 'Full', 'Large', 'Extra Spicy'
89  extra_price DECIMAL(8,2) NOT NULL DEFAULT 0.00,
90  -- Price ADDITION on top of base_price. 0.00 for default variant.
91  is_default  BOOLEAN NOT NULL DEFAULT FALSE,
92  -- The variant selected by default when item is added to cart
93  FOREIGN KEY (item_id) REFERENCES menu_items(id)
94);
95
96CREATE TABLE delivery_partners (
97  id           BIGINT AUTO_INCREMENT PRIMARY KEY,
98  name         VARCHAR(255) NOT NULL,
99  phone        CHAR(10) NOT NULL UNIQUE,
100  vehicle_type ENUM('BICYCLE','MOTORCYCLE','CAR') NOT NULL,
101  is_online    BOOLEAN NOT NULL DEFAULT FALSE,
102  -- Whether the partner is currently available for assignments
103  current_lat  DECIMAL(9,6) NULL,
104  current_lng  DECIMAL(9,6) NULL,
105  -- Denormalized current location for fast nearby-partner queries
106  -- Kept in sync by location_updates inserts
107  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
108  deleted_at   DATETIME NULL
109);
110
111-- Every GPS ping from the delivery partner's phone
112-- This table grows extremely fast: one row every 5 seconds per active partner
113-- 10,000 active partners × 720 pings/hour = 7.2M rows/hour
114CREATE TABLE location_updates (
115  id                  BIGINT AUTO_INCREMENT PRIMARY KEY,
116  delivery_partner_id BIGINT NOT NULL,
117  latitude            DECIMAL(9,6) NOT NULL,
118  longitude           DECIMAL(9,6) NOT NULL,
119  recorded_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
120  FOREIGN KEY (delivery_partner_id) REFERENCES delivery_partners(id)
121  -- No soft delete: location history is high-volume operational data
122  -- Old data is archived/dropped after retention window (e.g. 30 days)
123);
124
125CREATE TABLE orders (
126  id                  BIGINT AUTO_INCREMENT PRIMARY KEY,
127  customer_id         BIGINT NOT NULL,
128  restaurant_id       BIGINT NOT NULL,
129  delivery_address_id BIGINT NOT NULL,
130  delivery_partner_id BIGINT NULL,
131  -- NULL initially: partner assigned after restaurant confirms order
132  order_status        ENUM(
133    'PLACED','CONFIRMED','PREPARING',
134    'READY_FOR_PICKUP','OUT_FOR_DELIVERY','DELIVERED','CANCELLED'
135  ) NOT NULL DEFAULT 'PLACED',
136  -- This is the CURRENT status only
137  -- Full status history is in order_status_history table
138  subtotal            DECIMAL(10,2) NOT NULL,
139  delivery_fee        DECIMAL(8,2) NOT NULL DEFAULT 0.00,
140  discount_amount     DECIMAL(8,2) NOT NULL DEFAULT 0.00,
141  total_amount        DECIMAL(10,2) NOT NULL,
142  -- total_amount = subtotal + delivery_fee - discount_amount
143  -- Stored denormalized: amount at time of order may differ from current menu prices
144  special_instructions TEXT NULL,
145  estimated_delivery_at DATETIME NULL,
146  delivered_at        DATETIME NULL,
147  created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
148  updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
149  FOREIGN KEY (customer_id)         REFERENCES customers(id),
150  FOREIGN KEY (restaurant_id)       REFERENCES restaurants(id),
151  FOREIGN KEY (delivery_address_id) REFERENCES addresses(id),
152  FOREIGN KEY (delivery_partner_id) REFERENCES delivery_partners(id)
153);
154
155-- Line items of the order: what was actually ordered
156CREATE TABLE order_items (
157  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
158  order_id    BIGINT NOT NULL,
159  item_id     BIGINT NOT NULL,
160  variant_id  BIGINT NULL,
161  -- NULL if no variant was chosen
162  item_name   VARCHAR(255) NOT NULL,
163  -- SNAPSHOT: copied from menu_items.name at order time
164  -- Menu item names can change. Order records must reflect what was ordered.
165  unit_price  DECIMAL(8,2) NOT NULL,
166  -- SNAPSHOT: price at the time of order, not current price
167  quantity    TINYINT NOT NULL DEFAULT 1,
168  FOREIGN KEY (order_id)   REFERENCES orders(id),
169  FOREIGN KEY (item_id)    REFERENCES menu_items(id),
170  FOREIGN KEY (variant_id) REFERENCES item_variants(id)
171);
172
173-- Complete audit trail of every status change on an order
174-- This is separate from the current status on the orders table
175CREATE TABLE order_status_history (
176  id           BIGINT AUTO_INCREMENT PRIMARY KEY,
177  order_id     BIGINT NOT NULL,
178  status       ENUM(
179    'PLACED','CONFIRMED','PREPARING',
180    'READY_FOR_PICKUP','OUT_FOR_DELIVERY','DELIVERED','CANCELLED'
181  ) NOT NULL,
182  changed_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
183  changed_by   ENUM('CUSTOMER','RESTAURANT','DELIVERY_PARTNER','SYSTEM') NOT NULL,
184  notes        TEXT NULL,
185  FOREIGN KEY (order_id) REFERENCES orders(id)
186  -- Append-only: status history is never updated or deleted
187);
188
189CREATE TABLE payments (
190  id             BIGINT AUTO_INCREMENT PRIMARY KEY,
191  order_id       BIGINT NOT NULL UNIQUE,
192  amount         DECIMAL(10,2) NOT NULL,
193  method         ENUM('UPI','CARD','CASH_ON_DELIVERY','WALLET','NETBANKING') NOT NULL,
194  status         ENUM('PENDING','SUCCESS','FAILED','REFUNDED') NOT NULL DEFAULT 'PENDING',
195  gateway_txn_id VARCHAR(100) UNIQUE NULL,
196  paid_at        DATETIME NULL,
197  FOREIGN KEY (order_id) REFERENCES orders(id)
198);
199
200-- Reviews can be for the restaurant (food quality) or delivery partner (service quality)
201CREATE TABLE reviews (
202  id                  BIGINT AUTO_INCREMENT PRIMARY KEY,
203  order_id            BIGINT NOT NULL,
204  customer_id         BIGINT NOT NULL,
205  review_type         ENUM('RESTAURANT','DELIVERY_PARTNER') NOT NULL,
206  -- Differentiates who is being reviewed
207  restaurant_id       BIGINT NULL,
208  delivery_partner_id BIGINT NULL,
209  -- Exactly one of these is set based on review_type
210  rating              TINYINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
211  comment             TEXT NULL,
212  created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
213  UNIQUE (order_id, review_type),
214  -- One review of each type per order
215  FOREIGN KEY (order_id)            REFERENCES orders(id),
216  FOREIGN KEY (customer_id)         REFERENCES customers(id),
217  FOREIGN KEY (restaurant_id)       REFERENCES restaurants(id),
218  FOREIGN KEY (delivery_partner_id) REFERENCES delivery_partners(id)
219);
220
221CREATE INDEX idx_orders_customer    ON orders(customer_id);
222CREATE INDEX idx_orders_restaurant  ON orders(restaurant_id);
223CREATE INDEX idx_orders_partner     ON orders(delivery_partner_id);
224CREATE INDEX idx_order_items_order  ON order_items(order_id);
225CREATE INDEX idx_menu_items_category ON menu_items(category_id);
226CREATE INDEX idx_location_partner   ON location_updates(delivery_partner_id);
227CREATE INDEX idx_location_time      ON location_updates(recorded_at);
228-- Used for retention purge: DELETE WHERE recorded_at < NOW() - INTERVAL 30 DAY

Key Design Decisions

The Price Snapshot Problem

This is the most commonly missed design insight in e-commerce and food delivery schemas.
If order_items only stores item_id and quantity, what happens when the restaurant changes Butter Chicken from ₹250 to ₹290 next month? Every historical order that looks up menu_items.price via the FK now shows the new price. The customer's receipt is wrong. The restaurant's revenue reconciliation is wrong.
The solution is to snapshot the price and name at order creation time into order_items.unit_price and order_items.item_name. These columns are set once when the order is placed and never updated. The FK to menu_items is kept for catalogue reference, but the price and name for billing purposes come from the snapshot columns.
This is an intentional, justified denormalization. You are trading storage (a few extra columns per order item) for temporal correctness (accurate historical billing records).

Indexing Strategy

Common Traps to Avoid

  • Not snapshotting price and name in order_items — Price changes corrupt historical order records. Always snapshot at order time.
  • Storing only current status on orders, no history table — You lose the ability to measure time-between-states (how long did preparation take?) and cannot debug delivery disputes.
  • Computing avg_rating live on listing pages — Aggregating reviews for 500 restaurants on every search query will kill the database. Precompute and denormalize.
  • Not having a separate location_updates table — Updating current_lat/lng on delivery_partners for every GPS ping creates massive write contention on a single row per partner. Append to location_updates instead and denormalize only the latest position.
  • Hard-deleting menu items — Order history references menu items. Hard delete breaks FK integrity and historical records.

Interview Q&A

Q1: Why does order_items store item_name and unit_price if there's already a FK to menu_items?
Because menu items change over time. A restaurant can rename a dish or change its price. The FK preserves the catalogue relationship, but billing records must reflect the price at the time of purchase. unit_price and item_name are snapshots taken at order creation and never modified afterward.
Q2: How do you track the full lifecycle of an order?
Two mechanisms: orders.order_status holds the current state for fast reads. order_status_history is an append-only table that records every status transition with a timestamp and actor. This enables SLA tracking (how long was the order in PREPARING state?), support queries, and dispute resolution.
Q3: How do you find the nearest available delivery partner efficiently?
delivery_partners has a denormalized current_lat and current_lng updated after each location ping. The nearest-partner query runs a haversine distance calculation against rows where is_online = TRUE. At production scale this uses a geospatial index (MySQL SPATIAL index or PostGIS) or a Redis Geo sorted set for sub-millisecond lookups.
Q4: Why is location_updates a separate table instead of updating a column on delivery_partners?
GPS pings arrive every 5 seconds per partner. Updating the same row on delivery_partners 720 times per hour per partner creates severe write contention and InnoDB row lock conflicts. Appending to location_updates is a pure INSERT with no locking conflicts. The current_lat/lng on delivery_partners is updated asynchronously by a background job as a read optimisation.
Q5: How do you handle a refund when an order is cancelled?
The payments table has a status column with a REFUNDED state. A refund creates a new entry in a payment_refunds table (not shown here for brevity) linked to the original payment, recording the refund amount, reason, and gateway transaction ID. The original payment row is never modified.

ANKI Cards

Q: Why are item_name and unit_price stored as snapshot columns in order_items instead of always reading from menu_items?
A: Menu item names and prices change over time. Snapshotting at order creation time ensures historical orders always reflect the price and name at the time of purchase, regardless of future menu changes. The FK to menu_items is kept for catalogue reference only.
Q: In a food delivery schema, why is there both orders.order_status AND an order_status_history table?
A: order_status is the current state — fast to read for active order tracking. order_status_history is the append-only audit trail recording every transition with a timestamp. You need both: current state for the app UI, full history for SLA measurement and dispute resolution.
Q: Why is DECIMAL(9,6) used for latitude/longitude instead of FLOAT?
A: FLOAT has only ~7 significant digits of precision. At the equator, accurate geolocation to within 11 centimetres requires 6 decimal places. DECIMAL(9,6) stores exact values with 6 decimal places of precision. FLOAT accumulates rounding errors that corrupt precise location matching.