Creating Tables and Choosing Types

A schema for a small shop

CREATE TABLE customers (
    id          INTEGER PRIMARY KEY,           -- SERIAL / IDENTITY in PostgreSQL
    email       TEXT NOT NULL UNIQUE,
    full_name   TEXT NOT NULL,
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    id          INTEGER PRIMARY KEY,
    sku         TEXT NOT NULL UNIQUE,
    name        TEXT NOT NULL,
    price_cents INTEGER NOT NULL CHECK (price_cents >= 0),
    active      BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE orders (
    id           INTEGER PRIMARY KEY,
    customer_id  INTEGER NOT NULL REFERENCES customers(id),
    status       TEXT NOT NULL DEFAULT 'pending'
                 CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
    created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

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

Choosing types (PostgreSQL names)

Constraints are your safety net

• NOT NULL: a value is required.
• UNIQUE: no duplicates (emails, SKUs, slugs).
• CHECK: business rules such as non-negative prices.
• REFERENCES: the row it points to must exist.
• ON DELETE CASCADE: deleting an order deletes its items. Use cascades carefully; deleting a customer should usually be blocked if they have orders, not silently erase financial history.

Application code has bugs; constraints catch them before bad data is stored forever.

Changing tables

ALTER TABLE customers ADD COLUMN phone TEXT;
ALTER TABLE products RENAME COLUMN name TO title;
DROP TABLE IF EXISTS temp_import;

In real projects these changes go through migrations (module 8), never by hand in production.

Exercise

Design tables for a blog: authors, posts (with a unique slug and a status of draft/published), comments and tags (many-to-many). Write the CREATE TABLE statements with sensible constraints.

Check yourself

• Why store money as integer cents?
• What does ON DELETE CASCADE do, and when is it dangerous?
• Which constraint enforces that an email is not reused?