Data Modeling Best Practices Cheat Sheet

CREATE TABLE accounts (  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),  email CITEXT UNIQUE NOT NULL,  balance_cents BIGINT NOT NULL DEFAULT 0 CHECK (balance_cents >= 0),  status TEXT NOT NULL DEFAULT 'active'    CHECK (status IN ('active', 'suspended', 'closed')),  created_at TIMESTAMPTZ NOT NULL DEFAULT now());CREATE TABLE transactions (  id BIGSERIAL PRIMARY KEY,  account_id UUID NOT NULL REFERENCES accounts(id),  amount_cents BIGINT NOT NULL,  created_at TIMESTAMPTZ NOT NULL DEFAULT now());CREATE INDEX idx_transactions_account_created  ON transactions (account_id, created_at DESC);

-- OK: JSONB for genuinely variable, rarely-queried attributesCREATE TABLE products (  id SERIAL PRIMARY KEY,  name TEXT NOT NULL,  attributes JSONB  -- e.g., {"color": "red", "size": "M"} varies per category);-- Better: normalize anything you filter, sort, or join on frequentlyCREATE TABLE product_prices (  product_id INT REFERENCES products(id),  currency CHAR(3) NOT NULL,  amount_cents BIGINT NOT NULL,  PRIMARY KEY (product_id, currency));-- Index into JSONB only when necessaryCREATE INDEX idx_products_color ON products ((attributes->>'color'));

CREATE TABLE price_history (  product_id INT NOT NULL REFERENCES products(id),  price_cents BIGINT NOT NULL,  -- valid time: when this price was actually in effect  valid_from TIMESTAMPTZ NOT NULL,  valid_to TIMESTAMPTZ NOT NULL DEFAULT 'infinity',  -- transaction time: when the row was recorded/corrected  recorded_at TIMESTAMPTZ NOT NULL DEFAULT now(),  EXCLUDE USING gist (    product_id WITH =,    tstzrange(valid_from, valid_to) WITH &&  ));-- Query the price that was effective at a past point in timeSELECT price_cents FROM price_historyWHERE product_id = 7  AND valid_from <= '2026-03-01' AND valid_to > '2026-03-01';

-- Anti-pattern: commentable_type + commentable_id with no real FK-- CREATE TABLE comments (id SERIAL, commentable_type TEXT, commentable_id INT);-- Better: exclusive-arc join tables, each with a real foreign keyCREATE TABLE comments (  id BIGSERIAL PRIMARY KEY,  body TEXT NOT NULL,  created_at TIMESTAMPTZ NOT NULL DEFAULT now());CREATE TABLE post_comments (  comment_id BIGINT PRIMARY KEY REFERENCES comments(id),  post_id INT NOT NULL REFERENCES posts(id));CREATE TABLE photo_comments (  comment_id BIGINT PRIMARY KEY REFERENCES comments(id),  photo_id INT NOT NULL REFERENCES photos(id));-- Each join table enforces a real, indexable, integrity-checked FK-- instead of an application-level lookup by string type

CREATE TABLE categories (  id SERIAL PRIMARY KEY,  name TEXT NOT NULL);CREATE TABLE category_closure (  ancestor_id INT NOT NULL REFERENCES categories(id),  descendant_id INT NOT NULL REFERENCES categories(id),  depth INT NOT NULL,  PRIMARY KEY (ancestor_id, descendant_id));-- Every node has a depth-0 self-reference row-- Inserting a child of category 5 under root 1 requires copying-- all of 5's ancestor rows with depth + 1-- Fetch entire subtree of category 3 in one indexed join, no recursionSELECT c.* FROM categories cJOIN category_closure cc ON cc.descendant_id = c.idWHERE cc.ancestor_id = 3;