What Is a Database Schema? A Plain-English Guide

The Junk Drawer I Called a Database

In 2023 I was building Clickly, my URL shortener, and I did what every impatient engineer does on day one: I made a users table and started throwing columns at it. Email. Password hash. Then a plan name. Then a plan_expiry. Then a stripe_id, then a last_login, then — because I was too lazy to make a second table — a column called links_json where I stuffed all of a user’s short links as one giant blob of text.

It worked. For about three weeks. Then I tried to answer a question any analytics feature needs to answer — “how many clicks did this one link get last Tuesday?” — and realized my data had no shape. Everything was technically in there, but nothing was arranged so I could actually ask questions of it. I’d skipped the one boring step that would have saved me: designing a schema.

So let me give you the thing I didn’t have — what a database schema actually is, in plain English, without the textbook fog.

---

So What Is a Schema, Really?

A database schema is the blueprint of your data. It’s the structure — what tables exist, what columns each table has, what type of data goes in each column, and how the tables connect to each other. That’s it. When someone says “design the schema,” they mean “decide the shape of your data before you start shoving data into it.”

The vocabulary is worth nailing down once, because it’s used loosely everywhere:

• A table is one kind of thing you’re storing. users. orders. links. Think of it as a spreadsheet tab.
• A column (or field) is one attribute of that thing. A users table has an email column, a created_at column, and so on. Columns are the spreadsheet’s headers.
• A row (or record) is one actual instance. One user. One order. It’s a single line in the spreadsheet.

The schema is the headers and the rules — not the rows. The rows are your data; the schema is the empty form the data has to fit into. A brand-new database with zero rows still has a full schema, the way a blank tax form has structure before anyone fills it out.

If you’ve read my SQL vs MySQL explainer, this is the layer underneath that whole conversation: SQL is the language you use to define and query the schema, and MySQL or PostgreSQL is the program that holds it.

---

Columns Have Types, and Types Are a Free Bodyguard

Every column has a data type, and this is the first place beginners under-think it. A type is a promise about what a column is allowed to contain, and the database enforces that promise for free.

The common ones you’ll actually use:

• text / varchar — strings. Names, emails, descriptions.
• integer / bigint — whole numbers. Counts, IDs.
• numeric / decimal — exact decimals. Use this for money. Never store prices as floats, or rounding will eventually turn $19.99 into $19.989999 and you’ll hate yourself.
• boolean — true/false. is_active, email_verified.
• timestamptz — a timestamp with a timezone. Use it for created_at, always the timezone version.
• uuid — a globally unique ID, if you’d rather not leak how many users you have through sequential integers.
• jsonb — a whole JSON blob inside one column, indexable and queryable. Postgres’s superpower, and the grown-up version of my links_json disaster. (Indexes are also where slow apps get fixed — see SQL query optimization techniques when a page starts crawling.)

Here’s why types matter beyond tidiness: a typed column is a bodyguard. If age is an integer, the database will physically refuse to store "twenty-five" — a whole category of bug swatted before it reaches your code. Loose typing is how you end up with dates stored as "2026-07-14", "14/07/26", and "last Tuesday" in the same column, with no way to sort them. Pick the tightest type that fits. Cheapest insurance in software.

---

Keys: How Tables Point at Each Other

This is the concept that turned my junk drawer into an actual database, so slow down here.

A primary key is the column that uniquely identifies each row in a table. Every table should have one. It’s usually a column called id — an auto-incrementing integer or a UUID — and its whole job is to be a guaranteed-unique handle for that exact row. No two users share an id. That’s the rule the primary key enforces.

A foreign key is a column in one table that points to the primary key of another table. This is how relationships work, and it’s the entire reason relational databases are called relational.

Let me make it concrete with the classic pair — users and their orders:

CREATE TABLE users (
  id         BIGSERIAL PRIMARY KEY,
  email      TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id          BIGSERIAL PRIMARY KEY,
  user_id     BIGINT NOT NULL REFERENCES users(id),
  amount      NUMERIC(10,2) NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

Read the orders table’s user_id line out loud: “this column references users(id).” That single REFERENCES clause is the foreign key, and it buys you two enormous things. First, it documents the relationship — anyone reading the schema instantly knows an order belongs to a user. Second, the database will now refuse to insert an order whose user_id doesn’t match a real user. You cannot create an orphan order. That guarantee — “referential integrity” is the fancy name — is work your app code would otherwise have to do carefully, forever, without slipping once.

The shape here is one-to-many: one user has many orders, each order belongs to exactly one user. You model that by putting the foreign key on the “many” side — the order remembers its user, not the reverse. When you need many-to-many — users in multiple teams, teams with multiple users — you add a third table in the middle (team_members, holding a user_id and a team_id) whose only job is to record the pairings. It’s called a join table, and once you’ve seen the pattern you’ll see it everywhere.

---

Constraints: Rules the Database Enforces So You Don’t Have To

You already met a couple of constraints hiding in that SQL. Let’s name them, because they’re the difference between a schema that protects your data and one that just holds it.

• NOT NULL — this column can never be empty. An order with no amount is nonsense, so forbid it at the schema level.
• UNIQUE — no two rows can share this value. email UNIQUE means the database itself blocks a second signup on the same address. You don’t have to remember to check.
• DEFAULT — a value filled in automatically when you don’t provide one. created_at TIMESTAMPTZ DEFAULT now() timestamps every row for free.
• CHECK — an arbitrary rule. CHECK (amount >= 0) makes a negative order amount physically impossible to store.

The mindset shift, and it took me years to trust it: push rules into the schema, not just your app code. Your application code is where bugs live — someone eventually writes a path that forgets to validate the email, or a background job that skips your checks, or you fat-finger something in a psql console at midnight. The schema is the last line of defense that nothing gets past. A constraint is a rule that’s true no matter which door the data comes through.

---

Normalization, Just Enough to Not Hurt Yourself

“Normalization” sounds like a database-theory exam. There are formal levels — first normal form, second, third, and onward, from the relational-model research of the 1970s — but you don’t need to memorize them. For a founder it boils down to one plain rule:

Don’t store the same fact in two places.

That’s genuinely most of it. My links_json blob broke this rule spectacularly — a user’s links were trapped inside the user row instead of living in their own links table, so I couldn’t query, count, or index them. The normalized version is obvious in hindsight: a links table, each link its own row, each carrying a user_id foreign key back to its owner.

The reason isn’t neatness for its own sake — it’s that duplicated data drifts. Store a customer’s plan name in both the users table and every orders row, and the day they upgrade you have to update it in a dozen places, miss one, and now your data disagrees with itself. Give each fact exactly one home and updating it is a single write.

The counter-warning, because I’m the guy who over-engineers everything: don’t normalize into oblivion either. Splitting data across fifteen tables to satisfy a purity ideal turns every query into a maze of joins. There’s a reason jsonb exists and the industry stopped treating a little denormalization as a sin — sometimes one settings blob in one column is the right call. Start normalized, then denormalize on purpose only when a real query is too slow. (I went down every one of these rabbit holes in the advanced database guide.)

---

The Other “Schema”: Postgres Namespaces

Here’s where the word trips people up, so I’ll clear it now. In PostgreSQL specifically, “schema” has a second, narrower meaning that has nothing to do with your table blueprint.

In Postgres, a schema is a namespace — a named container that groups tables, views, and functions inside a single database. Every Postgres database ships with one called public, and unless you say otherwise, that’s where all your tables get created. There’s nothing magical about public; it’s just the default. Postgres figures out which schema you mean using a setting called the search_path, which is simply the ordered list of namespaces it looks through when you write a bare table name.

As a solo founder you’ll live entirely in public and never think about this. Where it gets useful is multi-tenancy: some apps give each customer their own schema (tenant_42.users, tenant_43.users) to wall their data off inside one database. That’s a real, advanced pattern — but not what people usually mean by “design your schema.” Just know the word does double duty: the blueprint meaning (this whole post) and the Postgres namespace meaning (this one section). Same five letters, two jobs.

---

Design It Early, Because Changing It Later Hurts

Here’s the founder-practical truth, and it’s the whole reason I wrote this. Your schema is the one part of your app that gets harder to change the more successful you are.

Renaming a column with zero users is a five-second edit. Renaming it with paying customers and live traffic is a careful, multi-step dance called a migration, run across separate deploys so nothing breaks mid-flight. The whole reason schema migrations are an entire discipline with dedicated tools like Prisma Migrate and Drizzle Kit is precisely that changing a live schema is dangerous. I’ve watched a lazy column-type change quietly delete production data at 2AM. Empty tables forgive anything; full ones forgive nothing.

So spend thirty minutes up front. A napkin genuinely works, but if you want to see the relationships, sketch it in dbdiagram.io or DrawSQL — you write the tables and it draws the arrows. Ask the boring questions now: What are the main things my app has? (Tables.) What do I know about each one? (Columns.) How do they connect? (Foreign keys.) What must never be true — duplicate emails, negative prices, orphan orders? (Constraints.)

That’s not over-engineering. Getting the shape roughly right is the one piece of upfront thinking that pays for itself ten times over — everything else, the framework, the host, the exact database, you can swap later. Your schema is the skeleton, and you don’t want to re-break bones after the body’s up and running. (For which database to hang it on, my answer is boring and unchanged — Postgres on Neon, the same call every guide here lands on.)

---

The Bottom Line

A database schema is just the structure of your data, written down: tables for your things, columns for their attributes, types to keep the data honest, keys to connect the tables, constraints to enforce the rules no matter who’s typing. That’s the entire concept. It sounds academic until the day your app can’t answer a simple question because the data has no shape — and then you understand it in your bones, the way I did staring at a column called links_json.

Draw it before you build on it. Keep each fact in one place. Let the database enforce your rules instead of trusting your future self to remember them. Do that, and the schema quietly does its job for years while you go fight the battle that actually matters: getting anyone to use the thing at all.

This is the Broken Engineer Guide — I over-engineer everything, fail at business, and hand you the plain-English version so you don’t spend three weeks learning what a REFERENCES clause does. Now go draw your tables, then go build.