Schema Design Basics

9 examples to get you started with schema design - 6 basic and 3 intermediate.

Prerequisites

CREATE on a development database.

# Local dev with Docker (PostgreSQL 18)
docker run -d --name pg18 -e POSTGRES_PASSWORD=dev -p 5432:5432 postgres:18
psql postgres://postgres:dev@localhost:5432/postgres

Basic Examples

1. Surrogate Primary Key

Opaque id generated by database.

CREATE TABLE app.accounts (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);

• Stable when business natural keys change
• Narrow FK references
• Expose id in APIs consistently

2. Natural Unique Key

Business identifier with UNIQUE constraint.

CREATE TABLE app.accounts (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  external_ref text NOT NULL UNIQUE,
  name text NOT NULL
);

• UNIQUE enforces identity from upstream systems
• Keep surrogate PK for joins
• Document external_ref source

3. Naming Tables

Plural nouns and schema prefix.

CREATE TABLE app.order_lines (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id bigint NOT NULL,
  sku text NOT NULL,
  qty int NOT NULL CHECK (qty > 0)
);

• Consistent plural or singular per org standard
• Prefix columns with table context when joining wide selects
• Avoid reserved words

4. Foreign Key Column

Name child column parent_table_id.

ALTER TABLE app.order_lines
  ADD CONSTRAINT order_lines_order_id_fkey
  FOREIGN KEY (order_id) REFERENCES app.orders(id);

• Explicit ON DELETE behavior required in design
• Index FK columns on child tables
• NOT NULL when relationship is required

5. NOT NULL Defaults

Reduce NULL ambiguity.

CREATE TABLE app.settings (
  account_id bigint PRIMARY KEY REFERENCES app.accounts(id),
  theme text NOT NULL DEFAULT 'light'
);

• Defaults speed inserts
• NOT NULL when unknown is invalid
• NULL reserved for optional unknowns

6. Check Constraints

Database-enforced row rules.

ALTER TABLE app.order_lines
  ADD CONSTRAINT order_lines_qty_positive CHECK (qty > 0);

• Survive application bugs
• Named constraints improve error messages
• Document in migration comments

Intermediate Examples

7. Composite Natural Key

When business pair is unique.

CREATE TABLE app.region_codes (
  country char(2) NOT NULL,
  region_code text NOT NULL,
  name text NOT NULL,
  PRIMARY KEY (country, region_code)
);

• Good for pure reference data
• Wider FKs on child tables
• Consider surrogate if FK width matters

8. Soft Delete Column

deleted_at marks inactive rows.

ALTER TABLE app.accounts ADD COLUMN deleted_at timestamptz;
CREATE INDEX accounts_active_idx ON app.accounts (id) WHERE deleted_at IS NULL;

• Partial index keeps hot set small
• Unique constraints may need partial unique index
• Vacuum still processes dead tuples

9. Comments on Objects

Catalog documentation.

COMMENT ON TABLE app.accounts IS 'Tenant billing account';
COMMENT ON COLUMN app.accounts.external_ref IS 'CRM id';

• Visible in psql \d+ and data catalogs
• Survives ORM regeneration if migrations own DDL
• Helps onboarding

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.