SQL and Data Modeling From Zero

You have been a data modeler for years. Every pipeline you built in GoHighLevel, every custom field you added to a contact, every "opportunity" you dragged between stages: that was you designing entities and relationships in someone else's database. The vendor just kept the vocabulary from you. This lesson gives you the vocabulary, and by the end you will have rebuilt the core of a CRM yourself, in about forty lines of SQL, and understood it better than you ever understood the tool.

The map: thinking in tables

A relational database stores everything as tables: named grids where each row is one thing and each column is one attribute of that thing. A contacts table has a row per contact, with columns like name and email. The design of your tables (which tables exist, what columns they carry, how they connect) is the schema, and it is the highest-leverage design artifact in most applications. Code changes weekly; schemas calcify. Part 4 makes this argument properly; today you get the mechanics.

Two kinds of keys make tables connect. A primary key is a column (almost always id) whose value uniquely identifies each row: no duplicates, ever, enforced by the database. A foreign key is a column holding some other table's primary key: opportunities.contact_id contains the id of a row in contacts, and that reference IS the relationship. There is no wire, no pointer, just a shared value the database can check and you can query across.

That one idea handles every relationship shape. One contact, many opportunities? Each opportunity carries a contact_id (a one-to-many). A pipeline owns its stages? Each stage carries a pipeline_id. Notice what you never do: cram a list into a cell. If a contact could have "many opportunities" you do not store opp1,opp2,opp3 in a text column; you give each opportunity its own row pointing back. Rows are cheap. Lists-in-cells are the classic amateur tell, and they destroy your ability to query.

SQL is the language for both defining and using the schema. It reads close to English, and the core is four verbs plus JOIN:

SELECT name, email FROM contacts WHERE created_at > '2026-01-01';
INSERT INTO contacts (name, email) VALUES ('Dana Whitfield', 'dana@example.com');
UPDATE contacts SET phone = '555-0141' WHERE id = 1;
DELETE FROM contacts WHERE id = 3;

The one habit to install immediately: UPDATE and DELETE take a WHERE clause, and without one they hit every row in the table. No confirmation, no undo. Type the WHERE first if you have to. Every database veteran has one story about this; the goal is for yours to happen today, in a scratch database, on purpose... or never.

JOIN is how you query across a relationship. "Each opportunity with its contact's name" means: match rows of opportunities to rows of contacts where the foreign key equals the primary key:

SELECT contacts.name, opportunities.value_cents
FROM opportunities
JOIN contacts ON contacts.id = opportunities.contact_id;

Indexes, at intuition level: without an index, finding rows WHERE stage_id = 3 means the database reads every row and checks (a full scan). An index on stage_id is a presorted lookup structure, like a book index, that jumps straight to the matching rows. Small tables do not care. At hundreds of thousands of rows, the difference is milliseconds versus seconds. The tradeoff: each index slightly slows writes (it must be maintained) and the rule of thumb is to index columns you routinely filter or join on, when the table is big enough to feel it.

One more decision hiding in the lab below: money is stored as integer cents (value_cents), never as a decimal dollars column. Fractional types introduce rounding surprises (P.10's debugging gym will make this visceral); integers never lie.

Tutor first

The lab: build the CRM

SQLite is a complete SQL database in a single file, no server, no account, and it is the perfect practice environment (it is also, not incidentally, a database you will ship real things on). macOS includes the sqlite3 command already. On Linux or WSL: sudo apt install sqlite3.

cd && mkdir -p practice && cd practice
sqlite3 crm.db

CREATE TABLE contacts (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE,
  phone TEXT
);

CREATE TABLE pipelines (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE stages (
  id INTEGER PRIMARY KEY,
  pipeline_id INTEGER NOT NULL REFERENCES pipelines(id),
  name TEXT NOT NULL,
  position INTEGER NOT NULL
);

CREATE TABLE opportunities (
  id INTEGER PRIMARY KEY,
  contact_id INTEGER NOT NULL REFERENCES contacts(id),
  stage_id INTEGER NOT NULL REFERENCES stages(id),
  value_cents INTEGER NOT NULL DEFAULT 0
);

INSERT INTO contacts (name, email, phone) VALUES
  ('Dana Whitfield', 'dana@example.com', '555-0141'),
  ('Marcus Lee', 'marcus@example.com', '555-0177'),
  ('Priya Nair', 'priya@example.com', NULL);

INSERT INTO pipelines (name) VALUES ('Sales');

INSERT INTO stages (pipeline_id, name, position) VALUES
  (1, 'New Lead', 1),
  (1, 'Discovery Call', 2),
  (1, 'Proposal Sent', 3),
  (1, 'Closed Won', 4);

INSERT INTO opportunities (contact_id, stage_id, value_cents) VALUES
  (1, 3, 480000),
  (2, 2, 1200000),
  (3, 3, 250000),
  (1, 4, 990000);

SELECT contacts.name, stages.name AS stage, opportunities.value_cents / 100.0 AS value
FROM opportunities
JOIN contacts ON contacts.id = opportunities.contact_id
JOIN stages ON stages.id = opportunities.stage_id
ORDER BY stages.position;

SELECT stages.name, COUNT(*) AS deals, SUM(opportunities.value_cents) / 100.0 AS total
FROM opportunities
JOIN stages ON stages.id = opportunities.stage_id
GROUP BY stages.id
ORDER BY stages.position;

UPDATE opportunities SET stage_id = 3 WHERE id = 2;
UPDATE contacts SET phone = '555-0163' WHERE id = 3;

CREATE INDEX idx_opportunities_stage ON opportunities(stage_id);

Where it breaks

Three modeling failures you will meet in the wild, all preventable with what you now know. Lists in cells: a tags column containing "vip,hot,demo". Querying "all vip contacts" becomes string parsing, counts become impossible, and one rename touches every row. Tags want their own table. Missing keys: tables related by vibes, where orders.customer holds a name typed by hand. Names collide and drift; keys do not. Redundant copies: the same fact stored in two tables "for convenience," which works until the two copies disagree and nobody knows which one is true. Store each fact once; JOIN when you need it elsewhere.

And the agent-era version of the warning: when you ask an agent to "add a feature," it will happily add columns and tables to make the feature work locally. Whether those additions respect the existing model is a judgment call, and it is yours. Schema review is the least skippable review, because schema mistakes are the ones you cannot refactor away on a Tuesday afternoon.

Knowledge check

Sources

SQL's core (SELECT, JOIN, keys, indexes) has been stable since before you had email, and SQLite's CLI ships with macOS and installs from every Linux package manager. No volatile claims to cite: every statement and result in the lab was executed against SQLite during authoring (July 2026), and yours is the rerun that matters.