Getting the schema right is cheaper than any optimization you can apply later. Getting it wrong is a migration you’ll be paying for in three years.
Prerequisites: Databases Overview, Indexing Time to read: ~24 minutes
You’re storing orders. The obvious first attempt:
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_name TEXT,
customer_email TEXT,
customer_address TEXT,
product_names TEXT, -- "Shoes, Socks, Hat"
total FLOAT,
status TEXT
);
Every one of those columns is a future incident:
customer_email repeated on every order. A customer changes their email; you now have 47 rows
with the old one and no idea which is current.product_names as a comma-separated string. “How many shoes did we sell?” requires string
parsing. A product named “Socks, Wool” breaks it entirely.total FLOAT. 🚨 0.1 + 0.2 = 0.30000000000000004. Your accounts won’t balance, and you will
eventually have a very awkward meeting about it.status TEXT. Someone writes "Shipped", someone else "shipped", a third "SHIPPED". Your
dashboard reports three statuses.DISTINCT over
a text column.Data modeling is the discipline of avoiding all of that up front.
The core rule: every fact should be stored exactly once.
If a customer’s email appears in one place, changing it is one update and it’s always correct. If it appears in 47 places, it will be wrong somewhere.
You don’t need the formal definitions. You need the three checks:
1NF — no repeating groups. One value per cell.
❌ orders(id, product_names) -- "Shoes, Socks, Hat"
✅ order_items(order_id, product_id, quantity)
2NF — no partial dependencies. Every non-key column depends on the whole key.
❌ order_items(order_id, product_id, quantity, product_name)
-- product_name depends only on product_id, not on the full key
✅ order_items(order_id, product_id, quantity) + products(id, name)
3NF — no transitive dependencies. Non-key columns depend on the key, not on each other.
❌ orders(id, customer_id, customer_city, customer_country)
-- country depends on city, not on the order
✅ orders(id, customer_id) + customers(id, city_id) + cities(id, name, country_id)
🎙️ The memorable version: “every non-key attribute depends on the key, the whole key, and nothing but the key.”
Higher forms (BCNF, 4NF, 5NF) exist and you’ll rarely apply them deliberately. 3NF is the practical target.
CREATE TABLE customers (
id BIGSERIAL PRIMARY KEY,
email CITEXT UNIQUE NOT NULL, -- case-insensitive
name TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE addresses (
id BIGSERIAL PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
line1 TEXT NOT NULL,
city TEXT NOT NULL,
country CHAR(2) NOT NULL -- ISO 3166-1
);
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
sku TEXT UNIQUE NOT NULL,
name TEXT NOT NULL,
price_cents INTEGER NOT NULL CHECK (price_cents >= 0) -- integers, not floats
);
CREATE TYPE order_status AS ENUM ('pending','paid','shipped','delivered','cancelled');
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id),
shipping_address_id BIGINT NOT NULL REFERENCES addresses(id),
status order_status NOT NULL DEFAULT 'pending',
total_cents INTEGER NOT NULL CHECK (total_cents >= 0),
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE order_items (
order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
product_id BIGINT NOT NULL REFERENCES products(id),
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price_cents INTEGER NOT NULL, -- ← price AT TIME OF ORDER
PRIMARY KEY (order_id, product_id)
);
CREATE INDEX idx_orders_customer ON orders (customer_id, created_at DESC);
CREATE INDEX idx_order_items_product ON order_items (product_id);
🚨 Note unit_price_cents on order_items. This looks like denormalization — the price is
already on products. It isn’t: it’s a different fact. products.price_cents is “what this
costs today”; order_items.unit_price_cents is “what the customer actually paid.” If you join to
products for historical orders, changing a price silently rewrites your entire sales history.
This distinction — current value vs historical fact — is one of the most commonly missed things in data modeling, and mentioning it in an interview is a genuine signal.
One-to-many — a foreign key on the “many” side.
orders.customer_id → customers.id
Many-to-many — a junction table.
CREATE TABLE product_tags (
product_id BIGINT REFERENCES products(id) ON DELETE CASCADE,
tag_id BIGINT REFERENCES tags(id) ON DELETE CASCADE,
PRIMARY KEY (product_id, tag_id)
);
CREATE INDEX idx_product_tags_tag ON product_tags (tag_id); -- for the reverse lookup
🚨 The composite primary key covers (product_id, tag_id) lookups; you need the second index for
“all products with tag X.” Beginners consistently forget the reverse direction.
One-to-one — usually a sign you should have one table. Legitimate reasons to split: optional data that’s rarely queried, large columns you don’t want in the main row (vertical partitioning), or different access-control requirements.
Self-referencing hierarchies:
CREATE TABLE categories (
id BIGSERIAL PRIMARY KEY,
parent_id BIGINT REFERENCES categories(id),
name TEXT NOT NULL
);
Querying an arbitrary-depth tree needs a recursive CTE:
WITH RECURSIVE tree AS (
SELECT id, name, parent_id FROM categories WHERE id = 5
UNION ALL
SELECT c.id, c.name, c.parent_id FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;
For deep, read-heavy hierarchies, consider materialized paths (/1/5/23/, queryable with a LIKE
prefix) or nested sets (fast reads, painful writes). If traversal is the primary operation, a
graph database may be the right tool.
Types are constraints, and constraints are free correctness.
| Data | Use | Never |
|---|---|---|
| Money | INTEGER cents, or NUMERIC(12,2) |
🚨 FLOAT/DOUBLE — binary floating point can’t represent 0.1 |
| Timestamps | TIMESTAMPTZ (stores UTC, converts on read) |
TIMESTAMP without zone, or a string |
| Dates without time | DATE |
A timestamp at midnight in an unspecified zone |
| Fixed set of values | ENUM or a lookup table + FK |
Free TEXT |
| Country / currency | CHAR(2) / CHAR(3) (ISO codes) |
Full names |
| Booleans | BOOLEAN |
0/1 in an INT, 'Y'/'N' |
| IDs | BIGINT or UUID |
🚨 INT — 2.1 billion arrives faster than you think |
| Variable text | TEXT (Postgres) / VARCHAR |
Arbitrary length caps you’ll regret |
| Semi-structured | JSONB |
A serialized blob in TEXT |
| IP addresses | INET |
TEXT |
🚨 The money one is not pedantry. SELECT 0.1::float + 0.2::float returns
0.30000000000000004. Sum a million of those and your ledger doesn’t balance. Use integer minor
units (cents, paisa, fils) or exact NUMERIC. Every payments engineer has a story about this.
🚨 TIMESTAMPTZ vs TIMESTAMP. TIMESTAMPTZ normalizes to UTC on write and converts on read —
which is what you almost always want. TIMESTAMP stores a wall-clock reading with no context, so
you cannot tell what moment it refers to. Store UTC, convert for display, and record the user’s
timezone separately if you need “9 a.m. their time.”
ENUM vs lookup table: enums are compact and enforced, but adding a value requires DDL (and in
some databases, a table rewrite). A lookup table with a foreign key is more flexible and lets you
attach metadata. For a truly fixed set (order status), enum; for anything the business might extend,
a table.
🚨 The most under-used feature in relational databases.
Application-level validation runs in one application. Constraints run for every writer — your API, a migration script, a data-fix run by an engineer at 2 a.m., a second service, an admin tool.
-- Referential integrity
FOREIGN KEY (customer_id) REFERENCES customers(id)
-- Business rules
CHECK (total_cents >= 0)
CHECK (ends_at > starts_at)
CHECK (status <> 'shipped' OR shipped_at IS NOT NULL)
-- Uniqueness — including partial
CREATE UNIQUE INDEX ON users (email) WHERE deleted_at IS NULL; -- unique among active users
-- Preventing overlaps (Postgres) — this is the double-booking fix
ALTER TABLE bookings ADD CONSTRAINT no_overlap
EXCLUDE USING gist (room_id WITH =, during WITH &&);
⚖️ The argument against foreign keys is that they cost write performance and complicate sharding (you can’t enforce an FK across shards). Both are true. But at anything below very large scale, the correctness is worth far more than the microseconds. Default to having them; remove them deliberately with a reason.
🎙️ “I’d keep foreign keys until we shard. The write overhead is small, and referential integrity enforced by the database is worth more than the equivalent application code — which only protects the paths that go through that application.”
Normalization is the default. Denormalization is an optimization with a maintenance cost, applied deliberately.
Legitimate reasons:
1. Read performance under measured load. A five-table join executed 50,000 times a second may justify a precomputed column.
2. Historical accuracy. As above — unit_price_cents is not denormalization, it’s correctness.
3. Aggregate counters. posts.comment_count avoids COUNT(*) on every page load. 🚨 But now it
can drift — update it in the same transaction, or reconcile periodically.
4. Sharding. Cross-shard joins are impossible, so you duplicate data to keep queries local. → Sharding
The rule: denormalize after measuring, never before. And when you do, write down how the copy stays correct — that’s the part people skip, and it’s why denormalized data drifts.
Alternatives to try first:
Soft deletes. deleted_at TIMESTAMPTZ NULL instead of DELETE.
✅ Recoverable, preserves referential integrity, keeps an audit trail.
❌ 🚨 Every query must remember WHERE deleted_at IS NULL — and one that forgets is a data leak.
Use a view or partial index. Also complicates unique constraints (hence the partial unique index
above), and the table grows forever.
Audit / history tables. Keep a full row-version history in a separate table, written by a trigger or by the application. Essential for anything regulated, and invaluable for debugging.
Temporal validity.
CREATE TABLE prices (
product_id BIGINT REFERENCES products(id),
price_cents INTEGER NOT NULL,
valid_from TIMESTAMPTZ NOT NULL,
valid_to TIMESTAMPTZ -- NULL = current
);
Lets you answer “what was the price on 3 March?” and schedule future changes.
JSONB for genuinely variable attributes.
ALTER TABLE products ADD COLUMN attributes JSONB;
CREATE INDEX idx_attrs ON products USING GIN (attributes);
SELECT * FROM products WHERE attributes @> '{"color": "red"}';
✅ Good for sparse, per-category attributes where a column per attribute is unworkable. 🚨 Bad as a general escape hatch. No type checking, no constraints, harder to query, and it becomes a dumping ground. If a field is queried or validated often, promote it to a real column.
Anti-pattern: EAV (entity-attribute-value). A table of (entity_id, attribute_name, value) to
model arbitrary schemas. It’s flexible and it’s a disaster — every query becomes multiple self-joins,
no type safety, no constraints, and terrible performance. JSONB does this job properly. If you
see EAV in a legacy system, that’s what it was trying to be.
| Decision | Gain | Cost |
|---|---|---|
| Normalize (3NF) | One source of truth; clean updates; integrity | More joins on read |
| Denormalize | Faster reads | Update anomalies; drift; must maintain the copy |
| Foreign keys | Integrity enforced for every writer | Small write overhead; blocks cross-shard |
| Constraints | Correctness that can’t be bypassed | Migrations must handle existing bad data |
| Soft deletes | Recoverable, auditable | Every query must filter; table grows forever |
| JSONB columns | Flexibility for sparse attributes | No type safety; harder to constrain and query |
| ENUM | Compact, enforced | DDL required to add values |
NUMERIC/DECIMAL exists. If you take one thing from this chapter,
take that.EXCLUDE constraints solve the double-booking problem declaratively, at any
isolation level — a genuinely elegant feature that most engineers have never used.
→ Isolation LevelsFLOAT for money. An instant negative signal.INT for IDs on a table that will exceed 2.1 billion rows.unit_price on the order line. That’s not denormalization — it’s a different fact.
Joining to the current product price would rewrite our sales history every time we change a
price.”1. Prove the float problem.
SELECT 0.1::float8 + 0.2::float8; -- 0.30000000000000004
SELECT 0.1::numeric + 0.2::numeric; -- 0.3
SELECT sum(x) FROM (SELECT 0.01::float8 AS x FROM generate_series(1,1000000)) t;
-- Not 10000. Off by a visible amount.
2. Model something real. Take a domain you know — a university course registration system, a food delivery app — and produce a 3NF schema with every constraint you can justify. Then write the ten queries the product would actually need. Where the queries are awkward, the model is wrong — that feedback loop is the whole skill.
3. Break a soft delete. Add deleted_at to a users table with a plain UNIQUE(email). Soft
delete a user, then try to re-register with the same email. It fails. Now use a partial unique index
and watch it work. This is a real bug that ships regularly.
4. Use an exclusion constraint.
CREATE EXTENSION btree_gist;
CREATE TABLE bookings (room_id INT, during TSTZRANGE,
EXCLUDE USING gist (room_id WITH =, during WITH &&));
INSERT INTO bookings VALUES (1, '[2026-07-22 10:00, 2026-07-22 11:00)');
INSERT INTO bookings VALUES (1, '[2026-07-22 10:30, 2026-07-22 11:30)'); -- rejected
unit_price on the order line when products already have a price?FLOAT for money?