Stop 17 · System Design for Full-Stack AppsTopics · 6/7

Database Design for Full-Stack Apps

Part of the Full Stack Developer Roadmap

In short

A schema that looks neat on paper can make your feed screen run ten queries. Design tables around the screens you actually show, and your API stays simple and fast.

Designing your schema with your API and frontend's actual query needs in mind — not in isolation, since what the frontend needs to display shapes what the backend needs to efficiently query.

The purple blob places a toothbrush and comb at the front of a shelf, with sweaters at the back.

Think of it like this

Packing your hostel shelf

You keep your toothbrush, comb and ID card at the front because you grab them daily. Winter sweaters go at the back. Your tables should work the same way: the data your busiest screen needs should be the easiest to reach.

Watch it happen

From the feed screen to the index

Feed screen

API

Postgres

needs 20 posts

1/6The screen shows post text, author name and like count, newest first. That's the whole requirement.

The big ideas

What you need to know

Screens drive queries

List your main screens and the exact query each needs. Then design tables and indexes so those queries are cheap.

Relations need keys

Use foreign keys and NOT NULL so the database itself refuses broken data, whatever the frontend sends.

Store counts you show often

If every post shows likes, keep a like_count column updated on each like instead of counting on every read.

Index what you sort by

Feeds sort by time and filter by user. Index those columns in the same order the query uses them.

See it in code

Walk through the code

sql

1-- The feed shows: post body, author name, like count, newest first2CREATE TABLE posts (3  id          bigserial PRIMARY KEY,4  author_id   bigint NOT NULL REFERENCES users(id),5  body        text NOT NULL,6  like_count  int NOT NULL DEFAULT 0,   -- kept in sync on like/unlike7  created_at  timestamptz NOT NULL DEFAULT now()8);9CREATE INDEX posts_feed_idx ON posts (created_at DESC, id DESC);10 11-- The exact query the feed endpoint runs12SELECT p.id, p.body, p.like_count, p.created_at, u.name AS author_name13FROM posts p JOIN users u ON u.id = p.author_id14WHERE (p.created_at, p.id) < ($1, $2)15ORDER BY p.created_at DESC, p.id DESC16LIMIT 20;

1/4A foreign key: a post can never point to a user who doesn't exist.

The table and index are shaped by the one query the feed screen runs. Write the query first, then design the table to make it fast.

Save yourself the pain

Common mistakes

Oops

Designing tables first, then making the API call the database once per item on the screen (the N+1 problem).

Do this instead

Write one query per screen using JOINs or a batched IN query, and check it with EXPLAIN.

Oops

Using OFFSET 10000 for deep pages of a busy feed.

Do this instead

Use cursor pagination on an indexed column, so page 500 is as fast as page 1.

Your mission

Query-first schema

~35 min

Quick check

Your feed shows 20 posts, and the API runs one extra query per post to get the author's name. What's the better design?

Go deeper

1Design a schema starting from 'what will the frontend need to render this screen' rather than abstract entities alone.
2Identify a query your planned schema would make awkward or slow, and adjust the design.
3Practice explaining a schema design decision in terms of the user-facing feature it supports.