Relational Database Concepts — Tables, Rows, Columns and Keys

A relational database organises data into tables — structured grids of rows and columns, similar to spreadsheets, but with strict rules about data types, relationships between tables, and constraints that prevent invalid data. PostgreSQL is the relational database of choice for FastAPI applications: it is free and open-source, feature-rich (JSONB, full-text search, arrays, window functions, CTEs), battle-tested at scale, and has excellent Python driver support. Before writing any SQL, understanding the core vocabulary — tables, rows, columns, keys, and relationships — makes everything else fall into place.

Core Concepts #

Database: blog_db
│
├── Table: users
│   ┌────┬─────────────┬──────────────────────┬───────────┐
│   │ id │ name        │ email                │ role      │
│   ├────┼─────────────┼──────────────────────┼───────────┤
│   │  1 │ Alice Smith │ alice@example.com    │ admin     │
│   │  2 │ Bob Jones   │ bob@example.com      │ editor    │
│   │  3 │ Charlie Kim │ charlie@example.com  │ user      │
│   └────┴─────────────┴──────────────────────┴───────────┘
│        ↑ primary key  ↑ unique constraint
│
└── Table: posts
    ┌────┬──────────────────┬───────────┬───────────────────────┐
    │ id │ title            │ author_id │ created_at            │
    ├────┼──────────────────┼───────────┼───────────────────────┤
    │  1 │ Hello World      │         1 │ 2025-08-06 14:30:00   │
    │  2 │ FastAPI Guide    │         1 │ 2025-08-07 09:00:00   │
    │  3 │ PostgreSQL Intro │         2 │ 2025-08-08 11:00:00   │
    └────┴──────────────────┴───────────┴───────────────────────┘
                                  ↑ foreign key → users.id

Tables, Columns and Data Types #

-- A table has a name and a set of columns
-- Each column has a name, a data type, and optional constraints

-- Example: the users table
CREATE TABLE users (
    id         BIGSERIAL    PRIMARY KEY,         -- auto-increment integer, primary key
    name       VARCHAR(100) NOT NULL,             -- string up to 100 chars, required
    email      TEXT         NOT NULL UNIQUE,      -- unlimited string, unique per row
    role       VARCHAR(20)  NOT NULL DEFAULT 'user', -- default value
    is_active  BOOLEAN      NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW()  -- timestamp with timezone
);

-- Each row satisfies the column constraints:
-- id:         1, 2, 3 ... (auto-generated)
-- name:       must be provided, max 100 chars
-- email:      must be provided, no two rows can have the same email
-- role:       'user' if not specified
-- is_active:  TRUE if not specified
-- created_at: current time if not specified

Keys and Relationships #

-- PRIMARY KEY: uniquely identifies each row
-- Usually an auto-incrementing integer (SERIAL/BIGSERIAL) or UUID

-- FOREIGN KEY: a column whose value must match a primary key in another table
-- This creates a relationship between the two tables

CREATE TABLE posts (
    id         BIGSERIAL    PRIMARY KEY,
    title      TEXT         NOT NULL,
    body       TEXT         NOT NULL,
    author_id  BIGINT       NOT NULL
               REFERENCES users(id) ON DELETE CASCADE,
    --         ↑ foreign key     ↑ if user deleted, their posts are also deleted
    published  BOOLEAN      NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

-- The relationship:
-- One user → many posts  (one-to-many)
-- users.id  referenced by  posts.author_id
-- Cannot insert a post with author_id=999 if no user has id=999

Relationship Types #

-- Many-to-many: posts and tags
-- A post can have many tags; a tag can belong to many posts
-- Solution: a junction (join) table

CREATE TABLE tags (
    id   BIGSERIAL PRIMARY KEY,
    name TEXT      NOT NULL UNIQUE
);

CREATE TABLE post_tags (
    post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id  BIGINT NOT NULL REFERENCES tags(id)  ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)   -- composite primary key, prevents duplicates
);

Common Mistakes #

Mistake 1 — Storing related data in a single column as JSON instead of related tables #

❌ Wrong — tags stored as a JSON string in a text column:

CREATE TABLE posts (
    tags TEXT   -- "python,fastapi,postgresql" — hard to query, no referential integrity
);

✅ Correct — use a proper many-to-many relationship with a junction table.

Mistake 2 — Using VARCHAR(255) everywhere without thinking #

❌ Wrong — arbitrary VARCHAR(255) for all strings:

title VARCHAR(255),  -- why 255? email VARCHAR(255),  -- might be too short

✅ Correct — choose types based on the actual data:

title TEXT,          -- no length limit needed
email VARCHAR(320),  -- RFC 5321 max email length is 320 chars

Mistake 3 — Forgetting NOT NULL on required columns #

❌ Wrong — NULL allowed silently:

email TEXT UNIQUE,   -- allows NULL — multiple rows can have NULL email!

✅ Correct — add NOT NULL for required fields:

email TEXT NOT NULL UNIQUE,   -- ✓ NULL not allowed, must be unique

Quick Reference — Core Concepts #