Schema Design Patterns

Schema Design Patterns

Good schema design prevents problems that are painful to fix later. These patterns appear in every production PostgreSQL application.

Standard Table Template

CREATE TABLE courses (
  -- Identity
  id TEXT PRIMARY KEY,           -- natural key for courses (cc-js-m01)
  -- or for user tables:
  -- id UUID PRIMARY KEY DEFAULT gen_random_uuid()

  -- Business columns
  track TEXT NOT NULL,
  title TEXT NOT NULL,
  subtitle TEXT NOT NULL,
  level TEXT NOT NULL CHECK (level IN ('Basic', 'Masters', 'PhD', 'Next-Gen AI')),
  xp INT NOT NULL DEFAULT 0 CHECK (xp >= 0),
  module INT NOT NULL CHECK (module >= 1),

  -- Optional relations
  cert_area TEXT,

  -- Audit columns (always include)
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Auto-update updated_at
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER courses_updated_at
  BEFORE UPDATE ON courses
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

Enum Types

CREATE TYPE course_level AS ENUM ('Basic', 'Masters', 'PhD', 'Next-Gen AI');
CREATE TYPE course_track AS ENUM ('crash', 'tech', 'marketing', 'trading');

CREATE TABLE courses (
  level course_level NOT NULL,
  track course_track NOT NULL,
  -- ...
);

Soft Delete

ALTER TABLE courses ADD COLUMN deleted_at TIMESTAMPTZ;

-- "Delete"
UPDATE courses SET deleted_at = now() WHERE id = 'cc-js-m01';

-- Active records only
CREATE VIEW active_courses AS
  SELECT * FROM courses WHERE deleted_at IS NULL;

-- Index only active
CREATE INDEX active_courses_track_idx ON courses (track)
WHERE deleted_at IS NULL;