Stage 3 · Build

Databases & Persistence

SQL Data Modeling

Design PostgreSQL tables, primary keys, foreign keys, indexes, constraints, and normalized relationships.

8 min readBuilding Backend ApplicationsBuild

Why SQL Data Modeling

Data modeling defines how your application stores and retrieves state. A good model prevents data corruption, makes queries fast, and adapts as requirements change. PostgreSQL gives you rich types, constraints, and indexing to enforce correctness at the database level.

Primary Keys

Every table needs a primary key. UUIDs are preferred over auto-incrementing integers for distributed systems. They avoid ID collisions across services and prevent enumeration attacks.

UUID primary keys in PostgreSQL


CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

pgcrypto provides gen_random_uuid() which generates v4 UUIDs. The DEFAULT clause means you do not need to provide the ID on insert.

UUID vs Auto-Increment

Auto-increment is simpler but leaks information (user 1, user 2, ...) and causes conflicts in distributed systems. UUIDs are globally unique but use more storage and indexes. For most applications, UUIDs are the right choice.

Foreign Keys

Foreign keys enforce referential integrity. They prevent orphaned rows and make JOIN queries unambiguous. Always add foreign key constraints even if your application layer also validates relationships.

Foreign key relationships


CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    total DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id UUID NOT NULL REFERENCES products(id),
    quantity INT NOT NULL CHECK (quantity > 0),
    price DECIMAL(10,2) NOT NULL
);

ON DELETE CASCADE removes child rows when the parent is deleted. ON DELETE RESTRICT prevents deletion if children exist. Choose based on your data lifecycle.

Indexes

Indexes speed up queries but slow down writes. Add indexes on columns used in WHERE, JOIN, and ORDER BY clauses. Use partial indexes for filtered queries. Avoid over-indexing.

Index types


-- B-tree index for equality and range queries
CREATE INDEX idx_users_email ON users(email);

-- Partial index for filtered queries
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';

-- Composite index for multi-column queries
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Unique index for uniqueness enforcement
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

Partial indexes are smaller and faster because they only index rows matching the WHERE condition. Use them when you frequently query a subset of rows.

Constraints

Constraints enforce data rules at the database level. They prevent invalid data regardless of which application or migration tool inserts it. Never rely solely on application-layer validation.

Common constraints


CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    stock INT NOT NULL DEFAULT 0 CHECK (stock >= 0),
    description TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Check constraint for custom rules
ALTER TABLE orders ADD CONSTRAINT valid_status
    CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered'));

CHECK constraints validate data ranges and allowed values. UNIQUE constraints prevent duplicates. NOT NULL prevents missing values. Use all three liberally.

Normalization

Normalization eliminates data redundancy. Third normal form (3NF) is the standard target: every non-key column depends on the primary key, the whole key, and nothing but the key. Denormalize only for performance with clear justification.

Normalized vs denormalized


-- Normalized: separate tables
CREATE TABLE users (id UUID PRIMARY KEY, name TEXT);
CREATE TABLE orders (
    id UUID PRIMARY KEY,
    user_id UUID REFERENCES users(id),
    -- user name is NOT here, it lives in users table
);

-- Denormalized for read performance (snapshot)
CREATE TABLE orders (
    id UUID PRIMARY KEY,
    user_id UUID REFERENCES users(id),
    user_name TEXT,  -- snapshot of user name at order time
    user_email TEXT  -- snapshot of user email at order time
);

Denormalize when JOINs are too slow for read-heavy queries. Store snapshots of data that changes rarely. Document why you denormalized so future engineers understand the tradeoff.

Do not denormalize prematurely

Start normalized. Measure query performance. Denormalize only when profiling shows JOINs are a bottleneck. Premature denormalization creates data consistency bugs and migration headaches.

Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.