Database design and indexing flashcards

Shape your tables and indexes so data stays right and fast.

Beginner friendly, starts from zero

CardsCheatsheetTips and tricks

Database design and indexingStart from zero · 1 of 12

What is a database, and what does 'database design' mean?

Think of the answer, then tap

A database is an organised store of information that a computer can search fast. Database design is deciding how to arrange that information before you fill it.

Plan the shelves before you stock the shop.

Space turn over · K got it · J again · ← back

All topics

Database design and indexing at a glance

Database design is planning how to store information in tables so it stays correct and is quick to find.

Like a tidy school office: a cabinet for each kind of thing, a card per item with the same boxes, and a roll number on every card.

Words you will meet

Table: A sheet that holds one kind of thing, like customers
Row: One item in a table, like one customer
Column: One detail every row has, like name or phone
Primary key: A value unique to each row, like a roll number
Foreign key: A column that points to a row in another table
Constraint: A rule the database enforces, like 'never empty'
Index: A sorted shortcut that finds rows without reading them all
Schema: The whole plan of tables, columns and rules

The steps

1. Start from zero (12 cards): Tables, rows, keys and links in plain words, then your first table.
2. Core basics (15 cards): Entities, keys, relationships, constraints and what an index really is.
3. Next steps (16 cards): Normal forms, denormalising on purpose, composite indexes and query plans.
4. Go deeper (16 cards): Covering and partial indexes, key choice, partitioning, scaling, history and migrations.

All 12 cards in Start from zero

What is a database, and what does 'database design' mean?: A database is an organised store of information that a computer can search fast. Database design is deciding how to arrange that information before you fill it.
Life before good design: what goes wrong with one giant spreadsheet?: The same customer's phone number gets typed in 50 rows. Change it in one row and the other 49 are now wrong. Good design writes each fact once, in one place.
Where do you already meet databases in daily life?: Your phone contacts, a train ticket booking, your bank statement and a school marks register are all rows in tables inside a database.
What everyday place works like a well designed database?: A school office: one cabinet for students, one for classes, a card per student with the same boxes on every card, and a roll number on each. That plan is called the schema.
What are a table, a row and a column?: A table holds one kind of thing, like customers. Each row is one item (one customer) and each column is one detail every row has, like phone.
What is a key, and what are 'primary' and 'foreign' keys?: A primary key is a value unique to each row, like a roll number. A foreign key is a column that stores another table's primary key to link to it, like a roll number written on a library slip.
How can tables be linked? One-to-many, many-to-many, one-to-one.: One restaurant has many dishes (one-to-many). Students take many courses and courses have many students (many-to-many). A person has one passport (one-to-one).
What do 'type', 'null' and 'constraint' mean?: A type is the kind of value a column holds: a number, text, a date or true and false. Null means no value at all. A constraint is a rule the database enforces, like 'stock can never be below zero'.
What are SQL, a query, an index and a scan?: SQL is the language you use to talk to a database, and a query is one question, like 'find the user with this email'. An index works like the index at the back of a book; without one, the database does a scan, reading every row.
What do you need before starting?: For the ideas, only pen and paper. To try the code, install a free database such as PostgreSQL, and learn a little SQL first if you can.
Your first tiny win: what does this SQL create?: A new table called customers. id holds a big whole number and is the primary key, and name holds text marked NOT NULL, so it can never be left empty.
What will you be able to do after this topic?: Turn a plain idea for an app into tables, pick keys, link tables the right way, add rules that keep data clean, and add indexes that keep it fast.

Database design and indexing cheatsheet

Keys and links

id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY Surrogate key
id uuid DEFAULT uuidv7() Ordered UUID key (PostgreSQL 18)
UNIQUE (email) Natural key rule
REFERENCES customers(id) ON DELETE RESTRICT Foreign key
PRIMARY KEY (student_id, course_id) Junction table key
user_id bigint PRIMARY KEY REFERENCES users(id) One-to-one
CHECK (stock >= 0) Row rule

Normal forms

1NF One value per cell, no repeating groups
2NF No fact depends on part of a composite key
3NF No fact depends on a non-key column
BCNF Every determinant is a candidate key
Denormalise Copy on purpose, with an owner and refresh path

Index types

USING btree (default) Equality, ranges, sorting
USING hash Equality only
USING gin (payload jsonb_path_ops) jsonb, arrays, full text
USING gin (name gin_trgm_ops) LIKE '%word%'
USING gist (during) Ranges, geometry, overlaps
USING brin (created_at) Huge append-only tables
USING hnsw (embedding vector_cosine_ops) Vector similarity (pgvector)

Index design

(eq_col, range_col) Equality first, range or sort last
(a, b, c) serves a / a,b / a,b,c Leftmost prefix rule
... INCLUDE (status, total) Covering, index-only scan
... WHERE status = 'queued' Partial index
ON users (lower(email)) Expression index
CREATE INDEX CONCURRENTLY Build without blocking writes
pg_stat_user_indexes.idx_scan = 0 Unused, consider dropping

Modelling patterns

amount_minor bigint + currency char(3) Exact money
timestamptz (UTC) Instants
local timestamp + IANA zone Future local events
deleted_at + partial unique index Soft delete done right
append-only *_history table Audit trail
tenant_id first + row-level security Multi-tenant SaaS
expand, migrate, contract Zero-downtime schema change