Database Design Interview Questions: URL Shortener, Chat

In design interviews, you are given a vague product (“design a URL shortener”) and have 30 to 45 minutes to build a schema and discuss trade-offs. There is no single right answer: what matters is a clear process and sensible reasoning. The three designs below include MySQL schemas that were run, so you can see them work.

A process that works

Clarify the requirements and the scale (“How many writes per second? Do we need to delete? Who can read?”).
List the entities and their relationships (nouns and verbs).
Write the tables: keys, types, constraints (Design Process).
Write the main queries and add the indexes that serve them.
Discuss scale: what breaks first, and how you would fix it (caching, partitioning, replicas, sharding).
Mention security, backups, and failure.

Design 1: a URL shortener

Requirements: users create short links for long URLs. Visiting a short code redirects. Count the clicks. Links can expire.

query.sql

SQL

CREATE TABLE links (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(10) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    long_url VARCHAR(2048) NOT NULL,
    owner_id INT UNSIGNED NULL,
    created_at DATETIME NOT NULL,
    expires_at DATETIME NULL,
    click_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    UNIQUE KEY uq_links_code (code),
    KEY idx_links_owner (owner_id, created_at)
);

CREATE TABLE clicks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    link_id BIGINT UNSIGNED NOT NULL,
    clicked_at DATETIME NOT NULL,
    referrer VARCHAR(200) NULL,
    KEY idx_clicks_link_time (link_id, clicked_at),
    CONSTRAINT fk_clicks_link FOREIGN KEY (link_id) REFERENCES links (id)
);

Output

OUTPUT

1
2
3

Query OK, 0 rows affected

Query OK, 0 rows affected

Design points to say out loud:

The code is ascii_bin (case-sensitive: aB3 and Ab3 must be different codes) with a UNIQUE index. The redirect query is a single index lookup on code.
The code can be the id encoded in base 62 (short, no collisions, but guessable), or random with a retry on duplicate. A unique index makes the retry safe (Idempotent Requests).
click_count is a denormalised counter for fast display, and clicks keeps the detail for analytics. A hot link makes the counter row a lock hotspot: batch the increments, or count in a cache and flush periodically.

query.sql

SQL

INSERT INTO links (code, long_url, owner_id, created_at, expires_at) VALUES
('aB3', 'https://example.com/a-very/long/path?x=1', 1, '2026-03-01 10:00:00', NULL),
('Ab3', 'https://example.com/another', 1, '2026-03-01 10:05:00', '2026-03-10 00:00:00');

-- the redirect lookup: exact, case-sensitive match, not expired
SELECT long_url FROM links WHERE code = 'aB3' AND (expires_at IS NULL OR expires_at > '2026-03-05 00:00:00');
SELECT long_url FROM links WHERE code = 'Ab3' AND (expires_at IS NULL OR expires_at > '2026-03-12 00:00:00');

Output

OUTPUT

+------------------------------------------+
| long_url                                 |
+------------------------------------------+
| https://example.com/a-very/long/path?x=1 |
+------------------------------------------+
1 row in set

Empty set

The first lookup finds the link. The second code (Ab3, different from aB3) has expired, so nothing is returned. Scaling notes: the read path is a primary-key-like lookup, so cache hot codes (Redis or memcached) and the database sees few reads. Writes are small. Partition or archive clicks by month, since it grows fastest.

Design 2: a chat application

Requirements: users, conversations (direct or group), messages, a message list that loads the latest messages and scrolls back in history.

query.sql

SQL

CREATE TABLE conversations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    kind ENUM('direct', 'group') NOT NULL,
    title VARCHAR(60) NULL
);

CREATE TABLE participants (
    conversation_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    last_read_message_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (conversation_id, user_id),
    KEY idx_participants_user (user_id),
    CONSTRAINT fk_part_conv FOREIGN KEY (conversation_id) REFERENCES conversations (id) ON DELETE CASCADE
);

CREATE TABLE messages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    conversation_id INT UNSIGNED NOT NULL,
    sender_id INT UNSIGNED NOT NULL,
    body TEXT NOT NULL,
    sent_at DATETIME(3) NOT NULL,
    KEY idx_messages_conversation (conversation_id, id),
    CONSTRAINT fk_msg_conv FOREIGN KEY (conversation_id) REFERENCES conversations (id) ON DELETE CASCADE
);

Output

OUTPUT

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

Design points:

The index (conversation_id, id) makes “the latest N messages of a conversation” an index range scan in reverse order, with no sort.
Keyset pagination for scrolling back: “messages older than the one at the top of the screen” is id < :oldest_loaded, which stays fast however far back the user goes (Pagination in PHP).
last_read_message_id per participant gives unread counts without a row per message per reader.
A direct conversation between two users should exist only once: store the pair in a consistent order (smaller user id first) in a separate unique key, or look it up by participants.

query.sql

SQL

INSERT INTO conversations (kind, title) VALUES ('group', 'Project team');
INSERT INTO participants (conversation_id, user_id, last_read_message_id) VALUES (1, 10, 3), (1, 11, 5), (1, 12, 0);
INSERT INTO messages (conversation_id, sender_id, body, sent_at) VALUES
(1, 10, 'Hello team', '2026-03-01 09:00:00.000'), (1, 11, 'Morning!', '2026-03-01 09:01:10.000'), (1, 10, 'Standup in 5', '2026-03-01 09:02:30.000'),
(1, 12, 'Joining now', '2026-03-01 09:03:00.000'), (1, 11, 'I pushed the fix', '2026-03-01 09:10:00.000'), (1, 10, 'Thanks!', '2026-03-01 09:11:00.000');

-- the newest 3 messages
SELECT id, sender_id, body FROM messages WHERE conversation_id = 1 ORDER BY id DESC LIMIT 3;

-- scroll back: the 3 messages before id 4
SELECT id, sender_id, body FROM messages WHERE conversation_id = 1 AND id < 4 ORDER BY id DESC LIMIT 3;

-- unread messages per participant
SELECT p.user_id, COUNT(m.id) AS unread
FROM participants p LEFT JOIN messages m ON m.conversation_id = p.conversation_id AND m.id > p.last_read_message_id AND m.sender_id <> p.user_id
WHERE p.conversation_id = 1
GROUP BY p.user_id
ORDER BY p.user_id;

Output

OUTPUT

+----+-----------+------------------+
| id | sender_id | body             |
+----+-----------+------------------+
|  6 |        10 | Thanks!          |
|  5 |        11 | I pushed the fix |
|  4 |        12 | Joining now      |
+----+-----------+------------------+
3 rows in set

+----+-----------+--------------+
| id | sender_id | body         |
+----+-----------+--------------+
|  3 |        10 | Standup in 5 |
|  2 |        11 | Morning!     |
|  1 |        10 | Hello team   |
+----+-----------+--------------+
3 rows in set

+---------+--------+
| user_id | unread |
+---------+--------+
|      10 |      2 |
|      11 |      1 |
|      12 |      5 |
+---------+--------+
3 rows in set

User 12 has read nothing and has three unread messages from other people. Scaling notes: the messages table grows without limit, so partition by time or by conversation range, archive old messages, and keep recent ones hot. A very large system shards by conversation_id. Real-time delivery (WebSockets) is not the database’s job: write the message, then push a notification.

Design 3: an API rate limiter

Requirements: allow each API key at most N requests per minute, across several servers.

query.sql

SQL

CREATE TABLE rate_limits (
    api_key CHAR(16) NOT NULL,
    window_start DATETIME NOT NULL,
    request_count INT UNSIGNED NOT NULL,
    PRIMARY KEY (api_key, window_start)
);

Output

OUTPUT

Query OK, 0 rows affected

The fixed window counter: one row per key per minute. A single atomic statement counts the request and tells you the total:

query.sql

SQL

INSERT INTO rate_limits (api_key, window_start, request_count) VALUES ('key-abc', '2026-03-01 10:00:00', 1)
ON DUPLICATE KEY UPDATE request_count = LAST_INSERT_ID(request_count + 1);
SELECT ROW_COUNT() AS row_count;

INSERT INTO rate_limits (api_key, window_start, request_count) VALUES ('key-abc', '2026-03-01 10:00:00', 1)
ON DUPLICATE KEY UPDATE request_count = LAST_INSERT_ID(request_count + 1);
SELECT ROW_COUNT() AS row_count, LAST_INSERT_ID() AS request_number_in_window;

INSERT INTO rate_limits (api_key, window_start, request_count) VALUES ('key-abc', '2026-03-01 10:00:00', 1)
ON DUPLICATE KEY UPDATE request_count = LAST_INSERT_ID(request_count + 1);
SELECT LAST_INSERT_ID() AS request_number_in_window;

Output

OUTPUT

+-----------+
| row_count |
+-----------+
|         1 |
+-----------+
1 row in set

+-----------+--------------------------+
| row_count | request_number_in_window |
+-----------+--------------------------+
|         2 |                        2 |
+-----------+--------------------------+
1 row in set

+--------------------------+
| request_number_in_window |
+--------------------------+
|                        3 |
+--------------------------+
1 row in set

The first call inserts the row (row_count is 1), so this is request number 1. (As in Gap-Free Numbering, LAST_INSERT_ID() is not set by the insert branch, so do not read it after the first call.) The next calls update the row, and LAST_INSERT_ID() returns the new count (2, then 3). The application rejects the request when the count passes the limit. The primary key makes the statement atomic across servers, with no read-then-write race.

Trade-offs to discuss:

A fixed window allows bursts at the boundary (N requests at 10:00:59 and N more at 10:01:00). A sliding window or token bucket smooths it.
Hitting MySQL on every API request is expensive. Real systems use Redis or an in-memory counter, with MySQL for the limits configuration and long-term usage totals. In an interview, say that.
Old windows must be deleted: a scheduled job or an event, in batches (Events).

query.sql

SQL

INSERT INTO rate_limits (api_key, window_start, request_count) VALUES ('key-abc', '2026-03-01 08:59:00', 7);

DELETE FROM rate_limits WHERE window_start < '2026-03-01 09:00:00' LIMIT 1000;
SELECT ROW_COUNT() AS deleted;
SELECT COUNT(*) AS rows_left FROM rate_limits;

Output

OUTPUT

+---------+
| deleted |
+---------+
|       1 |
+---------+
1 row in set

+-----------+
| rows_left |
+-----------+
|         1 |
+-----------+
1 row in set

Questions to expect, and short answers

Question	A good short answer
How do you generate unique short codes?	Encode an auto-increment id (no collisions), or random codes with a `UNIQUE` index and retry
How does it scale to millions of reads per second?	Cache hot keys, add read replicas, shard by key if the data outgrows one server
What if two users create the same short code at the same time?	The unique index lets only one succeed. The other retries with a new code
How would you delete a user’s data?	Foreign keys with `ON DELETE CASCADE` or a purge job in batches, plus backups and replicas (Privacy)
What indexes do you add and why?	For each main query, an index that matches its `WHERE` and `ORDER BY`, and no more (Index Design)
How do you keep the schema evolving?	Versioned migrations, expand and contract, online DDL (Schema Migrations)
What breaks first at scale?	Usually a hot row, a missing index, a very large table, or the connection count

lightbulb

Draw the queries, not just the tables

A schema is only as good as the queries it serves. For each table, write the one or two queries that matter, and say which index each uses.