Database Schema Design

Good schema design pays dividends for the lifetime of a project. A well-normalized schema with clear relationships and consistent conventions is easy to query, extend, and maintain. A poorly designed one creates confusion, data anomalies, and performance problems that compound over time. The decisions you make at schema design time — column types, constraint names, relationship modeling — are much harder to change later than the queries that run against them.

Normal Forms

Normalization is a set of progressively stricter rules for organizing data to eliminate redundancy. Each rule (normal form) solves a specific class of data anomaly. For most transactional applications, reaching Third Normal Form (3NF) is the right target.

First Normal Form (1NF)

Every column must hold a single atomic value. No repeating groups or multiple values packed into one column. Violating 1NF makes it impossible to query individual values cleanly — you end up with application-layer string splitting instead of SQL.

-- Bad: storing multiple phone numbers in one column
CREATE TABLE contacts (
  id        SERIAL PRIMARY KEY,
  name      TEXT,
  phones    TEXT  -- '555-1234,555-5678' violates 1NF
);

-- Good: separate table for the multi-valued attribute
CREATE TABLE contacts (
  id    SERIAL PRIMARY KEY,
  name  TEXT NOT NULL
);

CREATE TABLE contact_phones (
  id         SERIAL PRIMARY KEY,
  contact_id INT NOT NULL REFERENCES contacts(id),
  phone      TEXT NOT NULL,
  label      TEXT  -- 'mobile', 'home', etc.
);

Second Normal Form (2NF)

Every non-key column must depend on the whole primary key, not just part of it. This only applies when the primary key is composite. A partial dependency means data is being duplicated — if the course name is in the enrollments table, it gets repeated for every student enrolled.

-- Bad: course_name depends only on course_id, not the full (student_id, course_id) key
CREATE TABLE enrollments (
  student_id  INT,
  course_id   INT,
  course_name TEXT,  -- partial dependency — duplicated for every student
  grade       CHAR(1),
  PRIMARY KEY (student_id, course_id)
);

-- Good: move course_name to its own table where it belongs
CREATE TABLE courses (
  id   SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE enrollments (
  student_id INT REFERENCES students(id),
  course_id  INT REFERENCES courses(id),
  grade      CHAR(1),
  PRIMARY KEY (student_id, course_id)
);

Third Normal Form (3NF)

No non-key column should depend on another non-key column (no transitive dependencies). Transitive dependencies cause update anomalies — if the city for a zip code changes, you’d have to update every order row with that zip code rather than one row in a lookup table.

-- Bad: zip_city depends on zip_code, not on order_id
CREATE TABLE orders (
  id       SERIAL PRIMARY KEY,
  zip_code TEXT,
  zip_city TEXT,  -- transitive dependency — violates 3NF
  total    NUMERIC
);

-- Good: normalize zip codes into a lookup table
CREATE TABLE zip_codes (
  zip  TEXT PRIMARY KEY,
  city TEXT NOT NULL
);

CREATE TABLE orders (
  id       SERIAL PRIMARY KEY,
  zip      TEXT REFERENCES zip_codes(zip),
  total    NUMERIC
);

3NF is the standard target for transactional (OLTP) schemas. Going further (BCNF, 4NF) is rarely necessary in practice.

Modeling Relationships

One-to-Many

The most common relationship. The “many” side holds the foreign key. The ON DELETE clause controls what happens to child rows when a parent is deleted — choose based on whether children can exist without their parent.

CREATE TABLE authors (
  id   SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE books (
  id        SERIAL PRIMARY KEY,
  author_id INT NOT NULL REFERENCES authors(id) ON DELETE CASCADE,
  title     TEXT NOT NULL,
  isbn      TEXT UNIQUE
);

Many-to-Many (Junction Tables)

When two entities can each be associated with multiple of the other, you need a junction (bridge) table. The junction table’s primary key is the combination of both foreign keys, which automatically prevents duplicate associations.

CREATE TABLE tags (
  id   SERIAL PRIMARY KEY,
  name TEXT UNIQUE NOT NULL
);

CREATE TABLE article_tags (
  article_id INT REFERENCES articles(id) ON DELETE CASCADE,
  tag_id     INT REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (article_id, tag_id)  -- composite PK prevents duplicate tag assignments
);

Self-Referencing Tables

Used for hierarchies: org charts, category trees, threaded comments. A null foreign key typically indicates the root of the hierarchy.

CREATE TABLE employees (
  id         SERIAL PRIMARY KEY,
  name       TEXT NOT NULL,
  manager_id INT REFERENCES employees(id)  -- NULL for the top-level node
);

-- Query the full hierarchy with a recursive CTE
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL  -- start at the root

  UNION ALL

  SELECT e.id, e.name, e.manager_id, org.depth + 1
  FROM employees e
  JOIN org ON org.id = e.manager_id
)
SELECT depth, name FROM org ORDER BY depth, name;

Naming Conventions

Consistency matters more than the specific convention you pick. A predictable naming scheme means anyone familiar with the conventions can navigate an unfamiliar schema without documentation.

• Table names: lowercase plural snake_case — orders, product_categories
• Column names: lowercase snake_case — first_name, created_at
• Primary keys: id (or table_name_id in some styles)
• Foreign keys: referenced_table_singular_id — customer_id, order_id
• Boolean columns: prefix with is_, has_, can_ — is_active, has_verified_email
• Index names: idx_table_column — idx_orders_customer_id

Standard Columns

Almost every table benefits from created_at and updated_at. They cost almost nothing and provide invaluable debugging information — you can always answer “when was this record created?” and “when was it last changed?” A trigger keeps updated_at accurate without requiring application code to set it.

CREATE TABLE products (
  id          BIGSERIAL PRIMARY KEY,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
  -- domain columns follow
);

-- Trigger keeps updated_at current automatically on every UPDATE
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_products_updated_at
BEFORE UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

Soft Deletes

Instead of permanently deleting rows, mark them deleted so history is preserved and foreign keys remain valid. Soft deletes are especially important when other tables reference the deleted record, or when you need an audit trail of what existed.

ALTER TABLE orders ADD COLUMN deleted_at TIMESTAMPTZ;

-- Mark as deleted instead of removing the row
UPDATE orders SET deleted_at = now() WHERE id = 101;

-- All active-records queries add this condition
SELECT * FROM orders WHERE deleted_at IS NULL;

-- Partial index keeps queries on active records fast without indexing deleted ones
CREATE INDEX idx_orders_active ON orders (customer_id)
  WHERE deleted_at IS NULL;

UUID vs BIGSERIAL for Primary Keys

The right choice depends on whether your system is distributed and whether you want to expose sequential IDs.

Use BIGSERIAL for most tables. Use UUID when you need non-guessable IDs or are merging data from multiple sources.

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE TABLE sessions (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),  -- non-guessable, globally unique
  user_id    BIGINT NOT NULL REFERENCES users(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Schema Migrations

Never edit production schemas by hand. Use a migration tool (Flyway, Liquibase, golang-migrate, Alembic) to version every change. Migrations run in order, are tracked in a version table, and can be applied consistently across development, staging, and production.

Key principles for safe migrations:

• Add columns as nullable first, backfill, then add NOT NULL constraint
• Never rename a column directly — add the new column, backfill, update app code, then drop the old one
• Create indexes CONCURRENTLY to avoid locking the table
• Wrap DDL in transactions where possible

-- Safe pattern for adding a NOT NULL column to a table with existing data
ALTER TABLE users ADD COLUMN preferences JSONB;
UPDATE users SET preferences = '{}' WHERE preferences IS NULL;
ALTER TABLE users ALTER COLUMN preferences SET NOT NULL;
ALTER TABLE users ALTER COLUMN preferences SET DEFAULT '{}';

-- Non-blocking index creation — doesn't lock the table for the duration
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);