schema-design-guide

Schema Design Guide

Design well-structured relational database schemas with proper normalization, strategic indexing, referential integrity, and clear naming conventions.

When to Use

• User designs new database tables
• User asks about normalization or denormalization
• User needs help with indexing strategy
• User defines relationships between tables
• User asks about naming conventions for database objects

Core Patterns

Normalization (1NF through 3NF)

Apply normalization to eliminate redundancy, then selectively denormalize for performance.

-- BAD: Unnormalized (repeating groups, redundant data)
CREATE TABLE orders_bad (
  id SERIAL PRIMARY KEY,
  customer_name VARCHAR(255),
  customer_email VARCHAR(255),
  product1_name VARCHAR(255),
  product1_price DECIMAL(10,2),
  product2_name VARCHAR(255),
  product2_price DECIMAL(10,2)
);

-- GOOD: Normalized to 3NF
CREATE TABLE customers (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE products (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id),
  status VARCHAR(50) NOT NULL DEFAULT 'pending'
    CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled')),
  ordered_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  id BIGINT GENERATED ALWAYS AS IDENTITY 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 DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
  UNIQUE (order_id, product_id)
);

Indexing Strategy

Index columns used in WHERE, JOIN, and ORDER BY clauses. Prefer composite indexes that match query patterns.