Tables, rows, and 3NF

Picture the feature you’ve been handed: organizations send invoices, each with line items that carry a description, a quantity, and a price, plus tags so finance can filter them later. An ordinary slice of a SaaS product, the kind you’ll build a dozen times.

Before the first component or query comes one decision that quietly sets how hard everything after it will be: the shape of the data on disk. What are the tables? What columns, of what types? Which row points at which? An engineer settles this first, on purpose, because once the shape is wrong every component, query, and API route has to bend around the mistake.

This lesson answers one question: given a feature spec, what shape does the data take before any code exists? You’ll take a spec like the invoicing one and sketch its tables, columns, keys, and relationships, normalized so every fact lives in exactly one place.

A database is tables, rows, and typed columns

The relational model nests three ideas. A database is a set of tables, a table is a set of rows, and a row is a set of columns where each column has a declared type. Add references between tables and that’s the whole model.

The key word is typed. Every column declares what kind of value it holds, and the database refuses anything that doesn’t fit. A text column holds strings, an integer holds whole numbers, a boolean holds true or false, and a timestamptz holds an instant in time. A numeric holds an exact decimal, the type you reach for with money, because it won’t quietly round the way a floating-point number does. Write the string "banana" into an integer column and the database rejects it at the boundary, on the way in, rather than letting it surface as a problem later.

id uuid	name text	billing_email text
a1f3…	Acme Inc.	billing@acme.example
b7c2…	Globex	ap@globex.example
c9d4…	Initech	finance@initech.example

An organizations table: a header row naming each column and its type, then the rows themselves.

Why does it matter that the database enforces types when your TypeScript already does? Because TypeScript only describes data while it’s inside your running program. The moment data leaves that program, to sit in storage, get touched by a migration, or arrive from an API you forgot to validate, the TypeScript guarantee is gone. A type declared in TypeScript can be bypassed; a column type declared in Postgres cannot. The database is the last line of defense: when application code drifts or a migration does something careless, the column type still rejects the bad write.

There’s a tempting shortcut where you stop declaring real column types and shove everything into one loosely-typed blob; we’ll return to when that trade is worth making in the denormalization section.

Here’s the organizations table in raw SQL.

create table organizations (
  id uuid primary key,
  name text not null,
  billing_email text not null,
  created_at timestamptz not null default now()
);

This is DDL : a table named organizations with four typed columns plus two rules. The name and billing_email columns can’t be empty (not null), and a new row’s created_at defaults to the current time if you don’t supply one. You’ll rarely write this by hand; in the next chapter Drizzle generates it from your TypeScript, same shape in a different language.

Primary keys and foreign keys

A table of rows isn’t useful until you can point at one specific row and connect a row in one table to a row in another. Those are the jobs of the two kinds of key.

The primary key

Every row needs a unique name: a column whose value differs for every row, so you can say “that row” without ambiguity. That column is the primary key. In the organizations table it’s id: give me an id and I find exactly one organization.

A primary key can also span several columns that are unique only in combination, a case we’ll need shortly. Whether the key is a value the system invents (a UUID, an auto-incrementing number) or one that already means something in the real world (an email, a slug) is a decision we leave for the next chapter.

The foreign key

A foreign key is a column in one table whose values must exist as a primary key in another.

An invoice belongs to an organization, so the invoices table carries an organization_id, and every value in it has to be a real id over in organizations. The database won’t let you create an invoice pointing at a nonexistent organization: the reference is enforced, not a convention everyone agrees to honor.

The foreign key lives on the many side (invoices) and points at the primary key it references (organizations.id). With many invoices per organization, the single reference sits on the invoice.

Notice where the foreign key lives: on invoices, not organizations. An organization has many invoices, but each invoice belongs to exactly one organization, so the single reference sits on the invoice. What happens to those invoices when their organization is deleted is another decision we leave for the next chapter.

Every fact in one place: normalizing to 3NF

Suppose you skip the modeling and build the obvious thing: one invoices table holding everything needed to render an invoice. Its own data, plus the organization’s name and billing email on every row so you never look elsewhere, plus a tags column where you stuff the tags as a comma-separated string.

id	number	organization_name	organization_email	tags	total
a1f3…	INV-001	Acme Inc.	billing@acme.example	urgent,paid	1200.00
b7c2…	INV-002	Acme Inc.	billing@acme.example	paid	340.00
c9d4…	INV-003	Acme Inc.	billing@acme.example	q3,paid	980.00
d5e6…	INV-004	Globex	ap@globex.example	urgent	5600.00

One invoices table that crams in facts about other things. Acme's name and billing email (highlighted) are stored once per invoice — the same fact, four times over. This is the design to avoid.

Look at what happened. “Acme Inc.” appears on every Acme invoice, and so does their billing email. You haven’t stored Acme’s name once; you’ve stored it once per invoice. When Acme rebrands to “Acme Corp.”, you must update the name on every Acme row: twelve invoices, twelve updates. Miss one, and the database now says Acme is named two different things at once, with no way to tell you which is true. It never knew they were supposed to agree.

That failure has a name: an update anomaly . It isn’t a rare edge case; it’s the default outcome of duplicated data over a long enough timeline.

The same fact stored in two places will eventually disagree.

The tags column has its own version of the problem. To Postgres, "urgent,paid,q3" is just a string. You can’t query it without fragile substring matching that also catches urgenttt, you can’t put a foreign key on it to guarantee the tags are real, and you can’t index it for fast lookups. Packing three values into one cell throws away everything the database is good at.

The fix is a single idea:

Every fact lives in exactly one place.

Acme’s name is a fact about Acme, so it lives in one row in an organizations table and nowhere else. A tag is its own thing, so it lives in its own row. Normalization is the practice of getting there. The formal theory numbers it 1, 2, and 3, but these aren’t separate theories; they’re three checks against that one idea.

create table invoices (
  id uuid primary key,
  number text not null,
  organization_name text not null,
  organization_email text not null,
  tags text not null,
  total numeric not null
);

1NF, atomic columns. tags packs a list into a single cell. First normal form wants one value per cell, so a comma-separated list breaks it. The fix is a separate row per tag, not a string.

create table invoices (
  id uuid primary key,
  number text not null,
  organization_name text not null,
  organization_email text not null,
  tags text not null,
  total numeric not null
);

3NF, no fact about something else. These columns describe the organization, not the invoice. organization_name and organization_email are facts about the org, fixed by which org this is, so they belong in an organizations table, referenced by a foreign key. Third normal form catches this.

create table invoices (
  id uuid primary key,
  number text not null,
  organization_name text not null,
  organization_email text not null,
  tags text not null,
  total numeric not null
);

What’s left is genuinely about the invoice: its id, number, and total. (I skipped second normal form. It only has something to say when the primary key spans multiple columns; with a single-column key like id, you satisfy it for free. More on that next.)

← Previous 1 / 1 Next →

Each form is one check:

1NF wants every cell atomic : one value, no comma-separated lists, no address1/address2/address3 standing in for one.

2NF only bites when the primary key spans several columns, a composite key ; a column that depends on only part of the key belongs elsewhere. A single-column key like id satisfies it for free, which is why the walkthrough skipped it.

3NF wants no non-key column to depend on another non-key column, the classic trap being city stored beside zip when zip already determines city.

Higher normal forms exist, and you may see BCNF, 4NF, and 5NF named. This course stops at 3NF: the overwhelming majority of SaaS data fits it cleanly, and the higher forms rarely earn the complexity they add.

Watch video

Decomplexify’s 28-minute walkthrough of every normal form, 1NF through 5NF, example by example. A thorough second pass on the three forms above, plus the higher ones we skip.

Before we build the real schema, make sure the three problems are distinct in your head.

Sort each design by the problem it has — or whether it's already clean. Drag each item into the bucket it belongs to, then press Check.

Properly normalized Every fact in one place

1NF violation A cell holds more than one value

3NF violation A column is a fact about something else

A roles column holding "admin,billing,member" on the members table.

country stored next to country_code on every addresses row, where the code already fixes the country.

An author_id foreign key on a posts table pointing at users.id.

A single phone_numbers column with values separated by semicolons.

plan_price copied onto every subscriptions row, where the price is really a property of the plan.

A tag_id and post_id pair in a post_tags table, each a foreign key.

Check Reset

Building the normalized invoicing schema

We have the invoicing spec; let’s turn it into four normalized tables, built one at a time.

Start with organizations, the customer the whole feature hangs off. It needs an id primary key, a name, and a billing_email. Acme’s name lives here, in one row, and nowhere else, so the update anomaly from the bad design is now impossible: rebrand Acme and you change one cell.

Add invoices. An invoice has its own facts: a number, a status, an issued_at timestamp, a total. It belongs to an organization, so it carries organization_id, a foreign key pointing at organizations.id. The organization’s name is not on this table; to render it on the invoice, follow the foreign key.

Add invoice_line_items. Each invoice is made of lines, each with a description, a quantity, and a unit_price. A line item belongs to one invoice, so it carries invoice_id, a foreign key to invoices.id. Same one-to-many shape, one level down.

Add tags, and this one’s different. A tag like urgent isn’t owned by a single invoice: many invoices can carry it, and one invoice can carry many tags. That’s a many-to-many relationship, and no single foreign key on either side can express it. So you do two things. First, a tags table with id and name, so each tag’s name lives in one place (no more comma-separated string). Then a junction table , invoice_tags, with two columns, invoice_id and tag_id, each a foreign key. One row means “this invoice has this tag.” Tag three invoices with urgent and you get three rows, all pointing at the same tags row.

One detail ties back to normalization: the primary key of invoice_tags is the pair (invoice_id, tag_id) together, a composite key. That’s where second normal form finally has something to say.

Here is the whole schema at once.

The invoicing schema, normalized to 3NF. Each org’s name lives only in organizations, each tag’s name only in tags, and every invoice’s org is a single foreign key. The junction invoice_tags carries one foreign key to each side, and its primary key is the pair (invoice_id, tag_id), so invoices and tags relate many-to-many through it.

Trace one fact through the diagram and the point lands. Acme’s name? One cell, in one row, in organizations. The tag urgent? One row in tags, however many invoices carry it. An invoice’s organization? A single foreign key: follow it when you need the name, don’t copy it. Nothing is stored twice, so nothing can drift out of sync.

One objection remains: doesn’t all this splitting make the data harder to read back? If the org name isn’t on the invoice, don’t you do extra work to show it?

Normalized data isn't hard to read back. The query below joins each invoice to its organization and aggregates its tags, so the org name you 'moved away' comes back with a single join. Run it as-is, then tweak it: add a WHERE to filter to one organization, or change the ORDER BY to sort by total.

View schema & data

-- Four normalized tables plus the junction. Each fact lives in one place.
create table organizations (
  id uuid primary key,
  name text not null,
  billing_email text not null
);

create table invoices (
  id uuid primary key,
  organization_id uuid not null references organizations (id),
  number text not null,
  status text not null,
  total numeric not null
);

create table invoice_line_items (
  id uuid primary key,
  invoice_id uuid not null references invoices (id),
  description text not null,
  quantity integer not null,
  unit_price numeric not null
);

create table tags (
  id uuid primary key,
  name text not null
);

create table invoice_tags (
  invoice_id uuid not null references invoices (id),
  tag_id uuid not null references tags (id),
  primary key (invoice_id, tag_id)
);

-- Each org's name is stored exactly once, here.
insert into organizations (id, name, billing_email) values
  ('00000000-0000-0000-0000-0000000000a1', 'Acme Inc.', 'billing@acme.example'),
  ('00000000-0000-0000-0000-0000000000b7', 'Globex',    'ap@globex.example'),
  ('00000000-0000-0000-0000-0000000000c9', 'Initech',   'finance@initech.example');

-- The org is a single foreign key on each invoice, not a copied name.
insert into invoices (id, organization_id, number, status, total) values
  ('10000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-0000000000a1', 'INV-001', 'paid', 1200.00),
  ('10000000-0000-0000-0000-000000000002', '00000000-0000-0000-0000-0000000000a1', 'INV-002', 'open',  340.00),
  ('10000000-0000-0000-0000-000000000003', '00000000-0000-0000-0000-0000000000a1', 'INV-003', 'paid',  980.00),
  ('10000000-0000-0000-0000-000000000004', '00000000-0000-0000-0000-0000000000b7', 'INV-004', 'open', 5600.00);

insert into invoice_line_items (id, invoice_id, description, quantity, unit_price) values
  ('20000000-0000-0000-0000-000000000001', '10000000-0000-0000-0000-000000000001', 'Pro plan, annual', 1, 1000.00),
  ('20000000-0000-0000-0000-000000000002', '10000000-0000-0000-0000-000000000001', 'Extra seats',       4,   50.00),
  ('20000000-0000-0000-0000-000000000003', '10000000-0000-0000-0000-000000000004', 'Enterprise plan',   1, 5600.00);

-- Each tag name is stored exactly once, here.
insert into tags (id, name) values
  ('30000000-0000-0000-0000-000000000001', 'urgent'),
  ('30000000-0000-0000-0000-000000000002', 'paid'),
  ('30000000-0000-0000-0000-000000000003', 'q3');

-- The junction: one row means "this invoice wears this tag".
insert into invoice_tags (invoice_id, tag_id) values
  ('10000000-0000-0000-0000-000000000001', '30000000-0000-0000-0000-000000000001'),
  ('10000000-0000-0000-0000-000000000001', '30000000-0000-0000-0000-000000000002'),
  ('10000000-0000-0000-0000-000000000002', '30000000-0000-0000-0000-000000000002'),
  ('10000000-0000-0000-0000-000000000003', '30000000-0000-0000-0000-000000000003'),
  ('10000000-0000-0000-0000-000000000003', '30000000-0000-0000-0000-000000000002'),
  ('10000000-0000-0000-0000-000000000004', '30000000-0000-0000-0000-000000000001');

Run query Reset

The org name you “moved away” comes back with a single join, and unlike the comma-separated tags string, you can now filter, count, and constrain every piece.

The three cardinalities: 1:1, 1:N, N:M

Each relationship shape is a cardinality , and these three cover essentially every relationship you’ll model. Once you can spot which one a spec calls for, modeling stops being guesswork.

1:1

One row in A matches at most one row in B. The settings row reuses the user's id as both its primary and foreign key.

1:N

One invoice, many line items. The foreign key lives on the many side, here the line item.

N:M

Many invoices, many tags. A junction table holds a foreign key to each side, keyed on the pair (invoice_id, tag_id).

One-to-one (1:1). One row in A matches at most one row in B. It’s the rarest of the three, and usually better as a single table. Split it only when the B columns are optional and usually absent, large, or under different permissions than the A columns, as with a users table and a user_settings table sharing a key.

One-to-many (1:N). One row in A relates to many rows in B, with a single foreign key on the many side. This is the workhorse you’ll reach for far more than the others. When a spec says “an X has several Ys,” the foreign key goes on the Y, just as invoice_id sits on the line item.

Many-to-many (N:M). Many rows in A relate to many rows in B, so neither side can hold a single foreign key. You introduce a junction table with two foreign keys, one to each side, keyed on their composite, the way invoice_tags wires invoices and tags together. When a spec says “an X can have many Ys and a Y can belong to many Xs,” reach for a junction.

Spot “has many of those” and the foreign key goes on the many side; spot “relate both ways” and you need a junction.

Start at 3NF, denormalize only on a measurement

You now know how to normalize. The temptation, when you’re new, is to treat normalization as the cautious-beginner setting and “real” performance work as deliberately de-normalizing: flattening tables, copying columns, reaching for one loose blob because joins feel like overhead. That instinct is backwards.

The default is 3NF, and you stay there until something specific forces your hand. This isn’t training wheels you graduate from: a normalized schema is where the engine is strongest. The query planner is built to join normalized tables efficiently, foreign keys keep your data honest for free, and reading it back is a one-line join. Most web-app data fits 3NF without strain.

Denormalization is a measured response to a read pattern, never a starting point. You do it after you’ve shipped the normalized schema, after a measurement shows a specific problem, and only at the spot it points to. There are exactly three situations where it’s the right call.

The first is a hot read path where the join cost is measured and material. A high-traffic feed that joins comments to users thousands of times a second to show a username: if you profile it and the join is genuinely the bottleneck, copying the username onto the comment row is a defensible trade. The weight is on measured: you ran the numbers, you didn’t guess.

The second is a reporting or aggregate table built by a scheduled job. Computing daily revenue per organization from raw invoices on every page load is wasteful, so a job rolls it up into a daily_revenue table once a day. That table is denormalized on purpose, but it stays derived from the source-of-truth tables and rebuildable at any time; the real invoices are the authority, never it.

The third is jsonb for data that genuinely has no stable shape. An audit-log payload or a raw webhook body: every row looks different, so there’s no schema worth modeling. A jsonb column (Postgres’s binary JSON type) is the right tool here, and the exception to the typed-columns rule from earlier: you give up column-level type safety because there’s no schema to enforce, not to skip the discipline.

Then there’s the trigger that feels legitimate but isn’t: “the joins feel slow.” No measurement, just a hunch, and it’s the one that catches juniors. When a normalized query is actually slow, the cause is almost never the number of joins; it’s a missing index (a lookup structure that finds rows without scanning the whole table). Denormalizing to fix a query you never profiled is premature optimization: you take on the update-anomaly cost permanently to buy a win you never confirmed you needed. Indexes and the EXPLAIN ANALYZE command that shows where a query spends its time get their own chapter, and they come before you touch the schema.

Should I denormalize this?

A teammate opens a pull request that copies organization_name onto every row of the invoices table. The description reads, in full: “The invoice list felt a bit slow with the org join, so I denormalized the name onto the invoice.” You’re the reviewer. Before you approve, what do you ask them for?

The EXPLAIN ANALYZE output proving the join is where the query actually spends its time — not a hunch that it’s slow.

Stuff the organization’s fields into a jsonb column on the invoice so there’s no join at all.

Collapse invoices and organizations into a single wide table and drop the foreign key.

Put the whole rendered invoice list behind a cache so most requests skip the database.

Why this course uses Postgres

The typed columns, enforced foreign keys, normalized shape, and jsonb escape hatch in this lesson all assume one database engine, and the rest of this course uses Postgres .

Postgres gives you a strict relational core and a pressure valve for the rare unstructured case, in one engine. You get full SQL with real, enforced constraints, so your normalized model is a guarantee the database keeps rather than a convention you hope everyone follows; jsonb for the shapeless five percent; and a deep bench of features like generated columns and partial indexes that you don’t need yet but can grow into without switching databases. Drizzle, Neon, and the wider Postgres ecosystem build on top of it.

You could reach for something else. Here’s why you mostly won’t.

MySQL

A capable relational SQL database; Postgres edges it out for this stack on jsonb, extensions, and richer constraints.

SQLite

Excellent embedded, in-process, or local, but not the default for a multi-user cloud backend.

Document stores

MongoDB and friends are the wrong default for relational data: you re-implement joins and constraints in application code, the exact place those guarantees can be skipped.

A document store doesn’t make the relationships in your data go away: invoices still belong to organizations, tags still apply to many invoices. It moves enforcing those relationships out of the database and into application code, the place we said constraints get bypassed, the same trade as jsonb-everything scaled up to the whole database. For genuinely relational data, which is most SaaS data, that costs far more than it saves.

Next chapter, you hand this blueprint, normalized tables that Postgres enforces, to Drizzle.

External resources

PostgreSQL documentation

postgresql.org

The canonical reference the rest of this course leans on: versioned manuals for every Postgres feature.

PostgreSQL: JSON types

postgresql.org

The official chapter on jsonb: the one escape hatch this lesson defers, including when to reach for it and how to index it.