2. The `Link` record

Description

Everything Enlace stores fits in a five-field record. In this lesson you design it for real: which column, of what type, of what size, and —the question that separates a considered design from a copied one— why that one and not another. By the end you'll have the Link table with its PostgreSQL DDL, you'll know why the short_code is the primary key and not just any column, and you'll be able to derive with a sum the famous "~1 KB per record" you used in module 2 to compute the ~6 TB. That number wasn't decoration: it's the sum of the sizes of these five columns, and today you do it.

Designing the record is more important than its size suggests. It's the decision the rest of the system rests on: the store you choose (lesson 3) depends on what fields there are and how they're queried; the code generator (lessons 4 to 8) fills the short_code field; the cache (module 4) stores copies of this record; the replicas (module 5) copy it. A well-designed record makes everything else natural. A record with one extra column —or the wrong type on short_code— is paid for on every one of the 4,000 queries per second.

Connection to the module: this lesson fixes the what's stored; the next one (lesson 3) decides where. The two together are the foundation of the data model, before lessons 4 to 7 attack the code generation. Here you'll see the record for the first time with concrete database types, not just the Python dataclass that appeared in lesson 1. And you'll come across an idea that repeats throughout system design: the primary key isn't an administrative detail, it's the access pattern made into a column. In Enlace, the access pattern is "lookup by short_code", so short_code is the key. That pairing —access ↔ key— is what lets lesson 3 say "this is literally a dictionary".

The locker's label

Let's go back to the warehouse of lockers from the previous lesson. Each locker holds one thing (the long URL) but also has a label stuck on it with several pieces of data: the ticket's number, the date it was occupied, until when it's reserved, and a mark of how many times it's been opened. The Link record is exactly that label: not just the contents, but the metadata Enlace needs to operate the locker.

Think about what information you really need for the warehouse to work, and you'll see the five fields come out almost on their own. To return the contents when a ticket arrives, you need the ticket's number (short_code) and the contents (long_url): without that there's no service. To know when a ticket expires —because Enlace offers links with expiration— you need a deadline (expires_at). To be able to sort or clean up by age —delete old links, for example— you need to know when it was created (created_at). And for the basic analytics the shortener promises —"your link received 1,240 visits"— you need a counter (clicks). Five pieces of data, each with an operational reason. No "just in case".

Notice what the label does not carry, because designing is as much removing as adding. It doesn't carry the name of the user who created it (Enlace, in its canonical version, doesn't model users; if it did, it would be another table, and the "over-design" boundary we saw in lesson 1 would activate). It doesn't carry the geographic location of each click (that would be a separate events table, the size of serious analytics, and it takes us out of the core). The label is deliberately thin. That thinness is what, in lesson 3, will make a key-value store look so natural.

The five fields, one by one

Here is the record with the PostgreSQL types in place, and below the why of each decision. The identifiers are in English, like all the code in the guide; the explanation, in Spanish.

-- The Link record, as a PostgreSQL table.
CREATE TABLE links (
    short_code   varchar(7)   PRIMARY KEY,           -- the "ticket": 7 chars base62
    long_url     varchar(2048) NOT NULL,             -- the original URL
    created_at   timestamptz  NOT NULL DEFAULT now(),-- when it was shortened
    expires_at   timestamptz,                        -- when it expires (NULL = never)
    clicks       bigint       NOT NULL DEFAULT 0     -- how many times it was resolved
);

short_code varchar(7) PRIMARY KEY — the ticket, and the key. It's the heart of the record. It's a string of exactly 7 characters of the base62 alphabet (0-9, a-z, A-Z), like aX9kR2q. Two decisions live here. The first: it's the primary key, not just another column. That means the database guarantees it's unique (there can't be two rows with the same short_code) and —key for performance— builds the main index on it, so that "find me the row with short_code = 'aX9kR2q'" is a direct operation, not a scan of the whole table. Since Enlace's dominant access pattern is precisely that lookup, making short_code the primary key is aligning the data structure with the usage. The second decision: varchar(7), not text or varchar(255). The code is always 7, so reserving more is lying to the schema about what you store.

long_url varchar(2048) NOT NULL — the contents. The original URL. Module 2 established that the average URL weighs ~500 bytes, but the average isn't the maximum: there are URLs with many tracking parameters that go into the thousands of characters. The 2048 limit is a practical convention (many browsers and servers historically truncated around there); you could use text to have no cap. It's NOT NULL because a link without a destination isn't a link: if there's no long_url, there's nothing to resolve, and allowing NULL here would allow a meaningless record.

created_at timestamptz NOT NULL DEFAULT now() — the birth date. When the link was shortened. The type is timestamptz (timestamp with time zone), not plain timestamp: storing the instant with its zone avoids the class of bug where a server in one zone and another in another disagree about "when did this happen". The DEFAULT now() makes the database put the date for you on insert, so the application code doesn't have to remember. It serves to sort by age and for cleanup policies ("delete what was created more than 5 years ago", which is exactly module 2's retention).

expires_at timestamptz — the expiration, optional. When the link stops working. It's the only field that admits NULL, and that NULL means something: "this link never expires". Many links are permanent; some (a promotion, a single-use link) expire. Modeling the absence of expiration as NULL is more honest than inventing a far-off date like "year 9999". When a ticket arrives, Enlace checks: if expires_at is not NULL and has already passed, the link is dead (it responds 404/410 instead of redirecting). The lesson 8 project implements that check.

clicks bigint NOT NULL DEFAULT 0 — the visit counter. How many times the link has been resolved. It starts at 0 and rises on each redirect. Why bigint (64 bits) and not int (32 bits)? Because a signed int reaches up to ~2,147 million, and a viral link can exceed that; bigint reaches ~9.2 × 10¹⁸, which never runs out in practice. It's a case of "the small type seems enough until the day of the link that goes viral". A design warning is worth it, one that's paid for in module 4: incrementing clicks on each of the 4,000 reads per second turns a read operation into a write one, and that fights with the cache. In real systems the click count is usually done separately (a buffer that flushes every so often), but that's the topic of analytics and the write path; here the field exists and its type is the correct one.

Worked example: where the "~1 KB per record" comes from

In module 2 you used "~1 KB per record" to compute the ~6 TB at 5 years. I promised that number wasn't magic. Let's derive it by summing the field sizes, with Python doing the arithmetic so we don't go wrong:

# Approximate size of a Link record, field by field (bytes).
sizes = {
    "short_code": 7,      # 7 ASCII chars, 1 byte each
    "long_url":   500,    # the AVERAGE URL from module 2 (~500 bytes)
    "created_at": 8,      # 8-byte timestamp
    "expires_at": 8,      # 8-byte timestamp
    "clicks":     8,      # 8-byte bigint
}
payload = sum(sizes.values())
print("sum of fields        =", payload, "bytes")

# The real record weighs more: the database adds per-row overhead
# (header, pointers, alignment) and the primary-key index.
overhead_factor = 2      # rule of thumb: ~2x for row + index
record = payload * overhead_factor
print("record with overhead ~", record, "bytes  (~", round(record/1024, 2), "KB )")

# And the total at 5 years, module 2's number:
records_5y = 100_000_000 * 12 * 5
# We round to 1 KB/record and, as in module 2, treat 1 KB ≈ 1000
# bytes (napkin convention, powers of 10).
total_bytes = records_5y * 1000
print("records at 5 years   =", f"{records_5y:,}")
print("total storage        =", f"{total_bytes / 1000**4:.2f} TB")

What to expect. Running this with Python 3.14.0 gives:

sum of fields        = 531 bytes
record with overhead ~ 1062 bytes  (~ 1.04 KB )
records at 5 years   = 6,000,000,000
total storage        = 6.00 TB

There's the "~1 KB". The five fields add up to 531 bytes raw (dominated by the 500-byte URL). But a row in a real database doesn't weigh only its contents: it carries a per-row header, pointers, alignment padding, and above all the primary-key index, which is a separate structure that also takes up space. A reasonable rule of thumb is to multiply by ~2, and that leaves the record at ~1 KB (1062 bytes). Rounding to 1 KB per record —and, as in module 2, taking 1 KB ≈ 1000 bytes in the napkin arithmetic— the 6 billion records of 5 years give 6 TB exactly, the same number you used in the estimation. You didn't memorize it: you derived it from the fields. (If you prefer the "real" KB, 1024 bytes, the total rises only to 6.14 TB: the order of magnitude doesn't move, which is what matters in the napkin.) And now you understand that if tomorrow you decide to store the geolocation of each click in this same record, that "×2" shoots up and the 6 TB go up on you; that's why serious analytics lives in another table.

An honesty about the method: the ×2 factor is a napkin approximation, not an exact measurement of PostgreSQL (which depends on the version, the padding, the TOAST for large values, etc.). But the point of system design isn't to get it right to the byte; it's to know where the order of magnitude comes from so you can defend it and notice when a modeling decision multiplies it. The number that matters —"on the order of 1 KB per record, on the order of 6 TB at 5 years"— is robust.

The primary key is the access pattern made into a column

It's worth pausing on why short_code is the primary key, because it's an idea you'll reuse throughout system design. A table's primary key decides two things: which combination of columns is unique, and on what the index that makes searches fast is built. The question "what should the primary key be?" has a mechanical answer: the column you search by.

In Enlace, 99% of the operations are "give me the row with this short_code". If short_code is the primary key, that search is direct: the database has an index on the key and reaches the row without scanning the others. If instead you had put a numeric internal id as the key and left short_code as a normal column, each resolve would have to search by a non-indexed column —a scan of the whole table, catastrophic at 4,000 per second— unless you also added a unique index on short_code. That is: you'd end up building the index on short_code anyway, plus an id no one uses. Making short_code the primary key up front is recognizing the access pattern and modeling in its favor.

flowchart LR
    A["Request: GET /aX9kR2q"] --> B["resolve('aX9kR2q')"]
    B --> C{{"index by short_code<br/>(the primary key)"}}
    C -->|"direct lookup,<br/>one row"| D["Link row:<br/>long_url, expires_at, clicks"]
    D --> E["redirect 301/302 → long_url"]

This pairing —access by key ↔ primary key short_code— is also what lets lesson 3 say, in full, that Enlace "is literally a dictionary". A dictionary is exactly that: a key that leads directly to a value. The links table, with short_code as the key, is a persistent dictionary. And when something is a dictionary, a key-value store —which doesn't know how to do anything else, but does it very fast— becomes a first-rate option. That's the bridge to the next lesson.

Common mistakes

Using float or the wrong type for clicks. What happens: someone declares clicks as int (32 bits) and months later a viral link exceeds ~2,147 million clicks; the counter overflows and gives a negative number or breaks. Or worse, someone declares it as a floating-point type and the count loses accuracy at large numbers. Why it happens: the type is chosen out of habit ("counters are int") without thinking about the real range. How to detect it: ask yourself "what's the maximum credible value of this column in the system's life?". For clicks of a viral link, it exceeds 2 billion. How to fix it: bigint (64 bits) for counters that can grow with no clear ceiling, and never floating point for something that counts whole things.

Modeling "no expiration" with an invented date. What happens: someone, uncomfortable with NULLs, decides that "the link doesn't expire" is stored as expires_at = '9999-12-31'. Then a cleanup query or a date comparison treats that date as real and produces strange results, or the "magic number" leaks to the interface. Why it happens: NULL is avoided out of dogma ("NULLs are bad") without seeing that here the NULL means something precise: "not applicable". How to detect it: if you have a sentinel date like 9999 in the data, you have a disguised NULL. How to fix it: use NULL when the absence of a value is a legitimate state of the domain —"this link doesn't expire"— and let the logic treat it explicitly (if expires_at is not None and expires_at < now). It's more honest and less fragile.

Putting heavy analytics in the hot record. What happens: to the Link record, which is read 4,000 times per second, someone adds analytics columns —last IP, country, browser, referrer— or, worse, increments clicks synchronously on each read. The result: each redirect, which should be a very cheap read (and cacheable, module 4), becomes a write, and the cache stops helping. Why it happens: "the link has analytics" is confused with "the analytics lives in the same record". How to detect it: if your most frequent read operation also writes, you have a conflict with the cache and with scale. How to fix it: keep the Link record thin and move the analytics to a separate flow (a counter that's aggregated in batches, or an events table). The clicks field can exist, but its update shouldn't be on the synchronous path of each read. This is a preview of module 4.

Exercises

Exercise 1 — Justify each type. For each field of the Link record, write in one sentence why its type is the right one and what would go wrong with a reasonable alternative. Cover all five: short_code, long_url, created_at, expires_at, clicks.

Exercise 2 — Recompute the storage with a model change. Suppose the team decides to store, within the same Link record, three analytics columns: last_country varchar(2) (2 bytes), last_user_agent varchar(256) (~256 bytes on average), and last_ip varchar(45) (~16 bytes). Recompute the record size (with the same ×2 overhead factor) and the total at 5 years. What happened to the 6 TB, and what design lesson do you draw from it?

base = 531                       # the five original fields
extra = 2 + 256 + 16            # last_country + last_user_agent + last_ip = 274
payload = base + extra          # 805 bytes
record = payload * 2            # 1610 bytes (~1.57 KB)
records_5y = 100_000_000 * 12 * 5
total = records_5y * record
print(payload, record, f"{total/1000**4:.2f} TB")
# -> 805 1610 9.66 TB

Exercise 3 — Why short_code and not a numeric id as the primary key? A colleague proposes: "Let's put an autoincrement id bigint primary key, like in every table, and leave short_code as a normal column with a unique index". Argue in two or three sentences what that proposal gains and loses versus using short_code directly as the primary key, for Enlace's access pattern.

Summary and next step

You designed the Link record: five fields, each with an operational reason. short_code varchar(7) is the ticket and the primary key, because Enlace's access pattern is "exact lookup by short_code" and the primary key is that pattern made into a column. long_url varchar(2048) NOT NULL is the contents; created_at timestamptz the birth date; expires_at timestamptz the optional expiration (where NULL means "doesn't expire"); and clicks bigint the counter, in 64 bits so a viral link doesn't overflow it. You derived the "~1 KB per record" by summing the fields (531 bytes) and applying a ×2 of row and index overhead, and you reproduced the 6 TB at 5 years. And you saw, with a model change, how putting analytics in the hot record shoots up the storage —the reason it's kept thin.

Before moving on you should be able to: write the CREATE TABLE links from memory with the correct type in each column; explain why short_code is the primary key (and not an id); say what NULL means in expires_at; and derive the "~1 KB per record" from the fields.

What comes next is deciding where this table lives. Now that you know the record is thin and the access is "key → value", lesson 3 puts on the table the classic question —SQL or NoSQL?— and answers it not by fashion, but by the access pattern you just named. You'll see why "Enlace is literally a dictionary" isn't a metaphor, and what each type of store gains and loses for this case.

Resources

• PostgreSQL documentation — "CREATE TABLE" — the reference for the command with which you defined the links table, including PRIMARY KEY, NOT NULL, and DEFAULT. Read it to see all the constraints you can put at the schema level, not the application level.
• PostgreSQL documentation — "Date/Time Types" — the detail of timestamptz versus timestamp, which is why created_at and expires_at carry a time zone. The section on why to prefer timestamptz almost always is directly applicable to Enlace.
• PostgreSQL documentation — "Numeric Types" — the exact ranges of int (up to ~2,147 million) and bigint (up to ~9.2 × 10¹⁸), which justify choosing bigint for clicks. Seeing the range in the official table makes the decision obvious.
• Designing Data-Intensive Applications, Martin Kleppmann — Chapter 3 — the section on indexes explains why the primary key and its index make the lookup by key direct, which is the foundation of why short_code is the key. It's the theory behind this lesson's diagram.