How to Design a Database Schema: A Practical Guide

What Is a Database Schema?

A database schema defines the structure of a database — the tables, columns, data types, relationships, constraints, and indexes that determine how data is stored and accessed.

Core Principles

Use Appropriate Data Types

• Store numbers as INTEGER, BIGINT, or NUMERIC — not as VARCHAR
• Store dates and timestamps as DATE or TIMESTAMP WITH TIME ZONE — not as strings
• Store money as NUMERIC(10, 2) — not as FLOAT

Normalization

First Normal Form (1NF)

Every column contains atomic (indivisible) values. No repeating groups in a single column.

Violation: Storing multiple phone numbers as a comma-separated string in one column.

Correct: A separate phone_numbers table with a foreign key to users.

Third Normal Form (3NF)

No non-key column depends on another non-key column (no transitive dependencies).

Relationships

One-to-Many

sqlCopy
CREATE TABLE orders (
  id UUID PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES users(id),
  status TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Many-to-Many

sqlCopy
CREATE TABLE product_categories (
  product_id UUID REFERENCES products(id) ON DELETE CASCADE,
  category_id UUID REFERENCES categories(id) ON DELETE CASCADE,
  PRIMARY KEY (product_id, category_id)
);

Constraints

sqlCopy
-- Not null
email TEXT NOT NULL

-- Unique
email TEXT NOT NULL UNIQUE

-- Check constraint
status TEXT NOT NULL CHECK (status IN ('pending', 'active', 'cancelled'))

-- Foreign key with cascade delete
order_id UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE

Indexing

Always index:

• Foreign key columns (for join performance)
• Columns used in WHERE clauses in frequently-run queries
• Columns used in ORDER BY for paginated queries

sqlCopy
-- Querying orders by user
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Querying orders by status and date
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);

Common Design Mistakes

• Nullable everything: Use NOT NULL by default and be deliberate about nullable columns.
• No timestamps: Almost every table benefits from created_at and updated_at columns.
• Storing derived data carelessly: Maintain derived values explicitly or always recompute them in queries.

Summary

A well-designed database schema uses appropriate data types, enforces constraints at the database level, and is indexed for the queries the application actually runs.