Your schema is the contract between every part of your system — backend, frontend, analytics, ML, future engineers. A clean schema lets queries write themselves; a messy one creates years of pain. This lesson is where you stop being someone who queries databases and start being someone who designs them.
Learning Objectives
After this lesson, you will be able to:
Use CREATE TABLE to define tables with appropriate column data types
Apply PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT, and CHECK constraints
Design FOREIGN KEY relationships to connect related tables
Choose the correct data type for each column (PostgreSQL-first: TEXT, INTEGER, BIGINT, NUMERIC, TIMESTAMPTZ, UUID, JSONB)
Use CREATE TABLE IF NOT EXISTS to write idempotent schema scripts
Explain normalization and why splitting data across tables reduces redundancy
Pick between bigserial, UUID, and ULID primary keys (and know the trade-offs)
Your Reflection
Saves automatically
What’s one thing you learned? What’s still confusing?
Schema migrations are the #1 source of production incidents. Adding a NOT NULL column to a 1B-row table without a DEFAULT can lock the table for hours. Every senior backend engineer learns this once, painfully. Designing well up front means fewer migrations later
If you have been writing SELECT, INSERT, and UPDATE on pre-built tables, this is where you learn to design those tables yourself. It is like going from driving a car to building one -- more responsibility, but more control.
CREATE TABLE students (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
age INTEGER,
gpa REAL DEFAULT 0.0,
enrolled_at TEXT DEFAULT CURRENT_TIMESTAMP
);
Let's break down each part:
id INTEGER PRIMARY KEY AUTOINCREMENT -- unique identifier, auto-assigned
name TEXT NOT NULL -- required text field
email TEXT UNIQUE NOT NULL -- required and must be unique across all rows
age INTEGER -- optional (NULL allowed)
gpa REAL DEFAULT 0.0 -- decimal number, defaults to 0.0 if not provided
enrolled_at TEXT DEFAULT CURRENT_TIMESTAMP -- auto-set to creation time
-- Safe: won't error if the table already exists
CREATE TABLE IF NOT EXISTS students (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL
);
-- Without IF NOT EXISTS, running this twice causes an error
Always use IF NOT EXISTS in setup scripts and migrations. It makes your SQL idempotent (safe to run multiple times).
Loading visualization...
Try it! Modify the CREATE TABLE above to add a weight_grams column with a CHECK constraint that weight must be positive. Then insert a product with a negative weight and see what happens.
-- Booleans (SQLite uses 0/1 integers)
is_active INTEGER NOT NULL DEFAULT 1, -- 1 = true, 0 = false
-- Dates (SQLite stores as TEXT in ISO format)
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
birth_date TEXT, -- store as 'YYYY-MM-DD'
-- Money (use INTEGER cents to avoid floating-point errors)
price_cents INTEGER NOT NULL, -- $9.99 = 999
-- JSON (SQLite supports JSON functions on TEXT columns)
metadata TEXT DEFAULT '{}',
-- Enums (use TEXT with CHECK constraint)
status TEXT NOT NULL CHECK(status IN ('active', 'inactive', 'suspended')),
What Do You Think?
Why should you store prices as INTEGER cents (999) instead of REAL dollars (9.99)?
CREATE TABLE employees (
-- PRIMARY KEY: uniquely identifies each row. One per table.
id INTEGER PRIMARY KEY AUTOINCREMENT,
-- NOT NULL: this column must have a value (cannot be NULL)
name TEXT NOT NULL,
-- UNIQUE: no two rows can have the same value in this column
email TEXT UNIQUE NOT NULL,
-- DEFAULT: value used when INSERT does not provide one
hire_date TEXT DEFAULT CURRENT_TIMESTAMP,
-- CHECK: custom validation rule (must be true for every row)
salary REAL NOT NULL CHECK(salary >= 0),
age INTEGER CHECK(age >= 18 AND age <= 120),
status TEXT NOT NULL DEFAULT 'active'
CHECK(status IN ('active', 'inactive', 'terminated'))
);
Foreign keys create relationships between tables. They ensure that references are valid -- you cannot have an order for a customer that does not exist.
sql
-- Enable foreign key enforcement in SQLite (off by default!)
PRAGMA foreign_keys = ON;
CREATE TABLE departments (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE
);
CREATE TABLE employees (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
salary REAL NOT NULL CHECK(salary >= 0),
-- FOREIGN KEY: department_id must reference a valid departments.id
department_id INTEGER NOT NULL,
FOREIGN KEY (department_id) REFERENCES departments(id)
ON DELETE RESTRICT -- prevent deleting a department that has employees
ON UPDATE CASCADE -- if department id changes, update employees too
);
Prevent the parent row from being deleted/updated if children exist
CASCADE
Delete/update all child rows when the parent is deleted/updated
SET NULL
Set the foreign key column to NULL when the parent is deleted/updated
SET DEFAULT
Set the foreign key column to its DEFAULT value
NO ACTION
Same as RESTRICT in SQLite (check is deferred)
sql
-- CASCADE example: deleting a department deletes all its employees
FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE
-- SET NULL example: if department is deleted, employees become unassigned
FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL
-- RESTRICT example (safest): cannot delete department with employees
FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE RESTRICT
-- BAD: everything in one table (denormalized)
CREATE TABLE orders_flat (
order_id INTEGER,
customer_name TEXT,
customer_email TEXT, -- duplicated for every order by this customer
customer_address TEXT, -- duplicated again
product_name TEXT,
product_price REAL, -- duplicated for every order of this product
quantity INTEGER,
order_date TEXT
);
-- If a customer changes their email, you must update EVERY row
-- If product price changes, old orders show the new price (wrong!)
-- GOOD: split into related tables (normalized)
CREATE TABLE customers (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
address TEXT
);
CREATE TABLE products (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
price_cents INTEGER NOT NULL CHECK(price_cents > 0)
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY AUTOINCREMENT,
customer_id INTEGER NOT NULL,
order_date TEXT DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
CREATE TABLE order_items (
id INTEGER PRIMARY KEY AUTOINCREMENT,
order_id INTEGER NOT NULL,
product_id INTEGER NOT NULL,
quantity INTEGER NOT NULL CHECK(quantity > 0),
price_at_purchase INTEGER NOT NULL, -- snapshot price at time of order!
FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES products(id)
);
Benefits of normalization:
No data duplication: customer email is stored once, in the customers table
Easy updates: change a customer's email in one place
Data integrity: foreign keys prevent orphaned records
Historical accuracy: price_at_purchase captures the price at order time, not the current price
-- Add a new column
ALTER TABLE employees ADD COLUMN phone TEXT;
-- Rename a table
ALTER TABLE employees RENAME TO team_members;
-- Rename a column (SQLite 3.25+)
ALTER TABLE team_members RENAME COLUMN phone TO phone_number;
-- Drop a column (SQLite 3.35+)
ALTER TABLE team_members DROP COLUMN phone_number;
-- Remove a table entirely (structure + data)
DROP TABLE employees;
-- Safe version (no error if table doesn't exist)
DROP TABLE IF EXISTS employees;
Note: SQLite has limited ALTER TABLE support compared to PostgreSQL or MySQL. For complex schema changes in SQLite, the common pattern is: create a new table with the desired schema, copy data from the old table, drop the old table, and rename the new table.
Designing good schemas is a skill that develops with practice. Start with the simplest schema that works, add constraints generously (they catch bugs early), and normalize until each fact is stored in exactly one place. You can always denormalize later for performance -- but starting normalized keeps your data clean.
Quick Check1 / 5
What does the NOT NULL constraint do?
What's next: You can create tables. Next you'll learn how to make queries against them fast: indexes. A query that takes 30 seconds without an index can run in 30ms with the right one — but the wrong index makes INSERTs slow. Time to learn how the Postgres query planner thinks.