Schema design is turning a vague product description ("customers buy things, we take payment, they chat") into tables, keys, constraints and indexes that stay correct under concurrency and stay fast at scale. Interviewers ask "design the database for an e-commerce system, a messaging app, an order/payment system" because the answer reveals whether you reason from requirements and access patterns, whether you know which invariants belong in the database itself, and whether you can spot where the design will hurt (money, ordering, retries, hot rows). For a Backend or AI engineer it is the highest-leverage skill in this section: a wrong key or a missing constraint is expensive to fix once data exists, whereas an index can be added later.
1. Requirements -> nouns become ENTITIES, verbs become RELATIONSHIPS
"a customer places orders; an order has items; a payment settles an order"
2. Cardinality -> 1:N = foreign key on the "many" side
M:N = junction table (order_items: order x product)
1:1 = same table, or FK that is also UNIQUE
3. Keys -> one PRIMARY KEY per table (may be composite); UNIQUE for every other
natural identifier (email, sku); FK for every relationship
4. Invariants -> NOT NULL, CHECK, FK, UNIQUE: rules the DB enforces even if a
buggy service forgets them
5. Access paths -> list the top queries ("latest 50 messages in a chat",
"orders for a customer"), then create the indexes they need
6. Growth -> which table grows unbounded? what is the partition/shard key?
what is archived? what needs an audit trail?
E-commerce sketch (1:N and M:N):
customers 1 ---- N orders 1 ---- N order_items N ---- 1 products 1 ---- 1 inventory
|
1 ---- N paymentsThe order of the steps matters. Doing entities and constraints first makes the schema correct; doing access paths second makes it fast. Designing from screens or endpoints alone tends to produce tables shaped like JSON responses, which are convenient until two screens need the same fact and now it is stored twice. The correct instinct is to normalize the system of record (see the normalization page), enforce invariants with constraints, and add derived copies later for specific measured read paths.
customers(customer_id PK, email UNIQUE NOT NULL, name)
products(product_id PK, sku UNIQUE NOT NULL, name, price_cents CHECK (price_cents >= 0))
inventory(product_id PK/FK, on_hand CHECK (on_hand >= 0))
orders(order_id PK, customer_id FK, status CHECK IN (...), ship_city, total_cents)
order_items(order_id FK, product_id FK, qty CHECK (qty > 0),
unit_price_cents, PK (order_id, product_id))
INDEX orders(customer_id, order_id) -- "my orders" page
-- Real sqlite3 output: the product price rises to 6000 AFTER the order was placed.
order_id total_cents name qty charged current_price
-------- ----------- -------- --- ------- -------------
1 12000 Keyboard 2 5000 6000
1 12000 Mouse 1 2000 2000
-- Real sqlite3 errors: the schema rejects bad writes on its own
UPDATE inventory SET on_hand = on_hand - 1 ... CHECK constraint failed: on_hand >= 0
INSERT INTO orders VALUES (2, 99, ...) FOREIGN KEY constraint failed
INSERT INTO orders ... status 'refunded' CHECK constraint failed: status IN (...)
INSERT INTO customers ... duplicate email UNIQUE constraint failed: customers.emailThree ideas carry most of the e-commerce answer. First, order_items.unit_price_cents is a snapshot of what was charged: it is a different fact from products.price_cents, and without it every price change rewrites history. The same applies to the shipping address, which should be copied onto the order (or stored as an immutable address version) rather than pointing at a customer address the user can later edit. Second, money is stored as an integer in minor units (or a fixed-precision NUMERIC), with a currency code, never a binary floating-point type. Third, inventory is a hot, contended row, so give it its own table, guard it with CHECK (on_hand >= 0), and decrement it atomically (UPDATE ... SET on_hand = on_hand - :n WHERE product_id = :id AND on_hand >= :n, then check the affected-row count) rather than reading the value and writing it back, which is a lost-update race. Depending on requirements, real systems also add reservations with expiry so carts do not hold stock forever.
users(user_id PK, handle UNIQUE)
conversations(conversation_id PK, kind CHECK IN ('direct','group'))
conversation_members(conversation_id, user_id, last_read_seq, PK (conversation_id, user_id))
INDEX (user_id, conversation_id) -- "my conversations"
messages(conversation_id, seq, sender_id, body, sent_at,
PK (conversation_id, seq)) -- seq: per-conversation counter
Access pattern 1: latest N messages WHERE conversation_id = ? ORDER BY seq DESC LIMIT N
Access pattern 2: page older messages ... AND seq < :oldest_seen ORDER BY seq DESC LIMIT N
Access pattern 3: unread badge COUNT(*) WHERE seq > last_read_seq
-- Real sqlite3 output (8 messages in group 2; keyset page below seq 6)
seq sender_id body seq sender_id body
--- --------- ----- --- --------- -----
8 3 msg 8 5 3 msg 5
7 2 msg 7 4 2 msg 4
6 1 msg 6 3 1 msg 3
-- Real SQLite plan for the keyset page:
SEARCH messages USING PRIMARY KEY (conversation_id=? AND seq<?)The composite primary key (conversation_id, seq) makes every message of a conversation physically adjacent in the key order (in engines that cluster on the primary key, such as InnoDB, or in SQLite's WITHOUT ROWID tables), so "the last 50 messages" is a short range read with no sort. A per-conversation sequence number is preferred over timestamps for ordering and paging because timestamps can collide or arrive out of order across servers; a duplicate seq is rejected by the key (verified: UNIQUE constraint failed). Keyset pagination (seq < :last) stays cheap at any depth, while OFFSET 100000 reads and discards 100000 rows. Read state is stored as a cursor per member (last_read_seq) instead of a read-flag per message per user, which would multiply rows by group size. The cost of this design is that seq must be assigned safely: the usual options are a counter row locked briefly per conversation or a single-writer sequencer per conversation, and either creates a serialization point per busy conversation.
orders(order_id PK, amount_cents, currency, status CHECK IN (created, payment_pending, paid, failed, refunded))
payments(payment_id PK, order_id FK, idempotency_key UNIQUE, provider_ref UNIQUE,
amount_cents, status CHECK IN (initiated, succeeded, failed), created_at)
payment_events(event_id PK, payment_id FK, event, at) -- append-only history
ledger_entries(entry_id PK, txn_id, account, amount_cents) -- each txn_id sums to 0
Client retries the same request (real sqlite3):
INSERT ... idempotency_key 'k-abc' -> ok
INSERT ... idempotency_key 'k-abc' -> UNIQUE constraint failed: payments.idempotency_key
INSERT ... ON CONFLICT (idempotency_key) DO NOTHING -> silently ignored
SELECT COUNT(*) FROM payments; -> 1
After success (payment + order + event + ledger in ONE transaction):
txn_id net order_id order_status payment_status
------ --- -------- ------------ --------------
t-1 0 1 paid succeeded
Provider redelivers a webhook that maps to an existing provider_ref:
UNIQUE constraint failed: payments.provider_refPayments are a retry-heavy domain: clients time out and resend, gateways redeliver webhooks, workers crash mid-flow. The defence is at the schema level. A UNIQUE idempotency key means the same logical request can be safely inserted at most once; the unique provider_ref makes duplicate webhooks harmless. Status columns with a CHECK constraint restrict the states; the allowed transitions themselves are enforced by the guarded update (UPDATE ... SET status='paid' WHERE order_id=? AND status='payment_pending', verify one row changed). Keep history in an append-only table instead of overwriting, and keep a double-entry ledger where every transaction sums to zero so that money never appears or disappears silently. Transaction boundaries matter as much as tables: do not hold a database transaction open across the call to the payment provider. The common pattern is transaction one (insert the initiated payment with its idempotency key, commit), then call the provider outside any transaction passing the same key, then transaction two (record the result, update the order, write the event and ledger rows together).
UNIQUE constraints for other natural identifiers such as email and SKU.NOT NULL, CHECK, FK, UNIQUE); application checks alone can be bypassed by a second service, a script, or a race.(conversation_id, seq) can be both the identity and the read path.-- Run with: sqlite3 :memory: (three independent sessions)
.headers on
.mode column
-- ===== 1. E-commerce =====
PRAGMA foreign_keys = ON;
CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE, name TEXT NOT NULL);
CREATE TABLE products (product_id INTEGER PRIMARY KEY, sku TEXT NOT NULL UNIQUE, name TEXT NOT NULL,
price_cents INTEGER NOT NULL CHECK (price_cents >= 0));
CREATE TABLE inventory (product_id INTEGER PRIMARY KEY REFERENCES products,
on_hand INTEGER NOT NULL CHECK (on_hand >= 0));
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers,
status TEXT NOT NULL CHECK (status IN ('pending','paid','shipped','cancelled')),
ship_city TEXT NOT NULL, total_cents INTEGER NOT NULL CHECK (total_cents >= 0));
CREATE TABLE order_items (order_id INTEGER NOT NULL REFERENCES orders,
product_id INTEGER NOT NULL REFERENCES products, qty INTEGER NOT NULL CHECK (qty > 0),
unit_price_cents INTEGER NOT NULL, PRIMARY KEY (order_id, product_id));
CREATE INDEX idx_orders_customer ON orders (customer_id, order_id);
INSERT INTO customers VALUES (1,'[email protected]','Asha'),(2,'[email protected]','Ravi');
INSERT INTO products VALUES (100,'KB-1','Keyboard',5000),(101,'MS-1','Mouse',2000);
INSERT INTO inventory VALUES (100,10),(101,1);
BEGIN;
INSERT INTO orders VALUES (1,1,'pending','Pune',0);
INSERT INTO order_items SELECT 1, product_id, 2, price_cents FROM products WHERE product_id=100;
INSERT INTO order_items SELECT 1, product_id, 1, price_cents FROM products WHERE product_id=101;
UPDATE orders SET total_cents = (SELECT SUM(qty*unit_price_cents) FROM order_items WHERE order_id=1) WHERE order_id=1;
UPDATE inventory SET on_hand = on_hand - 2 WHERE product_id=100;
UPDATE inventory SET on_hand = on_hand - 1 WHERE product_id=101;
COMMIT;
UPDATE products SET price_cents = 6000 WHERE product_id=100; -- price change after the sale
SELECT o.order_id, o.total_cents, p.name, oi.qty, oi.unit_price_cents AS charged, p.price_cents AS current_price
FROM orders o JOIN order_items oi USING(order_id) JOIN products p USING(product_id);
-- order_id total_cents name qty charged current_price
-- 1 12000 Keyboard 2 5000 6000
-- 1 12000 Mouse 1 2000 2000
UPDATE inventory SET on_hand = on_hand - 1 WHERE product_id=101;
-- Runtime error: CHECK constraint failed: on_hand >= 0
INSERT INTO orders VALUES (2,99,'pending','Pune',0);
-- Runtime error: FOREIGN KEY constraint failed
INSERT INTO orders VALUES (3,2,'refunded','Pune',0);
-- Runtime error: CHECK constraint failed: status IN (...)
INSERT INTO customers VALUES (3,'[email protected]','Asha2');
-- Runtime error: UNIQUE constraint failed: customers.email
-- ===== 2. Messaging (new session) =====
PRAGMA foreign_keys = ON;
CREATE TABLE users (user_id INTEGER PRIMARY KEY, handle TEXT NOT NULL UNIQUE);
CREATE TABLE conversations (conversation_id INTEGER PRIMARY KEY, kind TEXT NOT NULL CHECK (kind IN ('direct','group')));
CREATE TABLE conversation_members (conversation_id INTEGER NOT NULL REFERENCES conversations,
user_id INTEGER NOT NULL REFERENCES users, last_read_seq INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (conversation_id, user_id));
CREATE INDEX idx_members_user ON conversation_members (user_id, conversation_id);
CREATE TABLE messages (conversation_id INTEGER NOT NULL REFERENCES conversations, seq INTEGER NOT NULL,
sender_id INTEGER NOT NULL REFERENCES users, body TEXT NOT NULL, sent_at TEXT NOT NULL,
PRIMARY KEY (conversation_id, seq)) WITHOUT ROWID;
INSERT INTO users VALUES (1,'asha'),(2,'ravi'),(3,'meera');
INSERT INTO conversations VALUES (1,'direct'),(2,'group');
INSERT INTO conversation_members VALUES (1,1,0),(1,2,0),(2,1,0),(2,2,0),(2,3,0);
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM n WHERE i<8)
INSERT INTO messages SELECT 2, i, 1+(i%3), 'msg '||i, '2026-01-01T10:0'||i FROM n;
SELECT seq, sender_id, body FROM messages WHERE conversation_id=2 ORDER BY seq DESC LIMIT 3;
-- 8 msg 8 / 7 msg 7 / 6 msg 6 (sender_id 3, 2, 1)
SELECT seq, sender_id, body FROM messages WHERE conversation_id=2 AND seq < 6 ORDER BY seq DESC LIMIT 3;
-- 5 msg 5 / 4 msg 4 / 3 msg 3
SELECT m.conversation_id, (SELECT COUNT(*) FROM messages x
WHERE x.conversation_id=m.conversation_id AND x.seq>m.last_read_seq) AS unread
FROM conversation_members m WHERE m.user_id=3;
-- conversation_id 2, unread 8
EXPLAIN QUERY PLAN SELECT seq, body FROM messages WHERE conversation_id=2 AND seq < 6 ORDER BY seq DESC LIMIT 3;
-- SEARCH messages USING PRIMARY KEY (conversation_id=? AND seq<?)
INSERT INTO messages VALUES (2,8,1,'dup','x');
-- Runtime error: UNIQUE constraint failed: messages.conversation_id, messages.seq
-- ===== 3. Order / payment (new session) =====
PRAGMA foreign_keys = ON;
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, amount_cents INTEGER NOT NULL CHECK (amount_cents>0),
currency TEXT NOT NULL,
status TEXT NOT NULL CHECK (status IN ('created','payment_pending','paid','failed','refunded')));
CREATE TABLE payments (payment_id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL REFERENCES orders,
idempotency_key TEXT NOT NULL UNIQUE, provider_ref TEXT UNIQUE,
amount_cents INTEGER NOT NULL CHECK (amount_cents>0),
status TEXT NOT NULL CHECK (status IN ('initiated','succeeded','failed')), created_at TEXT NOT NULL);
CREATE TABLE payment_events (event_id INTEGER PRIMARY KEY, payment_id INTEGER NOT NULL REFERENCES payments,
event TEXT NOT NULL, at TEXT NOT NULL);
CREATE TABLE ledger_entries (entry_id INTEGER PRIMARY KEY, txn_id TEXT NOT NULL, account TEXT NOT NULL,
amount_cents INTEGER NOT NULL);
INSERT INTO orders VALUES (1,12000,'INR','created');
INSERT INTO payments (order_id,idempotency_key,amount_cents,status,created_at) VALUES (1,'k-abc',12000,'initiated','t1');
INSERT INTO payments (order_id,idempotency_key,amount_cents,status,created_at) VALUES (1,'k-abc',12000,'initiated','t2');
-- Runtime error: UNIQUE constraint failed: payments.idempotency_key
INSERT INTO payments (order_id,idempotency_key,amount_cents,status,created_at) VALUES (1,'k-abc',12000,'initiated','t2')
ON CONFLICT (idempotency_key) DO NOTHING; -- no error, no new row
SELECT COUNT(*) AS payment_rows FROM payments; -- 1
BEGIN;
UPDATE payments SET status='succeeded', provider_ref='pi_123' WHERE idempotency_key='k-abc';
UPDATE orders SET status='paid' WHERE order_id=1;
INSERT INTO payment_events (payment_id,event,at) VALUES (1,'succeeded','t3');
INSERT INTO ledger_entries (txn_id,account,amount_cents) VALUES ('t-1','customer_receivable',-12000),('t-1','revenue',12000);
COMMIT;
SELECT txn_id, SUM(amount_cents) AS net FROM ledger_entries GROUP BY txn_id; -- t-1 net 0
SELECT o.order_id, o.status AS order_status, p.status AS payment_status FROM orders o JOIN payments p USING(order_id);
-- 1 paid succeeded
INSERT INTO payments (order_id,idempotency_key,provider_ref,amount_cents,status,created_at) VALUES (1,'k-zzz','pi_123',12000,'succeeded','t4');
-- Runtime error: UNIQUE constraint failed: payments.provider_ref
-- PostgreSQL (not run here): same idempotent insert
-- INSERT INTO payments (...) VALUES (...) ON CONFLICT (idempotency_key) DO NOTHING RETURNING payment_id;
-- an empty result means "already exists": read the existing row instead of charging again.
Q: How would you design a database for an e-commerce system?
A: I would start from the entities and cardinalities: customers 1:N orders, orders M:N products via an order_items junction table, products 1:1 inventory, orders 1:N payments. Constraints go in the schema (unique email and SKU, foreign keys, CHECK on stock and status), money is stored in integer minor units, and order_items snapshots the unit price and the order snapshots the shipping address so later edits do not rewrite history. Stock is decremented with an atomic conditional update, and the main indexes come from the read paths such as orders(customer_id, order_id). The trade-off is that snapshots and derived totals are intentional redundancy that I have to keep consistent, and a single hot inventory row can become a contention point during flash sales.
Q: How would you design a database for a messaging application?
A: Model users, conversations, membership and messages, and key messages by (conversation_id, seq) where seq is a per-conversation increasing number, so the latest page is a short range scan and older pages use keyset pagination instead of OFFSET. Read state is a per-member last_read_seq cursor rather than a flag per message per recipient. Because access is nearly always within one conversation, conversation_id is the natural shard or partition key, and message stores are commonly built on partitioned wide-column or key-value systems for that reason, though a sharded relational database works too. The costs are that seq assignment serializes writes within one very busy conversation, a giant group can become a hot partition, and cross-conversation queries such as global search need a separate index.
Q: How would you design a database for an order and payment system?
A: Separate orders from payments, give each payment a unique idempotency key and the provider's reference a unique constraint, restrict statuses with CHECK and move them only through guarded updates, and record history in an append-only event table plus a double-entry ledger. A payment attempt is split into short transactions around the external call: commit an initiated row, call the provider outside any transaction with the same idempotency key, then commit the result, order status, event and ledger rows together. The trade-off is that the system is deliberately eventually resolved between the two commits, so it needs a reconciliation job for payments stuck in initiated, in exchange for never holding locks across a slow network call.
Follow-up: Surrogate key or natural key as the primary key?
A: A surrogate key (integer or UUID) is stable and small when referenced by many foreign keys, while natural identifiers like email or SKU can change and are kept as UNIQUE constraints instead. Natural keys are still a good choice for junction tables, where the pair of foreign keys is the identity. Random UUIDs as primary keys can hurt insert locality on B-tree indexes, particularly in engines that cluster on the primary key, so time-ordered variants are often preferred; the extra cost is a slightly larger key.
Follow-up: Two customers buy the last unit at the same moment. What does the schema do?
A: With CHECK (on_hand >= 0) and an atomic UPDATE ... SET on_hand = on_hand - 1 WHERE product_id = ? AND on_hand >= 1, the database serializes the two updates on that row: the second sees zero rows affected (or violates the check) and the application reports "out of stock". Reading the stock first and then writing a computed value is the buggy version because both can read 1. The trade-off is that all buyers of that product now contend on one row, which can be relieved with reservations or sharded counters at the cost of more complex accounting.
Follow-up: Would you use soft deletes?
A: Only when a business requirement needs recoverability or audit, because soft deletes leak into every query (WHERE deleted_at IS NULL), complicate unique constraints (a deleted email still occupies the unique slot unless you use a partial or filtered unique index, which PostgreSQL and SQL Server support and MySQL does not natively), and grow tables forever. For financial data an append-only history table is usually a better fit than flagging rows as deleted.
In a design interview, schema design is where you make the scale story concrete: for each table state its primary access pattern, its growth rate, and its partition key. The e-commerce catalog is read-heavy and cacheable while inventory and orders are write-contended and need transactional guarantees, so they may sit in the relational system of record with search and product pages served from derived read models. The messaging design shows why the primary key doubles as a physical layout decision and why a shard key must match the access pattern: sharding by conversation_id keeps a conversation on one node but makes a huge group chat a hot partition. The payment design shows the difference between local atomicity (one database transaction) and business-level atomicity across services, which is handled with idempotency keys, outbox events and reconciliation rather than a distributed transaction. The same habits carry to AI systems: an embeddings table needs a stable source id, a version or content hash, and a tenant column so stale or cross-tenant rows can be found and removed, exactly like the snapshot and idempotency thinking above.
Every schema decision trades something. Strong constraints make the data trustworthy but reject writes that a flexible pipeline might have wanted to accept, and they cost a little write time; skipping them moves correctness into every client forever. Normalization prevents contradictions but adds joins, and denormalized totals or copies speed up reads but need a maintenance mechanism and a reconciliation query. Snapshots preserve history at the cost of storage and of two places that look like they hold the same value. Composite keys like (conversation_id, seq) give excellent locality for the main read but make secondary lookups need extra indexes, and they tie the physical layout to one access pattern, which is the wrong choice when that pattern changes. Over-designing up front is also a failure: adding partitioning, ledgers, and event tables to a system with a thousand orders a day spends complexity you do not need, so the right depth follows the requirements and the expected growth. Finally, engines differ, so mention the engine when it matters: PostgreSQL does not automatically index foreign key columns while InnoDB does, partial unique indexes exist in PostgreSQL but not natively in MySQL, and the idempotent insert syntax is ON CONFLICT DO NOTHING in PostgreSQL and SQLite but INSERT IGNORE or ON DUPLICATE KEY UPDATE in MySQL.