Design your database before you write a line of app code

Ask most people building an app what comes first, and they'll describe a screen. The login page, the dashboard, the thing a friend can look at and say "oh, that's cool." Ask what comes second and it's usually another screen. By the time anyone sits down to think seriously about database schema design for web apps, there are already a dozen components built assuming the data looks a certain way — and it usually doesn't, not exactly.

I've watched this pattern play out enough times now that I can predict it almost line for line. Someone builds the "customers" table with a single address field, because the first customer they pictured had one address. Three months in, a customer wants to bill one location and ship to another, and now the fix isn't a form field — it's a migration touching every query that joined against that column, plus whatever reports were quietly relying on the old shape.

The screen is the cheapest thing you'll ever build. That's worth sitting with, because it's the opposite of how it feels in the moment. Redoing a UI is an afternoon. Redoing a schema after real records have accumulated on top of the wrong relationships is a project with its own risk of losing someone's data along the way. The order you build in should follow the order things get expensive to undo, and the data model is at the top of that list, not the bottom.

Here's the part that trips people up: a schema isn't just "which columns exist." It's a set of decisions about relationships that every other layer of the app will end up depending on, whether or not anyone wrote that dependency down. Does a project belong to one customer or can it be shared across a team? Can a user exist without an account, the way a guest checkout needs to? Is an address a fact about a customer, or its own thing with a start and end date, because customers move? Answer these wrong at the start and you don't get a bug — you get a foundation that quietly assumes something false, and every feature built on top of it inherits that false assumption.

The practical fix isn't complicated, it's just unglamorous, which is exactly why it gets skipped. Before any component gets built, sketch the entities: what are the real "things" in this business, and how do they actually relate to each other in the world, not just in the first version of the app? A customer can have many addresses. An order can have one customer but many line items. A user might belong to more than one account someday, even if today it's always exactly one. Model for what's true, not for what's convenient to code on day one — the convenient version is the one that costs you a migration later.

Normalize the parts that change independently, and don't be precious about the rest. If an address changes without touching the customer's name, they're different things and belong in different tables, connected by a relationship instead of squashed into one row. This isn't academic database theory for its own sake — it's the difference between updating one row when a customer moves and hunting down every place that address string got duplicated across the system.

Constraints matter more than people expect from a tool that mostly stays invisible until the day it saves you. A foreign key that won't let an order reference a customer that doesn't exist, a "not null" on a field the business genuinely requires, a unique constraint on an email address — these catch the bad row before it lands, instead of you discovering it three weeks later as a support ticket nobody can explain. AI build tools are fast at generating a schema that looks reasonable and technically works. They're much less consistent about adding the constraints that keep it correct once real, messy human data starts flowing through it.

None of this means over-engineering for scale you don't have yet. You don't need to partition tables for a million rows when you have four hundred. What you do need, from the very first migration, is a shape that reflects reality — because the layers that get built on top of the schema (the APIs, the screens, eventually the reporting) all assume that shape is right. Get the entities and relationships correct early, and staying flexible later is cheap. Get them wrong, and "flexible" is exactly what the schema stops being.

This is the same logic behind why the data model sits near the base of the 13-layer build standard I use to scope and review builds — it's one of the two decisions (alongside the auth model) that everything else gets built on top of, so it's the one worth slowing down for before the first component exists.

If you're about to start a build, or you're a few weeks into one and something about the data feels like it's fighting you, that's usually the schema talking. Better to redraw it now, on a whiteboard, than after the third table has real customer rows in it.

See the full 13-layer build standard →

— Kyle Tysvaer, Founder, Insightful Eye Marketing