Design a URL Shortener: Data Model and APIs

Why This Section Decides Close Calls

Once the high-level architecture and capacity numbers are on the whiteboard (see the companion articles in this series), the interview often narrows into the data model and API contracts. This is where “I understand distributed systems in the abstract” gets tested against “I’ve actually designed a production schema and API before.” Sloppy field choices or a hand-wavy rate limiter here can turn a borderline-hire into a no-hire, even after a strong architecture discussion.

The Core Table Schema

At minimum, the mapping table needs:

CREATE TABLE urls (
    short_code   VARCHAR(10)   PRIMARY KEY,
    long_url     VARCHAR(2048) NOT NULL,
    user_id      BIGINT        NULL,        -- NULL for anonymous
    created_at   TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at   TIMESTAMP     NULL,        -- NULL = never expires
    is_active    BOOLEAN       NOT NULL DEFAULT TRUE,
    is_custom    BOOLEAN       NOT NULL DEFAULT FALSE
);

CREATE INDEX idx_user_id ON urls(user_id);
CREATE INDEX idx_expires_at ON urls(expires_at) WHERE expires_at IS NOT NULL;

Design notes worth stating explicitly in an interview:

• short_code as primary key, not an auto-increment ID with a separate unique index on short_code. The lookup pattern is always by short_code, so it should be the clustering key.
• long_url capped at 2048 characters, matching the de facto max URL length most browsers and servers support — say this number, it signals attention to real-world constraints.
• is_active as a soft-delete flag rather than hard-deleting rows, so a “deleted” link can return a clean 410 Gone instead of an ambiguous 404, and so analytics history isn’t destroyed.
• A partial index on expires_at (only indexing non-null values) keeps the expiration-sweep query cheap without bloating the index with permanent links.

A Separate Table for Click Analytics

Click events are high-volume, append-only, and have a completely different access pattern than the mapping table (write-heavy, rarely read individually, mostly aggregated). Never bolt a click_count counter column onto the urls table and increment it synchronously on every redirect — that turns your hottest read path into a write-contention bottleneck on the exact row every concurrent reader is also hitting.

CREATE TABLE click_events (
    id           BIGINT        PRIMARY KEY,
    short_code   VARCHAR(10)   NOT NULL,
    clicked_at   TIMESTAMP     NOT NULL,
    referrer     VARCHAR(500)  NULL,
    user_agent   VARCHAR(500)  NULL,
    ip_hash      VARCHAR(64)   NULL         -- hashed for privacy, not raw IP
);

CREATE INDEX idx_short_code_time ON click_events(short_code, clicked_at);

Click events should be written asynchronously — the redirect response fires an event onto a queue (Kafka, SQS, or even a simple in-memory batch buffer), and a separate consumer writes to click_events and periodically rolls up aggregates (hourly/daily click counts per short_code) into a summary table for fast dashboard reads. This decoupling is exactly what keeps the read path’s latency low, as established in the companion architecture article.

REST API Design

Create a short URL

POST /api/v1/urls
Content-Type: application/json

{
  "long_url": "https://example.com/some/very/long/path?with=query",
  "custom_alias": "my-launch",     // optional
  "expires_in_days": 30            // optional, null = never
}

Response 201 Created
{
  "short_url": "https://sho.rt/my-launch",
  "short_code": "my-launch",
  "long_url": "https://example.com/some/very/long/path?with=query",
  "expires_at": "2026-08-15T00:00:00Z"
}

Redirect

GET /{short_code}

Response 302 Found
Location: https://example.com/some/very/long/path?with=query

Get analytics for a link

GET /api/v1/urls/{short_code}/stats

Response 200 OK
{
  "short_code": "my-launch",
  "total_clicks": 4821,
  "clicks_last_7_days": 612,
  "top_referrers": ["twitter.com", "direct", "linkedin.com"]
}

Deactivate a link

DELETE /api/v1/urls/{short_code}

Response 204 No Content

Use DELETE semantically for deactivation even though the implementation is a soft-delete (is_active = false) — this is a deliberate API design choice worth naming: the client-facing contract should match REST conventions even when the internal implementation is more nuanced.

Rate Limiting

Rate limiting protects two very different things and needs two different strategies:

1. The create endpoint (POST /api/v1/urls) — protect against abuse (spam link generation, scraping the short-code namespace). Apply a per-user or per-IP token bucket, e.g., 100 creates/hour for anonymous users, higher for authenticated accounts. Return 429 Too Many Requests with a Retry-After header.
2. The redirect endpoint (GET /{short_code}) — this must almost never be rate limited per-user, since a viral link can legitimately receive thousands of requests per second from thousands of distinct users. Instead, protect against abuse patterns like a single IP hammering the same short_code (possible scraping or DoS attempt), using a much looser, higher-threshold limiter that only kicks in for clearly anomalous patterns.

A token-bucket or sliding-window-log algorithm implemented in Redis (using INCR + EXPIRE on a per-user or per-IP key) is the standard, interview-ready answer. Mention that the rate limiter itself should sit in front of the write path specifically — never throttle the read/redirect path the same way, and be ready to explain why if asked.

SQL vs. NoSQL: Making the Call

This is one of the most commonly asked follow-ups, and the honest answer is “either works, but here’s the tradeoff”:

The defensible interview answer: for the urls mapping table, a simple key-value store (DynamoDB, Cassandra, or even Redis as a durable primary with persistence) is a very natural fit, since every access is a point lookup by short_code with no joins and no complex queries. But at the storage/QPS scale established in the companion capacity estimation article (~300GB, ~1,000 peak read QPS), a well-indexed relational database like Postgres handles this workload without breaking a sweat, and gives you ACID guarantees on writes for free plus a team that already knows how to operate it. Say explicitly: “I’d start with Postgres because the scale doesn’t demand NoSQL yet, and I’d only migrate to a distributed KV store if growth projections showed us blowing past single-primary write throughput.” That answer shows judgment, not just knowledge of options.

For the high-write-volume click_events table, the calculus shifts — a wide-column or time-series-oriented store (Cassandra, or a managed service like DynamoDB with TTL for auto-expiring old raw events) is often the better fit once click volume gets large, since it’s an append-heavy, rarely-updated, time-partitioned workload — a different shape than the mapping table.

Wrapping Up

The data model and API layer is where architectural theory becomes concrete engineering decisions: what fields actually belong in each table, how analytics writes get decoupled from the hot redirect path, how rate limiting differs by endpoint, and — critically — a reasoned, scale-aware answer to SQL vs. NoSQL rather than a reflexive one. Nail these details and you demonstrate you’ve actually built systems like this, not just studied them.

For 50+ system design deep-dives like this one, see The 0-to-1 SWE Interview Playbook (Amazon: https://www.amazon.com/dp/B0H256Z1MF?tag=sirjohnnymai-20). It includes full schema and API designs for a dozen other classic interview questions using this same requirements-to-implementation methodology.

This completes the four-part series — read “System Design Interview Guide” for the requirements framework, “Architecture Diagram and Data Flow” for the request lifecycle, and “Capacity Estimation” for the numbers behind every decision made here.