Building the Schema

Objectives

By the end of this lesson, you should be able to:

• Turn the previous lesson’s sketch into real CREATE TABLE statements
• Choose appropriate data types and constraints for each column
• Verify the schema exists correctly before moving on

💡 Why this matters: This lesson has no new syntax, every piece was covered somewhere in Modules 1 through 5 and Module 9. What’s new is doing it all together, seven related tables, built once, in the right order.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database.

Table Creation Order Matters

A table with a foreign key can’t be created before the table it references exists. Building in this order avoids that problem entirely: categories and customers first (nothing references them yet), then products and employees (which reference categories and reference themselves), then employee_profiles and orders (which reference employees and customers), then order_items last (which references both orders and products).

The Full Schema

CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category_id INTEGER REFERENCES categories(id),
    price NUMERIC(10,2) NOT NULL CHECK (price > 0),
    in_stock BOOLEAN NOT NULL DEFAULT true
);

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    city VARCHAR(50) NOT NULL
);

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50) NOT NULL,
    manager_id INTEGER REFERENCES employees(id),
    hire_date DATE NOT NULL
);

CREATE TABLE employee_profiles (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER NOT NULL UNIQUE REFERENCES employees(id),
    bio TEXT
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    employee_id INTEGER REFERENCES employees(id),
    order_date DATE NOT NULL DEFAULT CURRENT_DATE,
    status VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'completed', 'cancelled'))
);

CREATE TABLE order_items (
    order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE,
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

Constraint Choices, Explained

A few decisions here are worth calling out specifically. products.price CHECK (price > 0) and order_items.quantity CHECK (quantity > 0) (Module 5) prevent nonsensical data, a free product or a negative quantity, at the database level, not just in application code. orders.status CHECK (status IN (...)) restricts status to exactly three valid values, the same technique from Module 5’s CHECK lesson. employee_profiles.employee_id is both UNIQUE and a foreign key (Module 9), enforcing the one-to-one relationship from the previous lesson’s design. order_items.order_id ... ON DELETE CASCADE means deleting an order automatically removes its line items, they have no meaning without the order they belong to (Module 9’s reasoning for choosing CASCADE). order_items’s composite primary key (Module 9) is (order_id, product_id), since a real-world order shouldn’t list the same product twice as two separate rows.

Verifying the Schema

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_name;

    table_name
--------------------
 categories
 customers
 employee_profiles
 employees
 order_items
 orders
 products

All seven tables exist. This is worth checking before moving to the next lesson, populating a schema that’s missing a table or has a typo’d column name surfaces as a confusing error much later otherwise.

Try It

1. Run every CREATE TABLE statement above, in order, and confirm all seven tables exist using the information_schema.tables query.
2. Try creating order_items before orders exists (temporarily, in a scratch database), and read the resulting error, confirming why creation order matters.
3. Identify every foreign key in this schema, and for each one, name which relationship from the previous lesson it implements.
4. Explain, in your own words, why order_items.order_id uses ON DELETE CASCADE while orders.customer_id has no ON DELETE action specified at all (defaulting to RESTRICT, Module 9).

Recap

• Tables with foreign keys must be created after the tables they reference, this schema builds in seven ordered steps.
• Every constraint choice here reuses a technique from an earlier module: CHECK for valid ranges and sets of values, UNIQUE + a foreign key for one-to-one, ON DELETE CASCADE for dependent rows, and a composite primary key for a junction table.
• Verifying the schema against information_schema.tables before populating it catches structural mistakes early.

Next lesson: populating this schema with real data, and performing the CRUD operations a live database actually needs.