Schema Design & Data Modeling

Normal forms are the formal rules behind normalization. Don't let the academic names scare you — each one is just a specific rule about how to organize your columns. In practice, you need to know three:

First Normal Form (1NF) — every column holds a single value, not a list. Sounds obvious, but it's the #1 beginner mistake:

-- ❌ BAD: Multiple values crammed into one column
CREATE TABLE orders_bad (
    id          INT PRIMARY KEY,
    customer    VARCHAR(100),
    products    VARCHAR(500)       -- "Mouse, Keyboard, Monitor" ← a LIST in one cell
);

INSERT INTO orders_bad VALUES (1, 'Alice', 'Mouse, Keyboard, Monitor');

-- Want all orders that include "Keyboard"?
SELECT * FROM orders_bad WHERE products LIKE '%Keyboard%';
-- This works... until someone orders "Keyboard Cleaner"
-- and now you get false matches. Unreliable.

-- ✅ GOOD: Each value in its own row
CREATE TABLE order_items (
    order_id    INT REFERENCES orders(id),
    product_id  INT REFERENCES products(id),
    quantity    INT DEFAULT 1,
    PRIMARY KEY (order_id, product_id)
);

-- One row per product per order
INSERT INTO order_items VALUES (1, 101, 1);  -- Mouse
INSERT INTO order_items VALUES (1, 102, 1);  -- Keyboard
INSERT INTO order_items VALUES (1, 103, 1);  -- Monitor

-- Want all orders with a Keyboard? Easy, exact, reliable.
SELECT o.* FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE oi.product_id = 102;

Second Normal Form (2NF) — every non-key column depends on the entire primary key, not just part of it. This matters when you have a composite keyA primary key made of two or more columns. For example, PRIMARY KEY (order_id, product_id) in the order_items table. The combination of order_id + product_id uniquely identifies each row. 2NF says that every other column must depend on BOTH parts of that key, not just one. (a primary key with multiple columns). If a column depends on only part of the key, it belongs in a different table.

Third Normal Form (3NF) — no column depends on another non-key column. Classic example: storing city and zip_code in the same table. The city depends on the zip code (zip 10001 is always New York), not on the primary key. Technically, city should be in a zip_codes lookup table. In practice, most teams keep city and zip together for simplicity and accept the tiny duplication — pragmatism over purity.

A schema without constraintsRules that the database enforces automatically. PRIMARY KEY (uniqueness + not null), FOREIGN KEY (must reference an existing row), UNIQUE (no duplicates), NOT NULL (can't be empty), CHECK (custom validation like CHECK (price > 0)), DEFAULT (auto-fill a value). These run inside the database engine, so they can't be bypassed by buggy application code. That's why you add them — even if your app validates too. is like a building without a fire code — it works until something goes wrong, and then everything burns down. Constraints are the rules that your database enforces no matter what. Even if your application code has a bug, the database catches the invalid data.

-- FOREIGN KEY: Can't create an order for a non-existent user
INSERT INTO orders (user_id, total) VALUES (9999, 50.00);
-- ERROR: insert or update on table "orders" violates foreign key constraint
-- Key (user_id)=(9999) is not present in table "users".

-- UNIQUE: Can't register two users with the same email
INSERT INTO users (name, email) VALUES ('Fake Alice', 'alice@email.com');
-- ERROR: duplicate key value violates unique constraint "users_email_key"

-- CHECK: Can't give a 6-star review
INSERT INTO reviews (user_id, product_id, stars) VALUES (1, 1, 6);
-- ERROR: new row for relation "reviews" violates check constraint
-- Check constraint: stars BETWEEN 1 AND 5

-- NOT NULL: Can't create a product without a name
INSERT INTO products (name, price) VALUES (NULL, 29.99);
-- ERROR: null value in column "name" violates not-null constraint

-- DEFAULT: Status auto-fills if you don't specify it
INSERT INTO orders (user_id, total) VALUES (1, 29.99);
-- status automatically set to 'pending' — you don't need to pass it

Twitter uses VARCHAR(280) on their tweet text column, not TEXT. Why? Because VARCHAR(280) is a constraint — the database physically cannot store a tweet longer than 280 characters. If a bug in the Twitter app tried to save a 500-character tweet, the database would reject it. Using TEXT (unlimited length) would mean relying on application code alone to enforce the character limit. Constraints are your last line of defense.

Choosing the right data typeThe kind of value a column can hold. Common types: INT (whole numbers), BIGINT (big numbers, up to 9.2 quintillion), VARCHAR(n) (text up to n characters), TEXT (unlimited text), DECIMAL(p,s) (exact numbers with p total digits and s decimal places), BOOLEAN (true/false), TIMESTAMP (date + time), UUID (128-bit unique identifier). The wrong choice causes bugs. The right choice prevents them. is not pedantic — it prevents real bugs. The most dangerous mistake in schema design is using FLOAT for money.

-- ❌ FLOAT: Stores money as a binary approximation
SELECT 0.1::FLOAT + 0.2::FLOAT;
-- Result: 0.30000000000000004  ← NOT 0.3!

-- This is not a PostgreSQL bug. It's how IEEE 754 floating point works.
-- Stripe, Shopify, every bank, every payment processor uses DECIMAL for money.

-- ✅ DECIMAL: Stores money as exact base-10 digits
SELECT 0.1::DECIMAL + 0.2::DECIMAL;
-- Result: 0.3  ← Exactly right.

-- Shopify's orders table:
-- total_price DECIMAL(10,2)  ← 10 digits total, 2 after the decimal
-- This means prices from 0.00 to 99,999,999.99 — more than enough.

-- GitHub's repositories table uses BIGINT for id, not INT:
-- INT max: 2,147,483,647 (2.1 billion) — GitHub approached this in 2019
-- BIGINT max: 9,223,372,036,854,775,807 (9.2 quintillion) — safe for centuries

Here's a quick reference for the most common choices:

Timestamps cause more subtle bugs than almost any other data type. The problem: your server is in US-East, your user is in Tokyo, and your database stores 2024-01-15 14:30:00. Is that 2:30 PM in New York or 2:30 PM in Tokyo? Without a timezone, nobody knows.

-- ❌ BAD: TIMESTAMP without timezone (ambiguous)
CREATE TABLE events_bad (
    id         SERIAL PRIMARY KEY,
    event_time TIMESTAMP           -- 2024-01-15 14:30:00 — which timezone?
);

-- ✅ GOOD: TIMESTAMPTZ (stores as UTC, converts on display)
CREATE TABLE events (
    id         SERIAL PRIMARY KEY,
    event_time TIMESTAMPTZ         -- stores internally as UTC
);

-- Insert with any timezone — PostgreSQL converts to UTC
INSERT INTO events (event_time) VALUES ('2024-01-15 14:30:00+09:00');  -- Tokyo
INSERT INTO events (event_time) VALUES ('2024-01-15 00:30:00-05:00');  -- NYC (same instant!)

-- Both rows store the same UTC moment: 2024-01-15 05:30:00+00
-- Display in any timezone:
SET timezone = 'America/New_York';
SELECT event_time FROM events;  -- shows 2024-01-15 00:30:00-05

SET timezone = 'Asia/Tokyo';
SELECT event_time FROM events;  -- shows 2024-01-15 14:30:00+09

-- RULE: Always use TIMESTAMPTZ. Always store in UTC. Convert on display.

Every major company has learned this the hard way. GitHub stores all timestamps in UTC. Stripe's API returns all timestamps as Unix timestampsThe number of seconds since January 1, 1970, 00:00:00 UTC (called the "Unix epoch"). For example, 1705315800 = 2024-01-15 14:30:00 UTC. No timezone ambiguity — it's always UTC. Stripe's API returns timestamps this way: "created": 1705315800. Your application converts to local time for display. Many APIs use this format because it's unambiguous and easy to do math on (want "5 minutes ago"? subtract 300). (seconds since 1970, always UTC). Shopify stores created_at and updated_at on every table, both in UTC. If you take away one rule: always use TIMESTAMPTZ, always store in UTC.

When a user "deletes" their account on Stripe, their data doesn't vanish from the database. It gets a timestamp in a column called deleted_at. This is called a soft deleteInstead of DELETE FROM users WHERE id = 42;, you do UPDATE users SET deleted_at = NOW() WHERE id = 42;. The row is still in the database, but your application filters it out: SELECT * FROM users WHERE deleted_at IS NULL;. This means you can "undo" deletes, comply with audit requirements, and debug issues by looking at "deleted" records. Stripe, Shopify, GitHub, and most SaaS companies use soft deletes on critical tables. — the data looks deleted to the user, but it's still there for auditing, compliance, and "oops, undo that."

-- Soft delete pattern (used by Stripe, Shopify, GitHub)
CREATE TABLE users (
    id         SERIAL PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    email      VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    deleted_at TIMESTAMPTZ          -- NULL = active, non-NULL = "deleted"
);

-- "Delete" a user (they still exist in the database)
UPDATE users SET deleted_at = NOW() WHERE id = 42;

-- Your application's default query ALWAYS filters deleted rows:
SELECT * FROM users WHERE deleted_at IS NULL;

-- Admin/support can see "deleted" users for debugging:
SELECT * FROM users WHERE deleted_at IS NOT NULL;

-- "Undo" a delete (customer changed their mind):
UPDATE users SET deleted_at = NULL WHERE id = 42;

-- For heavy audit requirements, add a separate audit table:
CREATE TABLE user_audit (
    id         SERIAL PRIMARY KEY,
    user_id    INT NOT NULL,
    action     VARCHAR(20) NOT NULL,  -- 'created', 'updated', 'deleted'
    old_data   JSONB,                 -- snapshot before change
    new_data   JSONB,                 -- snapshot after change
    changed_by INT,                   -- who made the change
    changed_at TIMESTAMPTZ DEFAULT NOW()
);

The created_at + updated_at + deleted_at trio appears on virtually every table at companies like Shopify. Rails generates these columns automatically. They're not optional — they're essential for debugging ("when was this record last changed?"), compliance ("show me the deletion timestamp for GDPR"), and data recovery ("undelete this accidentally removed product").

Imagine your product manager asks: "What was our total revenue by product category, by month, for the last two years, broken down by customer country?" That query needs to scan millions of rows across multiple tables. Your OLTP schema can answer it, but it'll take minutes. For analytics, companies use a different schema shape called a star schemaA denormalized schema optimized for analytical queries. At the center is a "fact table" (e.g., fact_sales) containing measurements (revenue, quantity, timestamps). Around it are "dimension tables" (products, customers, dates) containing descriptive attributes. It's called "star" because the ER diagram looks like a star with the fact table at the center. Data warehouses like Snowflake, BigQuery, and Redshift all use star schemas..

-- STAR SCHEMA: one fact table at the center, dimension tables around it

-- Fact table: one row per sale event (the measurements)
CREATE TABLE fact_sales (
    sale_id       BIGINT PRIMARY KEY,
    date_key      INT REFERENCES dim_date(date_key),
    product_key   INT REFERENCES dim_product(product_key),
    customer_key  INT REFERENCES dim_customer(customer_key),
    quantity      INT,
    revenue       DECIMAL(12,2),
    discount      DECIMAL(10,2)
);

-- Dimension: date (pre-computed calendar table)
CREATE TABLE dim_date (
    date_key    INT PRIMARY KEY,          -- 20240115
    full_date   DATE,
    year        INT, month INT, day INT,
    quarter     INT,
    day_of_week VARCHAR(10),
    is_weekend  BOOLEAN,
    is_holiday  BOOLEAN
);

-- Dimension: product (denormalized — category embedded, not a separate table)
CREATE TABLE dim_product (
    product_key   INT PRIMARY KEY,
    name          VARCHAR(200),
    category      VARCHAR(50),
    subcategory   VARCHAR(50),
    brand         VARCHAR(100)
);

-- Dimension: customer (denormalized — address embedded directly)
CREATE TABLE dim_customer (
    customer_key  INT PRIMARY KEY,
    name          VARCHAR(100),
    country       VARCHAR(50),
    city          VARCHAR(100),
    segment       VARCHAR(50)  -- 'Enterprise', 'SMB', 'Consumer'
);

-- The analytics query that would be slow on OLTP is FAST here:
SELECT dp.category, dd.year, dd.month, SUM(fs.revenue) AS total_revenue
FROM fact_sales fs
JOIN dim_product dp ON fs.product_key = dp.product_key
JOIN dim_date dd ON fs.date_key = dd.date_key
WHERE dd.year IN (2023, 2024)
GROUP BY dp.category, dd.year, dd.month
ORDER BY dd.year, dd.month, total_revenue DESC;

Key difference from OLTP: star schemas are intentionally denormalized. The customer's country is stored directly in dim_customer, not in a separate countries table. That duplication is fine — analytics databases optimize for read speed, not write consistency. Snowflake, BigQuery, Amazon Redshift, and ClickHouse are all built for star schemas.

Traditional schemas store current state: "Alice has $150 in her account." Event sourcing stores every change that ever happened: "Alice deposited $100, then deposited $50." You compute the current state by replaying the events. This sounds like more work (because it is), but it gives you a complete audit trail and the ability to reconstruct the state at any point in time.

-- EVENT SOURCING: store events, derive state

-- The event log — append-only, NEVER update or delete
CREATE TABLE account_events (
    event_id    BIGSERIAL PRIMARY KEY,
    account_id  UUID NOT NULL,
    event_type  VARCHAR(50) NOT NULL,    -- 'deposit', 'withdrawal', 'transfer'
    amount      DECIMAL(12,2) NOT NULL,
    metadata    JSONB DEFAULT '{}',       -- extra context (ATM location, etc.)
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

-- Alice opens an account and makes transactions
INSERT INTO account_events (account_id, event_type, amount) VALUES
('a1b2c3d4-...', 'account_opened', 0.00),
('a1b2c3d4-...', 'deposit',        100.00),
('a1b2c3d4-...', 'deposit',        50.00),
('a1b2c3d4-...', 'withdrawal',     30.00);

-- Compute current balance by replaying events
SELECT account_id,
       SUM(CASE
           WHEN event_type = 'deposit' THEN amount
           WHEN event_type = 'withdrawal' THEN -amount
           ELSE 0
       END) AS balance
FROM account_events
WHERE account_id = 'a1b2c3d4-...'
GROUP BY account_id;
-- balance = 120.00  (0 + 100 + 50 - 30)

-- "What was Alice's balance on Jan 15?"
-- Just replay events up to that date!
SELECT SUM(CASE WHEN event_type = 'deposit' THEN amount
                WHEN event_type = 'withdrawal' THEN -amount ELSE 0 END)
FROM account_events
WHERE account_id = 'a1b2c3d4-...' AND created_at <= '2024-01-15';

-- WHO USES THIS:
-- ✅ Banks and payment processors (complete audit trail required by law)
-- ✅ Stripe (every charge, refund, dispute is an event)
-- ✅ Event-driven systems (Kafka, EventStoreDB)
-- ❌ NOT for simple CRUD apps — overkill for a blog or todo list

Event sourcing is not a replacement for OLTP — it's a complement. Many systems use event sourcing for the core domain (payments, banking) and traditional OLTP for everything else (user profiles, settings). The events are the source of truth, and materialized views or read models provide fast query access.

Instagram serves over 2 billion monthly active users, yet their core storage is still PostgreSQLAn open-source relational database known for its correctness, extensibility, and rich feature set. It supports JSONB, full-text search, GIS data, and hundreds of extensions. Instagram, Shopify, GitLab, Supabase, and Notion all run on PostgreSQL. — not some exotic NoSQL system. The secret? Sharding by user_id.

Every photo, like, comment, and follow relationship is keyed to the user who created it. When you upload a photo, Instagram hashes your user_id to determine which PostgreSQL shard stores it. That means all of your data lives on the same machine — so loading your profile, your feed, and your notifications doesn't require cross-shard joins.

Their IDs are also clever: Instagram uses a variant of Snowflake IDsA 64-bit ID format invented by Twitter that encodes the creation timestamp, machine ID, and a sequence number into a single integer. It's sortable by time (newer IDs are always larger), globally unique without a central coordinator, and fits in a BIGINT column. Instagram, Discord, and many other companies use their own Snowflake variants. — 64-bit integers that encode the timestamp, shard ID, and a per-shard sequence number. This means IDs are time-sortable (newer photos always have higher IDs) and you can extract the shard from the ID itself, so routing is instant.

Shopify hosts millions of online stores, and every one of them has products, orders, customers, and inventory. The schema for each store is identical — same tables, same columns, same constraints. But the data is completely isolated. Merchant A can never accidentally see Merchant B's orders.

Under the hood, Shopify uses VitessAn open-source database clustering system for horizontal scaling of MySQL. Originally built at YouTube in 2010 to handle their massive growth, it sits between your application and MySQL, routing queries to the right shard automatically. Shopify, Slack, HubSpot, and Square all use Vitess in production. It handles connection pooling, query routing, and schema management across thousands of shards. (originally built at YouTube) to shard MySQL across thousands of machines. The shard key is the shop_id. Every query includes the shop context, so Vitess routes it to the correct shard without the application needing to know which physical database holds the data.

The brilliant part? Each merchant's schema is exactly the same, but each lives in its own virtual keyspace. When Shopify adds a new column (like fulfillment_status on orders), they roll out the migration one shard at a time over days — not a single big-bang deployment.

Slack's core product is messaging, and messages have a specific access pattern: you almost always read them in order, within a single channel, for a single workspace. Slack exploits this by storing messages in a heavily denormalizedIntentionally duplicating data across tables to avoid JOINs at read time. The opposite of normalization. You trade disk space and write complexity for much faster reads. Common in high-throughput systems where reads vastly outnumber writes — Slack reads 10-100x more than it writes. format, keyed by (workspace_id, channel_id, message_ts).

The message row stores the author's display name and avatar URL directly, rather than joining to a users table. Yes, this duplicates data. Yes, it means updating a username requires a backfill. But it means loading 50 messages in a channel is one simple query with zero JOINs — and at 18 billion messages, eliminating JOINs matters more than saving a few bytes of duplication.

Their schema evolved over time. Early Slack was more normalized. As they grew past 10 billion messages, they denormalized aggressively — a textbook example of the principle that the right schema at 1 million rows is not the right schema at 10 billion rows.

Airbnb's listing schema is deceptively complex. A listing isn't just a title and a price — it has an availability calendar (which dates are open, blocked, or booked), dynamic pricing (price changes per night based on demand, season, and local events), and location data (latitude, longitude, neighborhood, city).

Their calendar table has one row per listing per date — so a listing available for the next 365 days creates 365 rows. With millions of listings, that's billions of calendar rows. They partition this table by date range and shard by listing_id, so checking "is this listing available on March 15-18?" hits a single shard and scans only the relevant date partition.

The pricing model is even more interesting. Rather than storing a single price_per_night on the listing, Airbnb stores price overrides per date (like "New Year's Eve is $500/night instead of $150"). The schema supports a default_price on the listing, plus a price_overrides table keyed by (listing_id, date). The app queries both and picks the override if one exists.