Good database schema design rarely fails at the database engine. It fails in the first week of a project, in decisions about schema shapes, key choices and access patterns that look harmless at a thousand rows and become painful at ten million. Get those early calls right and scaling is a routine tweak. Get them wrong and you’re looking at a rebuild under pressure, with customers on the system while you do it. This is a practical guide to the schema decisions that matter most, aimed at founders and technical leads who want their data model to hold up as the product grows.
Start from access patterns, not the entity list
A schema that looks tidy on a whiteboard can still be slow in practice if it fights the queries the application actually runs. The common mistake is designing tables around the nouns in the product — users, orders, products — without asking how they’ll be read and written once there’s real traffic.
Before drawing a single table, we map out the access patterns: what gets fetched together, how often, filtered or sorted by what, and at what volume. A “recent orders for this customer” query and a “total revenue this quarter across all customers” query want very different physical layouts, even though they touch the same underlying data. Design for the queries you’ll actually run, not an abstract ideal of correctness, and you avoid a whole class of problems that only show up under load.
Primary keys: the decision you can’t easily undo
Primary key choice is one of the few schema decisions that’s genuinely expensive to reverse once you have data and foreign keys depending on it, so it’s worth getting right early.
- Sequential integers are compact and index well, but they leak business information (competitors can estimate your signup or order volume) and create write contention in distributed or sharded systems.
- Random UUIDs solve the leakage problem but bloat indexes and hurt insert performance, because new rows land in random positions instead of appending to the end of the index.
- Time-ordered identifiers (such as UUIDv7 or similar sortable formats) are usually the sweet spot: unpredictable enough not to leak information, but still roughly sequential, so indexes stay compact and inserts stay fast.
Whichever you choose, apply it consistently across the schema. Mixing key strategies between tables adds cognitive load for every engineer who joins later.
Indexing strategy: the multiplier on every query
Indexes are the single biggest lever on database performance, in both directions. The right composite index turns a full table scan into a millisecond lookup. The wrong one — or ten redundant ones nobody’s pruned — slows every write and bloats storage for no benefit.
A few rules that hold up in practice:
- Index for the queries you actually run, in the order your WHERE and ORDER BY clauses use the columns, not for every column that might be useful someday.
- Composite indexes should put the most selective, most frequently filtered column first.
- Review indexes periodically. Query patterns drift as the product evolves, and an index that mattered a year ago can be dead weight today.
Normalise for correctness, denormalise on purpose
Normalisation and denormalisation aren’t opposing philosophies — they’re tools for different jobs, and a healthy schema uses both deliberately rather than defaulting to one. Start normalised: it keeps data consistent, avoids update anomalies, and makes the schema easier to reason about while the product is still finding its shape. Then, once a specific read path is measurably hot and joins are the bottleneck, denormalise that path on purpose — duplicate a value, add a summary table, cache a computed total — and document why. The failure mode to avoid is denormalising early out of habit or performance anxiety, before you actually know which paths are hot.
Multi-tenancy changes the calculus
If you’re building a SaaS product, the schema decision that matters most isn’t a single table — it’s how you isolate one customer’s data from another’s. Shared tables with a tenant ID column, separate schemas per tenant, and separate databases per tenant all trade off differently on cost, blast radius and operational complexity, and the right answer depends on your compliance requirements and expected tenant count. We’ve written a longer guide to choosing the right SaaS isolation model if that’s the stage you’re at — it’s worth deciding deliberately rather than defaulting to whatever’s fastest to ship, because retrofitting isolation later is one of the more painful migrations you can run.
Two bugs nobody notices until an audit
Two data-type decisions cause a disproportionate number of production incidents, and both are cheap to get right from day one and expensive to fix later:
- Money. Store monetary values in integer minor units (pence, cents), never as floating-point numbers. Floating-point rounding errors are exactly the kind of bug that stays invisible until a reconciliation report doesn’t add up.
- Time. Store timestamps in UTC and convert for display only. Storing local time seems convenient until daylight saving changes or a user travels, and untangling it retroactively across a live dataset is miserable.
Plan for migrations before you need one
Growth means schema migrations, and migrations on a live table with millions of rows are their own discipline, not an afterthought. The pattern that keeps deployments boring: additive changes first (new nullable column, backfill, then enforce constraints), backfills run in small batches rather than one long transaction, and nothing that takes an exclusive lock during peak traffic.
This muscle matters most when you’re inheriting a schema rather than starting one — during due diligence on an acquisition, or when a growing team takes over a codebase built by someone else. Our technical due diligence checklist covers what to check in an unfamiliar schema before you commit to it, and if the underlying platform itself has aged out, our guide to legacy application modernisation covers migrating data models without a risky big-bang rewrite.
Measure before you optimise
Slow-query logs and EXPLAIN plans tell you where time actually goes, and they’re far more reliable than intuition. We optimise the queries that are provably slow in production, not the ones that feel like they should be slow. In practice, most “we need a bigger database” moments turn out to be “we need one more index, in the right place” moments — a five-minute fix that gets mistaken for a scaling crisis because nobody looked at the query plan first.
This is also where schema debt tends to surface: a query that was fine at launch degrades as row counts climb, and by the time it’s noticed it’s often tangled up with several other issues. A periodic code and schema audit before a scaling push catches these early, while they’re still a index change rather than a migration under pressure.
Why this matters more than it looks like it should
None of this is exotic. Sensible primary keys, indexes that match real queries, deliberate normalisation, UTC timestamps and integer money are all well-understood practices. The reason schemas still go wrong is that these decisions get made fast, early, under deadline pressure, before anyone has traffic data to justify them — and then nobody revisits them until performance forces the issue. Building products that need to survive real growth, not just launch day, is a big part of what separates a schema that ages well from one that turns into a costly rebuild eighteen months in.
If you’re scoping a new product and want the data model reviewed before you commit engineering time to it, or you’ve inherited a schema that’s starting to creak under real usage, book a free consultation and we’ll talk through what your access patterns actually need.
Related: Observability for Startups: What to Monitor Before You Scale — pairs well with schema design, since knowing what to monitor is how you catch database growing pains before they cause an outage.
Related: Monolith vs Microservices: What Startups Should Build First — a well-structured monolith and a schema built to survive growth go hand in hand; this covers the concrete signals that tell you it’s time to split.
