Relational Databases and Data Modeling for System Design

Every system in this course stores data, and most interview designs spend at least five minutes on the data model. Yet many candidates treat it as an afterthought: a list of tables with an id column each, drawn after the architecture. That is backwards. The data model decides which queries are fast, which are impossible, where contention appears, and later, how the data can be split across machines. A good model makes the architecture simple; a poor one makes every later decision harder.

This article teaches relational modelling for system design. It is not a SQL course. It focuses on the decisions: keys, relationships, constraints, how much to normalise, and above all how to design around access patterns.

• What a database gives you that files and caches do not.
• Tables, rows, primary keys, foreign keys and constraints.
• Modelling one-to-one, one-to-many and many-to-many relationships.
• Normalisation and when to denormalise on purpose.
• ACID transactions, in plain words.
• How to choose id formats: auto-increment, UUIDs, time-ordered ids.
• Designing the model from access patterns, with two worked schemas.
• Connection pooling, the N+1 query problem, and safe schema changes.

You could store data in files or in a cache. A database adds properties that are very hard to build yourself:

• Durability: acknowledged writes survive crashes.
• Querying: find, filter, join and sort data without loading it all.
• Concurrency control: many clients read and write at once without corrupting data.
• Constraints: rules (this email is unique, this order references a real customer) enforced on every write, by every client.
• Transactions: several changes succeed or fail together.

Relational databases (PostgreSQL, MySQL, SQL Server, Oracle and cloud versions of them) provide all five and have done so for decades. They are the right default for most structured application data. Article 17 covers when another kind of database fits better.

1. Tables hold one kind of thing: users, photos, orders.
2. Rows are individual things; columns are their attributes, each with a type.
3. A primary key uniquely identifies each row.
4. A foreign key is a column that refers to the primary key of another table, linking rows.
5. Constraints are rules the database enforces.

CREATE TABLE users (    user_id     BIGINT PRIMARY KEY,    username    VARCHAR(30) NOT NULL UNIQUE,    email       VARCHAR(254) NOT NULL UNIQUE,    created_at  TIMESTAMPTZ NOT NULL DEFAULT now());CREATE TABLE photos (    photo_id    BIGINT PRIMARY KEY,    owner_id    BIGINT NOT NULL REFERENCES users(user_id),    caption     VARCHAR(2200),    object_key  VARCHAR(300) NOT NULL,    status      VARCHAR(12) NOT NULL CHECK (status IN ('uploading', 'processing', 'visible', 'rejected')),    created_at  TIMESTAMPTZ NOT NULL DEFAULT now());

Constraints matter more in system design than they first appear. Consider "usernames must be unique". You could check in application code: "is this name taken? no? insert it". With two servers handling two signups for the same name at the same moment, both checks pass and both inserts succeed. A UNIQUE constraint makes the database reject the second insert, however many servers you run. PostgreSQL's documentation describes the constraint types (check, not-null, unique, primary key, foreign key and exclusion) and how each restricts the data allowed in a table (PostgreSQL: Constraints).

You will see this again and again: a unique constraint is often the simplest correct answer to "how do we prevent duplicates?", including duplicate payments and double-booked seats (articles 20 and 38).

CREATE TABLE follows (    follower_id BIGINT NOT NULL REFERENCES users(user_id),    followee_id BIGINT NOT NULL REFERENCES users(user_id),    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),    PRIMARY KEY (follower_id, followee_id),    CHECK (follower_id <> followee_id));

The composite primary key means a user can follow another only once (no duplicate rows), and the check stops users following themselves. Two rules, enforced for free.

Joins combine related tables at query time:

-- Photos from people that user 42 follows, newest firstSELECT p.photo_id, p.owner_id, p.caption, p.created_atFROM follows fJOIN photos p ON p.owner_id = f.followee_idWHERE f.follower_id = 42 AND p.status = 'visible'ORDER BY p.created_at DESCLIMIT 20;

This query is correct, and for a user who follows a few hundred people it is fine. For a user who follows 5,000 accounts, at 3,500 feed requests per second, it is far too expensive to run on every request. That is not a SQL problem; it is an access pattern problem, and article 35 solves it. Keep this example in mind.

Normalisation means storing each fact in exactly one place. A user's name lives in users, not copied into every photo row. Benefits: no contradictions (you cannot have two different names for one user), smaller storage, simple updates.

Denormalisation means deliberately copying data to make reads faster. Examples:

• A like_count column on photos, instead of running COUNT(*) over millions of likes on every read.
• The owner's username stored with each photo in a feed cache, to avoid a join per item.
• The price at the time of purchase stored on each order line, which is not even a copy but a historical fact: the product's current price will change, and the order must not.

Every denormalised copy needs an answer to "how is it kept in sync, and what if it is briefly wrong?" Options include updating both in one transaction, updating asynchronously from an event, or periodically recomputing. Microsoft describes the general approach of building precomputed read-optimised views as the Materialized View pattern, and notes that such views are typically updated after the source data changes and may be temporarily inconsistent with it (Materialized View pattern).

A good rule: normalise the source of truth; denormalise read paths on purpose.

A transaction groups several operations so they behave as one. ACID names four guarantees:

• Atomicity: all of the operations happen, or none do. A transfer that debits one account and credits another can never stop halfway.
• Consistency: the transaction moves the database from one valid state to another; constraints hold before and after. (This is a different meaning of "consistency" from the replication sense in article 19.)
• Isolation: concurrent transactions do not see each other's half-done work, to a degree set by the isolation level (article 14).
• Durability: once committed, the change survives crashes.

BEGIN;UPDATE accounts SET balance = balance - 500 WHERE account_id = 1 AND balance >= 500;UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;INSERT INTO transfers (from_id, to_id, amount) VALUES (1, 2, 500);COMMIT;

If the server crashes after the first update, atomicity guarantees the database rolls it back. (In real code you would also check that the first update changed exactly one row, and roll back if not.) Transactions inside one database are one of the strongest reasons to choose a relational store. Transactions across several databases or services are much harder; article 21 covers why.

Every row needs an identifier, and the choice affects performance, privacy and future sharding.

RFC 9562 defines UUID versions including the time-ordered version 7, which places a Unix timestamp in the most significant bits so that ids sort roughly by creation time (RFC 9562, Universally Unique IDentifiers).

Reasonable defaults: auto-increment for small, single-database systems; time-ordered ids (UUIDv7 or Snowflake-style) for systems that generate ids on many machines or will be sharded. Do not expose sequential ids publicly if counts or guessability matter; expose a separate random public id if needed. The URL shortener (article 30) and chat system (article 34) revisit id generation.

Here is the most important idea in this article. A database is fast for the queries it is organised for, and slow for others. So the first step of data modelling is not "list the entities", it is list the access patterns:

• What does each API operation read or write?
• With which filters, in which order, how many rows?
• How often (from your estimates)?
• Which ones are latency-critical?

Then shape tables, keys and indexes so that the frequent, latency-critical patterns are cheap. Rare and offline queries can be slower, or served from a separate analytics store.

Why? Because the same data can be organised in very different ways. "Photos by owner, newest first" and "all photos tagged #sunset" and "photos liked by user 42" are all questions about photos, but each needs a different index or table to be fast. You cannot make every possible query fast; you choose the ones that matter.

Access patterns (rates from articles 06 and 07, peak):

Model:

CREATE TABLE photos (    photo_id    BIGINT PRIMARY KEY,                 -- A1    owner_id    BIGINT NOT NULL REFERENCES users(user_id),    caption     VARCHAR(2200),    object_key  VARCHAR(300) NOT NULL,    status      VARCHAR(12) NOT NULL,    like_count  BIGINT NOT NULL DEFAULT 0,          -- A5: denormalised counter    created_at  TIMESTAMPTZ NOT NULL DEFAULT now());CREATE INDEX photos_by_owner ON photos (owner_id, created_at DESC);   -- A2CREATE TABLE likes (    photo_id    BIGINT NOT NULL REFERENCES photos(photo_id),    user_id     BIGINT NOT NULL REFERENCES users(user_id),    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),    PRIMARY KEY (photo_id, user_id)                  -- A4: one like per user per photo);

How each pattern is served:

• A1 by the primary key (and the cache from article 09).
• A2 by the index on (owner_id, created_at), which also supports cursor pagination (article 04).
• A3 cannot be served well by a join at this rate; it needs a precomputed feed (article 35). Recognising that at modelling time is the point.
• A4 by the composite primary key: liking twice is rejected by the database, so the API's idempotent PUT is easy to implement.
• A5 by the denormalised like_count, updated in batches from like events rather than on every like, accepting a few seconds of lag (article 09's write-back idea).
• A6 by follows' primary key (follower_id, followee_id), which serves "who do I follow"; "who follows me" needs a second index on (followee_id, follower_id).
• A7 is analytical: run it on a replica or a separate analytics store, never on the primary at peak.

Access patterns: create an order with several items; show a customer's orders, newest first; show one order with its items; staff list orders awaiting dispatch.

CREATE TABLE orders (    order_id     BIGINT PRIMARY KEY,    customer_id  BIGINT NOT NULL REFERENCES customers(customer_id),    status       VARCHAR(20) NOT NULL,    total_paise  BIGINT NOT NULL CHECK (total_paise >= 0),    created_at   TIMESTAMPTZ NOT NULL DEFAULT now());CREATE INDEX orders_by_customer ON orders (customer_id, created_at DESC);CREATE INDEX orders_by_status   ON orders (status, created_at);CREATE TABLE order_items (    order_id          BIGINT NOT NULL REFERENCES orders(order_id),    line_no           INT NOT NULL,    product_id        BIGINT NOT NULL REFERENCES products(product_id),    quantity          INT NOT NULL CHECK (quantity > 0),    unit_price_paise  BIGINT NOT NULL,     -- price at purchase time, not a live copy    PRIMARY KEY (order_id, line_no));

Design notes worth saying aloud:

• Money is stored as integers in the smallest unit (paise, cents), never as floating point, which cannot represent most decimal amounts exactly.
• The unit price is stored on the line, because the product's price will change later and the order must keep what the customer actually paid.
• The order and its lines are inserted in one transaction, so an order never exists without its items.
• order_items is keyed by order_id first, so "one order with its items" reads rows that sit together, and so the table can later be partitioned by order id without splitting an order (article 16).

Each application server talks to the database through connections. Opening one is expensive (network handshake, authentication, server-side setup), so applications keep a connection pool: a set of open connections reused across requests.

How large should a pool be? Use Little's Law again. If a server sends 300 queries per second and each takes 5 ms on average, it needs about 300 x 0.005 = 1.5 connections busy at a time. A pool of 10 gives plenty of headroom. Pools of 100 per server are rarely needed, and they multiply: 30 servers x 100 = 3,000 connections.

Databases do not cope well with thousands of connections. PostgreSQL, for example, runs a separate server process for each connection, so each one costs memory even when idle. Common fixes:

• Size pools from measured throughput and query time, not from guesses.
• Use a connection pooler between applications and the database (for example PgBouncer for PostgreSQL, or a managed proxy offered by your cloud provider), which lets many client connections share a smaller number of database connections.
• Keep transactions short: a connection held during a slow external call is a connection nobody else can use.

Code that loads 20 photos with one query and then, in a loop, loads each photo's owner with another query sends 21 queries instead of 2. Object-relational mappers (ORMs) make this easy to do by accident. At 3,500 requests per second it turns into 73,500 queries per second. Fix with a join, a batched WHERE user_id IN (...) query, or the ORM's eager-loading feature, and watch the query count per request.

Adding a column, renaming one or splitting a table on a large, busy database can lock tables or break old code still running during a deployment. The safe approach is expand and contract:

1. Expand: add the new column or table in a backward-compatible way (nullable, with no long lock).
2. Deploy code that writes to both old and new, and backfill old rows in small batches.
3. Switch reads to the new structure.
4. Contract: once nothing uses the old structure, remove it.

• Parameterised queries everywhere; never build SQL by joining strings with user input (SQL injection).
• Least privilege: the application's database user can read and write its own tables, not drop them or read other services' data.
• Encryption in transit and at rest; extra protection (or a separate store) for highly sensitive fields.
• Personal data modelled deliberately: know which columns hold it, so it can be protected, exported or deleted on request (article 28).

• Listing tables before listing access patterns.
• Relying on application checks for uniqueness.
• Using floating point for money.
• Copying data "for speed" without a plan to keep copies in sync.
• Serving feeds, timelines and other fan-in queries with large joins at high request rates.
• Running analytics on the primary at peak.
• Huge connection pools on every server.

• Say "let me list the access patterns first", then write three to six of them with rough rates.
• Show the main tables with primary keys, important foreign keys and the indexes that serve the hot patterns.
• Point out one denormalisation and how it stays in sync.
• Point out one constraint that guarantees correctness (a unique key against duplicates).
• Flag any pattern that will not scale as a query, and say how you will handle it later (cache, precompute, separate store).

CREATE TABLE books   (book_id BIGINT PRIMARY KEY, isbn VARCHAR(13) NOT NULL UNIQUE,                      title VARCHAR(500) NOT NULL, author VARCHAR(300) NOT NULL);CREATE TABLE branches(branch_id INT PRIMARY KEY, name VARCHAR(200) NOT NULL);CREATE TABLE copies  (copy_id BIGINT PRIMARY KEY,                      book_id BIGINT NOT NULL REFERENCES books(book_id),                      branch_id INT NOT NULL REFERENCES branches(branch_id));CREATE INDEX copies_by_book ON copies (book_id);CREATE TABLE members (member_id BIGINT PRIMARY KEY, name VARCHAR(200) NOT NULL,                      email VARCHAR(254) UNIQUE);CREATE TABLE loans   (loan_id BIGINT PRIMARY KEY,                      copy_id BIGINT NOT NULL REFERENCES copies(copy_id),                      member_id BIGINT NOT NULL REFERENCES members(member_id),                      borrowed_at TIMESTAMPTZ NOT NULL, due_at TIMESTAMPTZ NOT NULL,                      returned_at TIMESTAMPTZ);CREATE INDEX loans_by_member ON loans (member_id) WHERE returned_at IS NULL;CREATE INDEX loans_overdue   ON loans (due_at) WHERE returned_at IS NULL;-- A copy can have at most one open loan:CREATE UNIQUE INDEX one_open_loan_per_copy ON loans (copy_id) WHERE returned_at IS NULL;

• Next: Database Indexes and Query Performance: How to Make Reads Fast explains how those indexes work and what they cost.
• SQL refresher: SQL for Beginners: Learn to Query a Database With Real Examples.
• Later: concurrency and isolation (article 14), replication (15), sharding (16) and choosing a database (17).