Database Design Patterns

-- OLTP = Online Transaction Processing.
-- The shape behind a live app: small, fast, single-row reads and writes.
-- Normalised (no duplicated facts) so an UPDATE touches data in ONE place.

CREATE TABLE customers (
    customer_id  BIGSERIAL PRIMARY KEY,     -- surrogate key (see section 2)
    email        VARCHAR(255) UNIQUE NOT NULL,
    name         VARCHAR(100) NOT NULL,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE orders (
    order_id     BIGSERIAL PRIMARY KEY,
 
...

-- OLAP = Online Analytical Processing.
-- The shape behind dashboards: scan MILLIONS of rows, aggregate, report.
-- A "star schema": one FACT table (the events) surrounded by DIMENSION
-- tables (the descriptions). Deliberately denormalised so analysts join less.

-- Fact: one row per measurable event, mostly numbers (the "measures").
CREATE TABLE fact_sales (
    sale_id       BIGSERIAL PRIMARY KEY,
    date_key      INT NOT NULL,    -- foreign keys into the dimensions
    product_key   INT NO
...

-- A NATURAL key is real-world data used as the identifier
-- (email, ISBN, country code). A SURROGATE key is a meaningless
-- auto-generated number (BIGSERIAL / IDENTITY / UUID) with no meaning.

-- Natural key — looks tidy, but breaks when reality changes:
CREATE TABLE users_natural (
    email VARCHAR(255) PRIMARY KEY,   -- what happens when they change email?
    name  VARCHAR(100)
);

-- Surrogate key — the modern default for app tables:
CREATE TABLE users_surrogate (
    user_id BIGSERIAL 
...

-- A LOOKUP (reference) table replaces a free-text column with a
-- small controlled list. Instead of typing 'shipped' / 'Shipped' /
-- 'SHIPED' all over the orders table, you point at one row.

CREATE TABLE order_statuses (
    status_id    SMALLINT PRIMARY KEY,
    code         VARCHAR(20) UNIQUE NOT NULL,   -- 'pending', 'shipped', ...
    description  VARCHAR(100) NOT NULL
);

INSERT INTO order_statuses (status_id, code, description) VALUES
    (1, 'pending',   'Awaiting payment'),
    (2, '
...

-- A student can take MANY courses; a course has MANY students.
-- Many-to-many can't live in one table — you need a JUNCTION table
-- (also called a join / bridge / linking table) holding one row per pair.

CREATE TABLE students (
    student_id BIGSERIAL PRIMARY KEY,
    name       VARCHAR(100) NOT NULL
);
CREATE TABLE courses (
    course_id  BIGSERIAL PRIMARY KEY,
    title      VARCHAR(150) NOT NULL
);

-- The junction: each row links ONE student to ONE course.
CREATE TABLE enrolments (
   
...

-- WORKED QUERY: list every student with the courses they take.
-- You walk the junction in the middle to connect the two sides.

SELECT  s.name  AS student,
        c.title AS course
FROM students s
JOIN enrolments e ON s.student_id = e.student_id   -- student -> link
JOIN courses    c ON e.course_id  = c.course_id    -- link -> course
ORDER BY s.name, c.title;

-- HIERARCHY pattern A: Adjacency list.
-- Each row stores a pointer to its PARENT. Simple, cheap to write.

CREATE TABLE categories (
    category_id BIGSERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    parent_id   BIGINT REFERENCES categories(category_id)  -- self-reference
);
-- Electronics(parent NULL) -> Phones(parent Electronics) -> iPhone(parent Phones)

-- Walking the tree needs a RECURSIVE query:
WITH RECURSIVE tree AS (
    SELECT category_id, name, parent_id, 1 AS depth
 
...

-- HIERARCHY pattern B: Closure table.
-- Store EVERY ancestor-descendant pair (including self). Reads are a plain
-- JOIN with no recursion — great when you query subtrees constantly.

CREATE TABLE category_paths (
    ancestor_id   BIGINT NOT NULL REFERENCES categories(category_id),
    descendant_id BIGINT NOT NULL REFERENCES categories(category_id),
    depth         INT NOT NULL,           -- 0 = the node itself
    PRIMARY KEY (ancestor_id, descendant_id)
);

-- "All descendants of Electro
...

-- SOFT DELETE: don't physically remove a row — flag it.
-- Keeps history, lets you "undelete", and protects against accidents.

ALTER TABLE orders ADD COLUMN is_deleted BOOLEAN     NOT NULL DEFAULT FALSE;
ALTER TABLE orders ADD COLUMN deleted_at TIMESTAMPTZ;          -- NULL = live

-- "Deleting" is really an UPDATE:
UPDATE orders SET is_deleted = TRUE, deleted_at = NOW()
WHERE order_id = 12345;

-- EVERY normal query must now exclude deleted rows:
SELECT * FROM orders WHERE is_deleted = FALSE;
...

-- TIMESTAMPS + AUDIT/HISTORY: who changed what, and when.

-- 1) created_at / updated_at on the live table — bookkeeping for every row:
ALTER TABLE orders ADD COLUMN created_at TIMESTAMPTZ NOT NULL DEFAULT NOW();
ALTER TABLE orders ADD COLUMN updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW();
-- (a trigger or your app sets updated_at = NOW() on each UPDATE)

-- 2) A separate HISTORY table records a copy of every change.
CREATE TABLE orders_history (
    history_id  BIGSERIAL PRIMARY KEY,
    order_
...

-- EAV = Entity-Attribute-Value. ONE row per attribute, so any entity
-- can have any fields. Tempting for "user-defined fields"... but dangerous.

CREATE TABLE eav_values (
    entity_id      BIGINT NOT NULL,
    attribute_name VARCHAR(100) NOT NULL,
    value          TEXT,                 -- everything is a string: no types!
    PRIMARY KEY (entity_id, attribute_name)
);

-- Reading even ONE product means re-assembling it row by row:
SELECT  MAX(CASE WHEN attribute_name = 'cpu'    THEN value 
...

-- NORMALISATION removes duplicated facts so writes stay consistent.
-- DELIBERATE DENORMALISATION re-adds a copy to make reads cheaper —
-- a conscious trade, not laziness.

-- Normalised: customer name lives only in customers. To show it on an
-- order you must JOIN every time:
SELECT o.order_id, c.name
FROM orders o JOIN customers c ON o.customer_id = c.customer_id;

-- Denormalised: copy the name onto orders to skip the JOIN on a hot path:
-- ALTER TABLE orders ADD COLUMN customer_name VARCH
...