Database Design Principles Every Developer Should Know

I used to think database design was just "make some tables and wire them up." Then I spent a weekend migrating a production app because we had let nulls creep into a column that was supposed to be required, and our joins silently dropped half the data. After that, I stopped treating schemas like an afterthought. Your data model is the foundation of your app. If it's shaky, everything on top wobbles.

Start With Reality, Not Tables

The mistake I made early on was opening my editor and creating tables before I truly understood the product. Now I start with user flows and questions:

Who are the actors? (users, admins, vendors)
What do they do? (place orders, leave reviews, request refunds)
What has to be true? (an order always has at least one item; a review must belong to a product and a user)
What needs to be unique? (emails, SKU codes)
What can change, and how often? (user names rarely; inventory levels constantly)

When you can answer those questions plainly, the tables almost suggest themselves.

Keys: Natural, Surrogate, and Composite

I prefer surrogate keys (UUID or bigserial) as primary keys and add unique constraints for natural keys (like email or SKU). Composite keys are useful for junction tables where identity is the pair itself.

-- PostgreSQL examples
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email CITEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE products (
  id BIGSERIAL PRIMARY KEY,
  sku TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL
);

-- Many-to-many via junction table with a composite key
CREATE TABLE product_categories (
  product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  category_id BIGINT NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
  PRIMARY KEY (product_id, category_id)
);

Relationships You'll See Everywhere

One-to-many: users → orders, posts → comments. Use a foreign key and index it.
Many-to-many: posts ↔ tags, products ↔ categories. Use a junction table with a composite PK.
One-to-one: rarer than you think. Often becomes a single table or a child table with a UNIQUE FK.

Always add foreign key constraints. They prevent orphaned data and catch bugs early. I've never regretted adding constraints; I have regretted skipping them.

Normalization vs. Pragmatism

Normalize by default (3NF gets you far), then denormalize specific hotspots when needed. A true story: we rendered an order history page that joined five tables and struggled under load. Adding a cached order_total_cents column (updated by triggers/app code) removed two joins and cut page time by 80%.

Normalize to keep data correct and queries predictable.
Denormalize when you can measure a real gain and accept the write complexity.

Choose Data Types Intentionally

Money: store as integer cents (amount_cents INT) or DECIMAL with scale; never float.
Time: use TIMESTAMPTZ and store UTC; convert at the edges.
Text lookup: consider CITEXT for case-insensitive emails in Postgres.
Enums: DB-native enums or check constraints; document how you evolve them.

Make Constraints Do Real Work

Constraints encode business rules in the only place guaranteed to be checked: the database.

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  status TEXT NOT NULL CHECK (status IN ('pending','paid','shipped','canceled')),
  total_cents INT NOT NULL CHECK (total_cents >= 0),
  placed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  id BIGSERIAL PRIMARY KEY,
  order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
  quantity INT NOT NULL CHECK (quantity > 0),
  unit_price_cents INT NOT NULL CHECK (unit_price_cents >= 0)
);
-- Ensure an order has at least one item (application or trigger-level invariant)

Indexes: Small Changes, Big Wins

Index foreign keys and columns used in WHERE, JOIN, and ORDER BY.
Avoid indexing low-selectivity columns (e.g., boolean flags) unless paired with others.
Use composite indexes that match your most common query patterns.

Measure with EXPLAIN (ANALYZE). Add one index, verify the plan and timing, then move on. Guessing creates index bloat.

Soft Deletes, Audits, and History

Soft delete: add deleted_at TIMESTAMPTZ; filter on WHERE deleted_at IS NULL.
Audits: capture who changed what and when (separate audit tables or CDC tools).
Versioning: for critical records, store immutable history rows instead of overwriting.

Plan for Growth Without Over-Engineering

Design keys and timestamps so future partitioning is possible (by time or tenant).
Keep big blobs (images, PDFs) out of your main tables; store in object storage with URLs.
Don't shard on day one; do leave room to evolve.

Migrations: Evolve Safely

Use migrations for every schema change; never edit prod by hand.
Deploy in backwards-compatible steps: add columns nullable first, backfill, then enforce NOT NULL.
Test migrations against production-sized data in staging; long locks can take you down.

Common Pitfalls I See (And Made)

Letting NULLs through — add NOT NULL where appropriate and defaults for timestamps.
Using FLOAT for money — rounding bugs will haunt you.
No foreign keys — looks fine until you try to delete a user and discover 14 orphaned tables.
Overusing UUIDs everywhere — they're great, but consider sequence PKs where locality helps indexes.
SELECT * in app code — fetch only what you need; it affects performance and coupling.

A Simple, Solid Starting Point

-- Minimal e-commerce core
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email CITEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE products (
  id BIGSERIAL PRIMARY KEY,
  sku TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  price_cents INT NOT NULL CHECK (price_cents >= 0)
);

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id),
  status TEXT NOT NULL CHECK (status IN ('pending','paid','shipped','canceled')),
  total_cents INT NOT NULL CHECK (total_cents >= 0),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  id BIGSERIAL PRIMARY KEY,
  order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id BIGINT NOT NULL REFERENCES products(id),
  quantity INT NOT NULL CHECK (quantity > 0),
  unit_price_cents INT NOT NULL CHECK (unit_price_cents >= 0)
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

Checklist Before You Ship

Primary keys on every table, foreign keys on every relationship.
NOT NULL where appropriate, sensible defaults for timestamps and statuses.
Unique constraints for natural keys (email, sku).
Indexes for common queries; none you can't justify.
Backwards-compatible migrations and a tested rollback plan.

Final Thought

Great database design isn't about memorizing every normal form. It's about modeling the real world faithfully, enforcing the rules that matter, and leaving yourself room to grow. Get those right and the rest is iteration.