Tables, rows, and 3NF

Picture the feature you’ve been handed: organizations send invoices. Each invoice has line items, with a description, a quantity, and a price. Invoices can be tagged so finance can filter them later. It’s an ordinary slice of a SaaS product, the kind you’ll build a dozen times.

You already know how to render this. By now you’ve built lists and detail views, and you’ve wired up routes, so you might reach straight for the components. But one decision comes before the first component, before the first query, before the first line of UI, and it quietly decides how hard everything after it will be.

That decision is the shape of the data on disk. What are the tables? What columns does each one hold, and of what type? Which row points at which other row? An experienced engineer settles this first, on purpose, because once the shape is wrong every component, every query, and every API route has to bend around the mistake. The shape underneath constrains everything you build on top of it.

So that’s the question this lesson answers: given a feature spec, what shape does the data take before any code exists? By the end you’ll be able to take a spec like the invoicing one and sketch its tables, columns, keys, and relationships, normalized so that every fact lives in exactly one place. That sketch is the blueprint. A couple of chapters from now you’ll encode it with Drizzle; here, you learn to draw it.

A database is tables, rows, and typed columns

The relational model has a reputation for being heavy, but it isn’t. Underneath the decades of theory, it’s three nested ideas you can hold in your head all at once.

A database is a set of tables. A table is a set of rows. A row is a set of columns, and each column has a declared type. That’s the whole model: no graph of objects, no nested documents, just tables, the rows inside them, and references between them. When people say “relational,” the relation is the table itself, a named set of rows with a fixed set of columns.

The key word there 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 column holds whole numbers. A timestamptz column holds an instant in time. A boolean holds true or false. 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. Try to write the string "banana" into an integer column and the database rejects it right at the boundary, on the way in, rather than letting it through with a warning to surface 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 so much that the database enforces types, when your TypeScript already does? Because TypeScript only describes the data while it’s inside your running program. The moment data leaves that program, whether it sits in storage, gets touched by a migration, or arrives from an API you forgot to validate, the TypeScript guarantee is gone. A type you declared in TypeScript can be bypassed; a column type declared in Postgres cannot. The database is the last line of defense. When your application code drifts, when a validation schema has a hole, when a hand-run migration does something careless, the column type is still standing there rejecting the bad write.

Hold on to that idea, because it has a twin you’ll meet later in this lesson. There’s a tempting shortcut where you stop declaring real column types and shove everything into one loosely-typed blob, “stringly-typed everything.” It feels flexible. What it actually does is move the constraint out of the database and up into your application code, the exact place we just said the guarantee can be skipped. We’ll come back to when that trade is worth making and when it’s a trap.

Now, what does a table actually look like when you define it? Here’s the organizations table expressed 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 . Read it top to bottom and it says exactly what the model promised: a table named organizations, with four columns, each one carrying a type, plus a couple of 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 will almost never write SQL like this by hand. I’m showing it so the abstract model has a concrete face, so that when this lesson says “a typed column,” you’ve seen the literal line of code that declares one. In a couple of chapters you’ll define your tables in TypeScript with Drizzle, and the tool will generate this CREATE TABLE for you. The shape is the same either way; only the language you write it in changes.

Primary keys and foreign keys: how rows point at rows

A table full of rows isn’t useful until you can do two things: 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: one column whose value is different 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 can find exactly one organization, never two, never zero-or-many.

A primary key can also be made of more than one column working together, a few columns that are only unique in combination. We’ll need that case shortly, so hold the thought.

That’s all you need from primary keys right now. There’s a real decision underneath: should the key be a value the system invents (a random UUID, an auto-incrementing number) or a value that already means something in the real world (an email address, a slug)? That choice has consequences, and it gets its own lesson when you start writing schemas in the next chapter. For this lesson, you just need to be comfortable with the term “primary key,” because normalization relies on it heavily.

The foreign key

The second kind of key handles the connection between tables. A foreign key is a column in one table whose values must exist as a primary key in another table. It’s the database-level way of saying “this row belongs to that one.”

To make it concrete: an invoice belongs to an organization. So the invoices table carries a column, call it organization_id, and every value in it has to be a real id over in organizations. That constraint is the whole point. The database won’t let you create an invoice pointing at an organization that doesn’t exist, and (depending on rules you’ll set later) it won’t let you delete an organization out from under its invoices. The reference is enforced, not just 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 the invoices table, not on organizations. An organization has many invoices, but each invoice belongs to exactly one organization, so the single reference sits on the invoice. That direction matters, and it’s the seed of the cardinality rules we’ll get to. There’s a deeper question here too: when an organization is deleted, what happens to its invoices, do they get deleted along with it or does the deletion get blocked? That’s a real decision, deferred to the next chapter along with the primary-key choice. For now, remember: foreign key on this side, primary key on that side, reference enforced.

Every fact in one place: normalizing to 3NF

This section is where the lesson does its real work, and I’m going to start with a mistake instead of a rule, because the rule only makes sense once you’ve seen the problem it prevents.

Suppose you skip the modeling and build the obvious thing: one invoices table that holds everything you need to render an invoice. The invoice’s own data, of course, but also the organization’s name and billing email right there on the row (so you don’t have to look anywhere else to display them), and 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. Now watch what that costs you. Acme rebrands to “Acme Corp.”, so you have to find and update that name on every single Acme row: twelve invoices, twelve updates. Update eleven of them and miss one, because the request timed out, or a filter was off by a row, or for any reason at all, and your database now says Acme is named two different things at the same time. Which one is true? The database can’t tell you. It doesn’t know they were ever supposed to agree.

That failure has a name: an update anomaly . And it’s not a rare edge case; it’s the default outcome of duplicated data over a long enough timeline. The principle to hold on to:

The same fact stored in two places will eventually disagree.

The tags column has its own version of the problem. "urgent,paid,q3" is a string as far as Postgres is concerned. You can’t ask “give me every invoice tagged urgent” without resorting to fragile substring matching, which will happily also match a tag named urgenttt. You can’t put a foreign key on it to guarantee the tags are real. You can’t index it for fast lookups. By packing three values into one cell you threw away everything the database is good at.

So here’s the fix, the single idea that the rest of this section is just three ways of checking:

Every fact lives in exactly one place.

That’s the working definition of a normalized schema. 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 breaks it into “normal forms” numbered 1, 2, and 3, but you don’t need to memorize them as three separate theories. They’re three checks against the one idea above.

Let me walk the bad table through those three checks.

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 of values into a single cell. First normal form says 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. This invoice row is describing the organization, not the invoice. organization_name and organization_email are facts about the org, determined by which org this is, so they belong in an organizations table, referenced by a foreign key. Third normal form is what 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, its number, its total. (You’ll notice I skipped second normal form. It only has anything to say when the primary key is made of multiple columns. With a single-column key like id, you satisfy it for free. More on that in a moment.)

← Previous 1 / 1 Next →

That’s the three normal forms, in the order you actually meet them:

First normal form, atomic columns. Each cell holds one value, and that value has to be atomic . No comma-separated lists like tags: "urgent,paid,q3". No numbered repeating columns like address1, address2, address3 standing in for a list. The comma-separated tags column is the 1NF violation; the fix is one row per tag in a table built for tags.

Second normal form, every non-key column depends on the whole key. This one only has teeth when the primary key is made of several columns together, a composite key . The classic example is a table keyed on (invoice_id, product_id) that also stores product_name. The product’s name depends only on product_id, half the key, so it doesn’t belong here; it belongs in a products table keyed on product_id alone. When your key is a single column (the common case, and the one you’ll use most), there’s no “half the key” to depend on, so 2NF is satisfied automatically. That’s why I skipped it in the walkthrough.

Third normal form, no non-key column depends on another non-key column. Every column should describe the thing the primary key names, not some other thing. The textbook trap is storing city and zip together when zip already determines city: city isn’t a fact about this row, it’s a fact about the zip code. In our invoices table the offender is organization_name, which is a fact about the organization, not about the invoice. The fix is to move it to where it belongs, an organizations table, and reference it by foreign key.

Three checks, one idea: every fact in one place.

There are higher normal forms; you may see BCNF, 4NF, and 5NF mentioned. They exist, and they address rarer problems. This course stops at 3NF, because the overwhelming majority of SaaS data fits 3NF cleanly and the higher forms rarely earn the complexity they add. It’s enough to know the names exist.

Watch video

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

Let’s make sure the three problems are distinct in your head before we build the real schema.

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

The worked schema: invoices, organizations, line items, tags

Now let’s earn the payoff. We started with the invoicing spec; let’s turn it into the four tables it actually needs, normalized, one table at a time. By the end you’ll see what “every fact in one place” looks like as a whole schema, and you’ll have the exact blueprint the next chapter encodes in Drizzle.

I’ll build it up table by table rather than dropping the finished diagram on you, because the relationships only make sense once the tables they connect already exist.

Start with organizations, the customer the whole feature hangs off. It needs an id as its primary key, a name, and a billing_email. Acme’s name lives here, in exactly one row, and nowhere else. 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. And 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; if you need it to render the invoice, you follow the foreign key. One organization has many invoices, and the single reference sits on the invoice, exactly the direction we established.

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. This is the same one-to-many shape as before, one level down: one invoice has many line items, and the foreign key lives on the line item.

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 you can’t express it with a single foreign key on either side. So you do two things. First, a tags table with id and name, so each tag’s name lives in exactly 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 in invoice_tags means “this invoice has this tag.” Tag three invoices with urgent and you get three rows, all pointing at the same one tags row.

Here’s a detail that ties back to normalization: the primary key of invoice_tags is the pair (invoice_id, tag_id) together, a composite key. That’s the case where second normal form finally has something to say. (Exactly how you wire a junction table up in code is a next-chapter concern; here you just need to recognize the shape.)

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; 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) together, so invoices and tags relate many-to-many through it.

Trace one fact through that diagram and the whole point lands. Acme’s name? One cell, in one row, in organizations. The tag urgent? One row in tags, no matter how 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. Nothing can drift out of sync, because there’s no second copy to disagree with.

Now, the objection every beginner has at this point: doesn’t all this splitting make the data a pain to read back? If the org name isn’t on the invoice, don’t I have to do extra work to show it? Let’s look.

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. (Writing queries properly is a later chapter; this is just to see that reassembling the pieces is one short statement.)

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

There it is. The org name you “moved away” comes back with a single join. One short statement reassembles the normalized pieces into exactly the row you wanted to display, and unlike the comma-separated tags string, you can now filter, count, and constrain every one of those pieces. Normalization didn’t make the data harder to get at. It made it honest, and reading it back is one line.

One-to-one, one-to-many, many-to-many

You just met all three relationship shapes in the worked schema without me naming them as a set. Let’s name them now, because once you can recognize which one a spec calls for, modeling stops being guesswork. These three shapes, the cardinalities , cover essentially every relationship you’ll ever model.

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 on the line item.

N:M

Many invoices, many tags. A junction table holds a foreign key to each side, and its primary key is the pair (invoice_id, tag_id).

One-to-one (1:1). One row in A matches at most one row in B. This is the rarest of the three, and when you reach for it you should pause, because usually two tables in a 1:1 relationship are better off as one table. The legitimate reasons to split them out are that the B columns are optional and usually absent, or large, or governed by different permissions than the A columns. The textbook case is a users table and a user_settings table sharing the same key. If you don’t have a reason like that, just make it one table.

One-to-many (1:N). One row in A relates to many rows in B, and you express it with a single foreign-key column on the many side. This is the workhorse, the relationship you’ll model far more than any other. Our invoice and its line items are exactly this: one invoice, many line items, and the invoice_id foreign key lives on the line item. Whenever a spec says “an X has several Ys,” this is the shape, and the foreign key goes on the Y.

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

Map “this entity has many of those” to foreign key on the many side, and “these two relate both ways” to junction table, and you can shape most schemas on sight.

Start at 3NF, denormalize only on a measurement

Here’s where experienced engineers and beginners visibly part ways. You now know how to normalize. The temptation, especially 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 around, reaching for one big loosely-typed blob because joins feel like overhead. That instinct is backwards, and getting it right is most of what this section is for.

The default is 3NF. You start normalized, and you stay there until something specific forces your hand. This isn’t training wheels you graduate from. A normalized schema is where the database engine is strongest: the query planner is built to join normalized tables efficiently, foreign-key constraints keep your data honest for free, and, as you just saw, reading the data back is a one-line join. Normalized is the correct default for a working engineer, not a simplification you outgrow. Most SaaS data fits 3NF without strain.

Denormalization is a measured response to a read pattern, never a starting point. That’s the line that separates the two mindsets. You denormalize after you’ve shipped the normalized schema, after something measurable has shown you a specific problem, and only in the specific spot the measurement points at. 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. Imagine a high-traffic activity feed that shows a username next to every comment. Rendering it joins comments to users thousands of times a second. If you profile that and find the join is genuinely the bottleneck, copying the username onto the comment row is a defensible trade: you accept that a username change now has to update two places, in exchange for a read that no longer joins. The word that carries the weight is measured. You ran the numbers. You didn’t guess.

The second is reporting and aggregate tables built by a job, not by hand. Say you need daily revenue per organization on a dashboard. Computing that from raw invoices on every page load is wasteful, so you precompute it: a scheduled job rolls the numbers up into a daily_revenue table once a day. That table is denormalized on purpose, but notice the discipline: it’s derived from the source-of-truth tables and rebuildable from them at any time. It is never the authority. The real invoices are.

The third is jsonb for data that genuinely has no stable shape. An audit-log entry’s payload, the raw body of a third-party webhook: data where there’s no schema worth modeling because every row looks different. For that, a jsonb column (Postgres’s binary JSON type) is the right tool. And this is the exception that proves the typed-columns rule from the start of the lesson: you give up column-level type safety here precisely because there’s no schema to enforce. You’re not skipping the discipline; there’s genuinely nothing to be disciplined about. (Querying inside jsonb is its own skill, covered in a later chapter; here it’s only “this is the one place a shapeless blob is the right answer.”)

And now the trigger that feels legitimate but isn’t: “the joins feel slow.” No measurement, just a hunch. This is the one that catches juniors, and it’s worth being blunt about: a feeling is not a measurement. 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 lets the database find rows without scanning the whole table). Denormalizing to “fix” a query you never profiled is premature optimization in its purest form: you take on the update-anomaly cost today, permanently, to buy a performance win you never confirmed you needed. The tools that tell you the truth, indexes and the EXPLAIN ANALYZE command that shows you where a query actually spends its time, get their own chapter. For now, just know they exist and that they come before you touch the schema.

Here’s the decision, walked through.

Should I denormalize this?

The point isn’t in any single leaf; it’s in the order of the questions. Measurement gates everything, and then the kind of pressure picks the kind of denormalization. Walk it that way every time.

One more question, to make the habit stick.

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 Postgres is the engine

Everything in this lesson, the typed columns, the enforced foreign keys, the normalized shape, the one jsonb escape hatch, assumes a particular database engine underneath. The rest of this course commits to Postgres . Let me tell you why, because “it’s what we use” isn’t a reason an engineer should accept.

Postgres gives you a strict relational core and the pressure valve for the rare unstructured case, in one engine. For a 2026 SaaS, that means three things. You get full SQL with real, enforced constraints, so the normalized model you just built is something the database guarantees, not a convention you hope everyone follows. You get jsonb for the shapeless five percent, so your one legitimate escape hatch is built in rather than bolted on. And you get a deep bench of features you don’t need yet but will be glad are there, like generated columns and partial indexes, that pay off later without a migration to a different database. On top of all that sits a mature ecosystem the rest of this course rides on: Drizzle for the schema and queries, Neon for hosting, and the broader world of Supabase, RDS, and friends.

You could, in principle, reach for something else. Here’s why you mostly won’t, one line each.

MySQL

A perfectly capable relational SQL database; Postgres edges it out for this stack on jsonb, extensions, and the richness of its constraints.

SQLite

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

Document stores

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

Notice the through-line in that last one. 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 just moves the job of enforcing those relationships out of the database and into your application code, which is precisely the place we said constraints can be bypassed. It’s the same trade as jsonb-everything, scaled up to the whole database. For genuinely relational data, which is most SaaS data, that trade costs you far more than it saves.

So, to recap: a database is a set of tables; a table is a set of rows; a row is a set of typed columns; one column or composite is the primary key; foreign keys wire rows to rows. You start at 3NF so every fact lives in one place, and you denormalize only against a measurement. Postgres is the engine. That’s the mental model the chapters ahead encode in code, and it’s the blueprint you’ll hand to Drizzle in the next chapter.

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.