fogserv.cloud fogserv.cloud

SQL Schema — Designing Tables, Constraints, Indexes, and Query Plans

Status: Active | Last Updated: 2026-08-29 Category: SQL — Schema Prerequisites: SQL fundamentals Tags: sql, schema, create-table, alter-table, data-types, constraints, not-null, unique, check, default, foreign-key, primary-key, indexes, b-tree, composite-index, partial-index, explain, query-plan Estimated Time: 4-5 hours (Self-paced, includes lab time)

Summary

Schema design is how you lay out your database's structure: columns, data types, constraints, and indexes. Good schema design makes queries fast and prevents data corruption at the source. This lesson covers CREATE TABLE, data type selection across SQLite/PostgreSQL/MySQL, constraint enforcement, index design (B-tree, composite, partial), and how to read EXPLAIN output to diagnose slow queries.

What You'll Learn (Core Competencies)

Table of Contents

  1. Architectural Overview & Core Schema
  2. Deep Dive & Implementation
  3. Anti-Patterns & Common Pitfalls
  4. Independent Challenge
  5. Consolidation & Key Invariants
  6. Next Steps

1. Architectural Overview & Core Schema

The Role of Schema in the Stack

Schema is the contract between your application and your data. A well-designed schema:

The schema lives in migrations — version-controlled files that describe each change to the database structure. In the fogserv.cloud stack, Prisma Migrate manages schema changes.

Schema Design Principles

  1. Normalize to eliminate redundancy (3NF is the typical target): each fact lives in exactly one place.
  2. Denormalize strategically for read performance: materialized views or duplicated columns for frequently-joined data.
  3. Choose the narrowest type that fits: INTEGER for IDs, TEXT for anything variable-length, BOOLEAN for flags.
  4. Index what you filter, sort, and join on.

Query Execution Flow

SQL Query
  ↓
Parser → AST
  ↓
Query Planner/Optimizer (chooses access path)
  ↓
  ├─ Sequential scan (full table read)     ← slow for large tables
  ├─ Index scan (B-tree lookup)            ← fast for selective queries
  └─ Nested loop / hash join (multi-table)
  ↓
Executor → Result rows

The query planner's choices depend on the schema and indexes. EXPLAIN reveals what the planner decided.

2. Deep Dive & Implementation

CREATE TABLE

CREATE TABLE users (
    id         INTEGER       PRIMARY KEY AUTOINCREMENT,
    email      TEXT          NOT NULL UNIQUE,
    name       TEXT          NOT NULL,
    age        INTEGER       CHECK (age >= 0 AND age < 150),
    is_active  INTEGER       DEFAULT 1,
    created_at TEXT          NOT NULL DEFAULT (date('now'))
);

Each column has: name, type, and optional constraint chain. The column list order matters — it defines the physical layout.

Data Types Across Databases

Category SQLite PostgreSQL MySQL
Integer INTEGER INTEGER, BIGINT, SMALLINT INT, BIGINT
Text/variable TEXT TEXT, VARCHAR(n) VARCHAR(n), TEXT
Decimal/fixed REAL NUMERIC(p,s), DECIMAL DECIMAL(p,s)
Float REAL REAL, DOUBLE PRECISION FLOAT, DOUBLE
Boolean INTEGER (0/1) BOOLEAN TINYINT(1)
Date/Time TEXT TIMESTAMP, DATE, TIME DATETIME, TIMESTAMP
Binary BLOB BYTEA BLOB
UUID TEXT UUID CHAR(36)
JSON TEXT JSONB, JSON JSON

SQLite is dynamically typed — types are a hint, not enforced. PostgreSQL and MySQL are strongly typed. In the fogserv.cloud stack, Prisma handles type mapping between TypeScript and PostgreSQL.

Constraints

PRIMARY KEY — unique row identifier; creates a clustered index (most databases). One per table.

id INTEGER PRIMARY KEY

NOT NULL — prevents missing values; most business columns should be NOT NULL.

email TEXT NOT NULL

UNIQUE — no duplicate values in the column across all rows.

email TEXT UNIQUE

CHECK (condition) — arbitrary expression that must be true for every row.

age INTEGER CHECK (age >= 0 AND age < 150)

DEFAULT value — used when no value is provided on INSERT.

created_at TEXT DEFAULT (date('now'))

FOREIGN KEY — references another table's PRIMARY KEY; enforces referential integrity.

user_id INTEGER REFERENCES users(id)
    ON DELETE CASCADE   -- delete child rows when parent is deleted
    ON UPDATE CASCADE   -- update FK when PK changes

ALTER TABLE

-- Add a column
ALTER TABLE users ADD COLUMN phone TEXT;

-- Add a constraint
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- Drop a column (PostgreSQL; SQLite requires recreate-table migration)
ALTER TABLE users DROP COLUMN phone;

-- Rename a table
ALTER TABLE users RENAME TO accounts;

Indexes

Indexes are separate data structures (typically B-trees) that point to table rows, enabling fast lookups instead of full table scans.

-- Single-column B-tree index (default type)
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Composite index (leftmost prefix is used for single-column lookups)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Partial index (index only rows matching a condition — smaller, faster)
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';

-- Unique index (enforces uniqueness + acts as index)
CREATE UNIQUE INDEX idx_users_email ON users(email);

Column order in composite indexes matters: an index on (user_id, status) supports lookups on user_id alone, but NOT on status alone.

EXPLAIN and Query Plans

EXPLAIN shows the query plan without executing the query. EXPLAIN ANALYZE executes and shows actual vs estimated row counts.

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE user_id = 42;

Common node types in query plans:

Node Meaning
SCAN TABLE Full table scan — slow for large tables
SEARCH TABLE USING INTEGER PRIMARY KEY Index lookup — fast
SEARCH TABLE USING INDEX Index scan
USING TEMP B-TREE FOR ORDER BY Sort using temporary structure — consider adding an index
USING GROUP BY Hash or sort-based grouping

Guided Checkpoint

Verify index usage and query plan differences using SQLite:

sqlite3 :memory: -header -column "
  CREATE TABLE orders (id INTEGER, user_id INTEGER, status TEXT, amount REAL);
  -- Insert 1000 rows: user_id 1-10, status varies
  WITH RECURSIVE cnt(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM cnt WHERE n < 1000)
  INSERT INTO orders
    SELECT n, (n % 10) + 1,
           CASE (n % 3) WHEN 0 THEN 'pending' WHEN 1 THEN 'completed' ELSE 'cancelled' END,
           ABS(RANDOM() % 10000) / 100.0
    FROM cnt;

  -- Create index on user_id
  CREATE INDEX idx_orders_user_id ON orders(user_id);

  -- Test: query with index (user_id filter)
  SELECT '=== Query plan: indexed lookup (user_id=1) ===' AS test;
  EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 1;

  -- Test: query without index (status filter — no index)
  SELECT '=== Query plan: full scan (status=completed) ===' AS test;
  EXPLAIN QUERY PLAN SELECT * FROM orders WHERE status = 'completed';

  -- Verify: count rows for both
  SELECT '=== Row counts ===' AS test;
  SELECT status, COUNT(*) FROM orders GROUP BY status;
  SELECT 'Indexed user_id=1 count:' AS label, COUNT(*) FROM orders WHERE user_id = 1;
"

Expected: indexed lookup shows SEARCH TABLE USING INDEX; status filter shows SCAN TABLE. Creating an index on status would change the second plan.

3. Anti-Patterns & Common Pitfalls

Anti-Pattern 1: Missing Indexes on Foreign Keys and Filter Columns

The most common cause of slow queries: a WHERE, JOIN ON, or ORDER BY column with no index.

-- After your app grows, this becomes slow without an index on user_id
SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC;

-- Fix: add a composite index covering both the filter and sort
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);

Profile with EXPLAIN before adding indexes — premature indexing adds write overhead.

Anti-Pattern 2: Over-Indexing

Every index slows down INSERT, UPDATE, and DELETE (the index must be updated). A table with 10 indexes pays the price of updating all 10 on every write.

Rule: index what you query, not what you store. Remove unused indexes.

Anti-Pattern 3: Weak or Absent Constraints

Without NOT NULL, CHECK, and FOREIGN KEY, invalid data enters the database silently. Fixing corrupted data is far harder than preventing it.

-- Bad: no constraints — negative prices, NULL emails, orphan FKs possible
CREATE TABLE products (id INTEGER, price REAL, email TEXT, user_id INTEGER);

-- Good: constraints prevent bad data
CREATE TABLE products (
    id     INTEGER PRIMARY KEY,
    price  REAL    NOT NULL CHECK (price >= 0),
    email  TEXT    NOT NULL UNIQUE,
    user_id INTEGER REFERENCES users(id)
);

Anti-Pattern 4: SQL Injection via Schema-Building String Concatenation

Never build CREATE TABLE or ALTER TABLE statements with user input:

-- Bad
const sql = `CREATE TABLE ${userProvidedName} (id INTEGER)`;

-- Good: whitelist table names at the application layer
const ALLOWED_TABLES = ['users', 'products', 'orders'];
if (!ALLOWED_TABLES.includes(tableName)) throw new Error('Invalid table name');

Anti-Pattern 5: Using VARCHAR(255) for Everything

VARCHAR(n) enforces a maximum, not a minimum. For fixed-length values (country codes, status flags), use TEXT with a CHECK constraint. For unbounded text (descriptions, posts), always use TEXT — VARCHAR(10000) and TEXT have identical storage in most databases.

4. Independent Challenge

Design and optimize the schema for a library lending system.

Design a schema that handles:

-- Entities you need to model:
-- books (title, author, ISBN, available_copies)
-- members (name, email, membership_start)
-- loans (book_id, member_id, checkout_date, due_date, return_date)
-- authors (name, bio)
-- book_authors (book_id, author_id)  -- many-to-many

Write a .sql file that:

  1. Creates all tables with appropriate constraints, data types, and a primary key.
  2. Adds indexes for: find loans by member, find loans by book, find overdue loans (return_date IS NULL AND due_date < today).
  3. Adds a CHECK constraint ensuring available_copies >= 0.
  4. Adds a foreign key from loans to books and members with ON DELETE CASCADE.
  5. Uses EXPLAIN QUERY PLAN to show the difference between an indexed and non-indexed version of: SELECT * FROM loans WHERE member_id = 1 AND return_date IS NULL;

Save as challenge_library_schema.sql. Run against a test database.

5. Consolidation & Key Invariants

Bullet-list summary of the must-remember rules.

6. Next Steps

Change Log

Choose Theme

Your selection is saved locally.

Neural Cacophony
Aperture v2
Flux v1
Mosaic Chaos
Nexus v1
Nexus Zest
Prism v2
Synapse