Stop 06 · Databases (SQL & NoSQL)Topics · 5/8

Schema Design

Part of the Full Stack Developer Roadmap

In short

Your database schema outlives your UI. Pages get redesigned every year, but tables stay for a long time. A bit of planning now saves you from painful migrations and weird bugs later.

Planning your tables/collections, fields, and relationships before writing code — the decisions here shape how easy (or painful) your API will be to build later.

The mascot arranges medicine boxes onto neat shelves in a new medical store.

Think of it like this

Stocking a new medical store

Before opening, the chemist decides which shelf holds what: tablets by company, syrups together, a separate register for prescriptions. Do it right and any medicine is found in seconds. Dump everything in one cupboard and every order turns into a search party.

Watch it happen

A small shop's tables

shop_db

users

addresses

orders

order_items

products

1/4Start with the nouns of your app. Each one becomes a table.

The big ideas

What you need to know

Store each fact once

Keep the user's email only in users. If it's copied into orders too, the two will drift apart.

Many-to-many needs a join table

Students and courses, orders and products: add a middle table with both ids, plus anything about the link itself.

Freeze history

Save the price on order_items at checkout. If the product price changes later, old orders must not change.

Design from your API

List the screens and endpoints first. Each GET should be answerable with a simple query or one join.

See it in code

Walk through the code

sql

1CREATE TABLE users (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email TEXT UNIQUE NOT NULL);2CREATE TABLE products (3  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,4  name        TEXT NOT NULL,5  price_paise INT NOT NULL CHECK (price_paise >= 0)6);7CREATE TABLE orders (8  id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,9  user_id BIGINT NOT NULL REFERENCES users(id),10  status  TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'paid', 'shipped')));11CREATE TABLE order_items (12  order_id    BIGINT REFERENCES orders(id) ON DELETE CASCADE,13  product_id  BIGINT REFERENCES products(id),14  qty         INT NOT NULL CHECK (qty > 0),15  price_paise INT NOT NULL, -- price at the time of the order16  PRIMARY KEY (order_id, product_id));

1/4Two independent tables: users and products. Each has its own id.

Money is stored in paise as whole numbers, so there are no rounding surprises.

Save yourself the pain

Common mistakes

Oops

Storing a comma-separated list like tags = 'react,node,sql' in one column.

Do this instead

Use a separate table (or a Postgres array if you never query inside it). Lists in strings can't be indexed or joined well.

Oops

Referencing the product's current price when showing an old order.

Do this instead

Copy the price into order_items when the order is placed. History should not change when prices do.

Your mission

Design a college events app

~35 min

Quick check

A student can join many clubs and a club has many students. How do you model it?

Go deeper

1Design a schema for a real feature, e.g. a blog with posts and comments, before coding it.
2Practice identifying one-to-many and many-to-many relationships and how to model each.
3Consider what your frontend will actually need to query when designing the schema.