Data modeling

Data modeling is the practice of deciding how your data is laid out where it lives — what tables (or collections) exist, what columns (or fields) each holds, and how they connect — so that the data stays correct (it never contradicts itself) and fast (you can read and write it quickly) even as it grows from a thousand rows to a billion. It is the companion to the interview Databases and SQL lessons: those teach the query language you use to talk to a database; this teaches the shape the data should take underneath the queries. Get the shape wrong early and every feature you build on top inherits the pain — slow pages, bugs where the same fact disagrees with itself, and migrations that are terrifying to run. This lesson is self-contained: by the end you should be able to design a small schema, reason about indexes, and choose between SQL and NoSQL on purpose.

Why does the shape matter so much? Because data outlives code. You will rewrite your application many times, but the data accumulated under it — every user, order, and message — is the asset you cannot casually throw away. A clean model is forgiving: new features slot in. A muddled model is a tax you pay on every change forever. So it is worth slowing down to get the fundamentals right.

Relational basics: tables, keys, and relationships

A relational database (PostgreSQL, MySQL, SQLite, and others) stores data in tables. A table is a grid. A row (also called a record) is one horizontal entry — one customer, one order, one product. A column (also called a field) is one vertical attribute that every row has — a name, a price, a total. Think of a single spreadsheet tab: the header row names the columns, and every line below it is a row.

A primary key is a column (or small set of columns) whose value uniquely identifies a row — no two rows in the table may share it, and it is never empty. It is the row's permanent name tag. In practice this is almost always a column called id: an auto-incrementing integer (1, 2, 3, …) or a random UUID. Uniqueness is the whole point: when you say "order 4071," exactly one row answers. Without a primary key you have no reliable way to point at "that specific row" to update or delete it.

Tables connect to each other through a foreign key: a column in one table that stores the primary key of a row in another table. It is a typed pointer from one record to another. Consider three tables for an online store — customers, orders, and products:

CREATE TABLE customers (
  id        SERIAL PRIMARY KEY,        -- the unique name tag for each customer
  name      TEXT NOT NULL,
  address   TEXT
);

CREATE TABLE orders (
  id          SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL
              REFERENCES customers(id),  -- foreign key: each order belongs to one customer
  total       NUMERIC
);

The customer_id column on orders points each order at exactly one customer. That is a one-to-many relationship: one customer has many orders, but each order has exactly one customer. The "many" side is the side that holds the foreign key. To read "all of customer 7's orders," you ask WHERE customer_id = 7.

Some relationships are many-to-many: an order contains many products, and each product appears in many orders. You cannot model that with a single foreign-key column, because a column holds one value, not a list. The standard solution is a third table — a join table (also called a junction or bridge table) — whose rows are pairs of foreign keys, one pair per connection:

-- many-to-many: each row links one order to one product (with a quantity)
CREATE TABLE order_items (
  order_id   INTEGER REFERENCES orders(id),
  product_id INTEGER REFERENCES products(id),
  quantity   INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)   -- the pair is unique
);

An order with three products is three rows in order_items. A product sold in 900 orders appears in 900 rows. Any number on either side becomes any number of pair-rows in the middle — that is exactly what "many-to-many" needs.

Normalization: store each fact exactly once

Normalization is the discipline of arranging your tables so that every fact lives in exactly one place. The reason is not tidiness for its own sake — it is correctness. When a fact is stored in two places, the two copies can drift apart, and then your data contradicts itself, with no way to know which copy is right.

Here is the classic mistake, concretely. Imagine you skipped the customers table and instead copied each customer's shipping address directly into every order row:

-- UN-normalized: the address is duplicated on every order
orders
 id | customer_name | address           | total
----+---------------+-------------------+------
 1  | Mei           | 12 Oak St, Reston | 40
 2  | Mei           | 12 Oak St, Reston | 18
 3  | Mei           | 12 Oak St, Reston | 95

Mei moves house. Her address is now stored in three rows (and next year, three hundred). To update it you must find and change every row. Miss one — and at scale you will — and your database now claims Mei lives at two different addresses at once. That is called an update anomaly: a single real-world change requires many writes, and any miss creates a contradiction. The fix is to store the address once, on the customer, and have orders merely reference the customer:

-- normalized: the address lives once, on the customer
customers                          orders
 id | name | address               id | customer_id | total
----+------+----------------       ----+-------------+------
 7  | Mei  | 12 Oak St, Reston      1  | 7           | 40
                                    2  | 7           | 18
                                    3  | 7           | 95

Now Mei's move is a single UPDATE customers SET address = '...' WHERE id = 7; and every order automatically sees the new value, because the orders never held a copy in the first place. You read the address back by joining the two tables on the key (the SQL lesson covers JOIN in depth).

Database theory names the steps of this process — first normal form (1NF), second (2NF), and third (3NF) — but for everyday work you can read them as one idea applied progressively: remove duplication step by step until each fact sits in exactly one place. 1NF roughly means "one value per cell, no comma-separated lists." 2NF and 3NF mean "don't store a fact in a table where it doesn't fully depend on that table's key" — for example, don't repeat the customer's address on the order. You rarely recite these in practice; you internalize the goal and the forms follow.

Denormalization: trading safety for read speed, on purpose

Normalization keeps writes safe, but it spreads data across tables — so reads must join the pieces back together. Joins are cheap on small data and increasingly expensive as tables grow and queries fan out across many of them. Denormalization is the deliberate decision to duplicate some data, reversing a bit of normalization, so that a hot read becomes fast and join-free.

Concrete example: your order-history page shows each order with the customer's name. Normalized, every render joins orders to customers to fetch the name. If that page is viewed millions of times a day, you might copy customer_name onto the order row so the page reads one table and no join:

-- denormalized for read speed: name is duplicated onto the order
orders(id, customer_id -> customers.id, customer_name, total)
--                                       ^^^^^^^^^^^^^ a deliberate copy

The cost is real and you must accept it consciously: you now own keeping the copies in sync. When a customer renames themselves, you must update both the customers row and every cached customer_name on their orders — the very update anomaly normalization was protecting you from. So denormalize only when (1) reads vastly outnumber writes, (2) you have measured the join as a real bottleneck, and (3) the duplicated fact rarely changes. Denormalize on purpose, never by accident.

Indexes: the single biggest query lever

An index is a separate, sorted lookup structure the database builds and maintains for one or more columns — exactly like the index at the back of a textbook. The book's pages are in reading order, but the index lists topics alphabetically with page numbers, so you can jump to "normalization, p. 142" without reading the whole book. A database index does the same for a column: it keeps the values in sorted order with pointers to the matching rows.

Without an index, answering WHERE email = 'mei@x.com' forces a full-table scan: the database reads every row and checks each one. On ten million users that is ten million reads to find one person. With an index on email, the database does a seek — it binary-searches the sorted structure and lands on the row in a handful of steps. The intuition, the way EXPLAIN (the command that shows a query's plan) would describe it:

-- no index on email:
EXPLAIN SELECT * FROM users WHERE email = 'mei@x.com';
   -> Seq Scan on users   (reads all 10,000,000 rows)   # slow

-- after: CREATE INDEX idx_users_email ON users(email);
EXPLAIN SELECT * FROM users WHERE email = 'mei@x.com';
   -> Index Scan using idx_users_email   (touches ~1 row)   # fast

That swing — from "read ten million" to "read one" — is the single biggest query-performance lever most engineers ever touch. Indexes also accelerate JOINs (the database seeks the matching key instead of scanning) and ORDER BY (the data is already sorted), which is why you index foreign-key columns and columns you sort on.

SQL vs NoSQL: choose by access pattern

There is no universally "better" database. You choose by how the data will actually be used — its access pattern — not by which technology is fashionable.

Reach for relational / SQL when data is structured, relationships matter, and you need rich, unpredictable queries plus strong correctness guarantees. The headline guarantee is transactions / ACID — a set of writes either all happen or none do (a checkout that debits inventory and charges the card must not do one without the other), and the database stays consistent throughout. ACID stands for Atomicity (all-or-nothing), Consistency (rules always hold), Isolation (concurrent transactions don't corrupt each other), Durability (committed data survives a crash). SQL also gives you JOINs, so you can answer questions you didn't anticipate at design time.

Reach for NoSQL when you need extreme horizontal scale, a flexible or per-record schema, or just simple known lookups. NoSQL is an umbrella over several shapes:

• Document (MongoDB, DynamoDB): stores a self-contained JSON-like blob per record; great when each record varies and is read whole.
• Key-value (Redis, DynamoDB): a giant hash map — give a key, get a value; fastest possible "one lookup by id."
• Wide-column (Cassandra, Bigtable): rows with flexible columns, built for massive write throughput across many machines.

The senior framing: in SQL you model around the data and let flexible queries follow; in NoSQL you model around your queries. You deliberately shape each document to match how you will read it — even duplicating data that a relational design would forbid — because there is no join to assemble it later. Many large systems use both, picking the right store per workload; the choice is part of system design.

Migrations: schema changes are code

Your schema will change — new features need new columns and tables. A migration is a versioned script that alters the schema. Treat it exactly like application code: it is checked into source control, code-reviewed, and applied forward in a fixed order, so every environment (your laptop, staging, production) reaches the identical schema by running the same numbered steps. Never edit a production schema by hand; you'd have no record of what changed or how to reproduce it.

The senior habit is backward-compatible migrations: structure each change so the previous version of your code keeps working after the migration runs, which means a bad deploy can roll back without corrupting data. The danger is a destructive, one-way change shipped in the same deploy as the code that depends on it — for example, renaming or dropping a column the old code still reads. If you roll the code back, it now queries a column that no longer exists, and breaks.

The pattern that makes this safe is expand / contract (also called parallel change): split a risky change into additive steps, deploy them separately, and only remove the old thing once nothing uses it. Concretely, to rename a column addr to address:

-- EXPAND (deploy 1): add the new column, write both, read old
ALTER TABLE customers ADD COLUMN address TEXT;   # nullable, safe to roll back
--   backfill old data, app now writes addr AND address

-- MIGRATE reads (deploy 2): app reads 'address' instead of 'addr'

-- CONTRACT (deploy 3, later): only once nothing reads 'addr'
ALTER TABLE customers DROP COLUMN addr;

The simplest version of this is just "add a nullable column or a new table." Adding is almost always safe and reversible; removing requires proof that nothing depends on the thing anymore. When in doubt, expand now and contract in a later, separate deploy.