Database Schema Design for the Long Run

We have inherited enough first-year codebases to know where the pain usually starts. It is rarely the framework or the hosting. It is a table designed in an afternoon during week one that now has a year of production data inside it.

Database schema design is the work of deciding which tables exist, what each column holds, how records relate to each other and which rules the database enforces on its own. A schema is cheap to change while it is empty. Once customers, invoices and audit history depend on it, each change becomes a small project with its own risk.

This post explains why early schema choices compound, the seven decisions we make carefully in week one, what can safely wait, the mistakes that surface in year two, and how to change a live schema without breaking the product.

Why Week-One Schema Choices Get Expensive by Year Two

Application code can be rewritten in an afternoon and redeployed. Data cannot. Every row written under the old schema stays in that shape until someone migrates it, and every report, integration and API response built on it has to change at the same time.

Three forces make early schema mistakes compound.

• Volume grows, so a migration that took seconds in month one can lock a busy table or take hours in year two.
• Dependencies grow, as exports, dashboards, partner integrations and analytics jobs start reading the same tables.
• Knowledge fades, because the engineer who knew why a column exists has often moved on by the time it needs changing.

None of this means the first schema has to be perfect. It means a handful of decisions deserve an hour of thought each, because they are the ones we see teams pay for most often later.

Database Schema Design Decisions to Get Right in Week One

These are the seven areas where we slow down during the first week of a build. Each one is cheap to get right on an empty database and expensive to correct once real data depends on it.

1. Give Every Table a Stable Primary Key

Every table needs an identifier that never changes and carries no business meaning. Email addresses, phone numbers and order numbers all change eventually, and anything that references them breaks when they do.

A generated identifier, either an identity column or a UUID, keeps relationships intact when the human-facing values move. In PostgreSQL, the community guidance is to use identity columns rather than serial for new applications.

2. Enforce Relationships With Foreign Keys

If an invoice belongs to a customer, the database should refuse an invoice that points to a customer who does not exist. As the PostgreSQL documentation puts it, a foreign key constraint maintains the referential integrity between two related tables.

Teams sometimes skip foreign keys to move faster, planning to enforce the rule in application code. That works until a script, an import or a second service writes to the table directly, and orphaned records start appearing in reports.

3. Pick Correct Types for Money and Time

Money and timestamps cause more year-two bugs than any other columns we review. Floating-point numbers round money in ways accountants notice. Timestamps stored without a time zone become ambiguous the day the product gains a customer in another region.

In PostgreSQL that means an exact numeric type for currency, plus a currency code column if more than one currency is possible, and timestamptz for moments in time. The same community guidance warns against plain timestamp for storing moments and against the money type for most monetary values.

4. Mark What Is Required and Constrain What Is Valid

Decide which columns must always have a value and declare them NOT NULL. Add CHECK constraints for simple rules, such as a quantity that cannot be negative or a status that must be one of a known set.

Every constraint added on day one is a class of bad data that never enters the system. Adding the same constraint in year two means first finding and fixing every row that already breaks it.

5. Normalize the Core and Keep Volatile Settings Separate

Core business entities, such as customers, orders and line items, belong in normalized tables where each fact is stored once. Settings, preferences and feature flags are different. They change constantly and rarely apply to every record.

A common early pattern is adding a new column to the users table for every new setting. It is simple at first, and by year two the table is wide, sparse and risky to alter. A separate settings table, or a single jsonb column for loosely shaped preferences, keeps the core table stable.

6. Plan for Tenants, Soft Deletes and Audit History

If the product will serve several customer organizations, every relevant table needs a tenant identifier from the start. Retrofitting one later touches nearly every query in the codebase.

The same applies to deciding how records are deleted and whether changes need a history. These are product and compliance questions as much as technical ones, so we ask them in week one rather than after the first customer requests an audit trail.

7. Write Down Why Each Table Exists

A short note per table, covering what it represents, who writes to it and which rules it enforces, is the cheapest insurance in database schema design. It lets the next engineer change the schema with confidence instead of guessing.

When we plan product engineering work with a founder, this note sits alongside the scoped plan. It is part of how we approach product engineering for early-stage teams, where the aim is a first version the next engineer can extend safely.

What Can Safely Wait Until You Have Real Data

Not every schema decision deserves week-one attention. We deliberately defer the choices that depend on usage patterns nobody can predict yet.

• Most indexes beyond primary keys and foreign keys, which should follow real slow queries.
• Denormalized copies and summary tables, which should follow measured read load.
• Partitioning and sharding, which very few first products need.
• A second database, which should follow a clear need. Our guide to choosing between SQL and NoSQL covers when that need is real.

Deferring these is safe because each can be added later without rewriting existing rows. That is the test we use. If a decision can be added later without touching old data, it can usually wait.

Schema Design Mistakes That Show Up in Year Two

The mistakes we find in year-two schemas tend to repeat, and each one traces back to a week-one shortcut.

• A users table with dozens of setting columns that most users never touch.
• Money stored as floating point, with reports that disagree by a few cents.
• Timestamps without time zones that shift by hours after a daylight saving change.
• Status fields as free text, so the same state appears under several spellings.
• Missing foreign keys, leaving orphaned rows that break joins and exports.
• Natural keys such as email used as identifiers, breaking links when a customer changes address.

Each of these is fixable, but the fix costs a migration, a backfill and a round of application changes, often while the team is also trying to ship features. We cover how to schedule that work in our post on managing technical debt without stopping feature work.

How to Change a Schema Safely Once It Is Live

Changing a live schema is routine when it is done in small steps. We follow an expand and contract pattern. Add the new structure alongside the old, write to both, backfill old rows, switch reads, then remove the old structure in a later release.

Some changes are cheaper than founders expect. The PostgreSQL documentation notes that adding a column with a non-volatile default stores the value in the table's metadata, so no rewrite of the table is required. Others, such as changing a column's type, can rewrite the whole table and need planning.

Our guide to database migrations without downtime walks through the sequence step by step.

Spend One Careful Day on the Schema Before Launch

Good database schema design in week one is not about predicting the future. It is about getting the few decisions right that are hard to reverse, namely identifiers, relationships, types, constraints and where volatile data lives, and deferring the rest until real usage tells you what to optimize. We find that one careful day here saves weeks of migrations later.

If you want an experienced review of your data model before the first customers arrive, Better Software does this as part of scoping a build.

Frequently Asked Questions

When Is the Easiest Time to Change a Database Structure?

The easiest time is before real data exists, ideally during the first weeks of a build. Once records depend on a structure, every change needs a migration and code updates. We treat the period before launch as the window to fix anything we already doubt.

How Do You Design a Good Database Schema?

We start with the core business entities and how they relate, then add stable primary keys, foreign keys and correct types. We declare required fields and simple rules as constraints. Settings that change often go into a separate table, and we write down why each table exists.

Why Is Database Design Important?

Database design decides how reliably a product stores the facts it depends on. A sound design keeps data consistent and makes new features easier to add. In our experience, a weak design shows up later as reporting errors, slow queries and migrations that delay product work.

How Many Schemas Should a Database Have?

Most first products need only one schema. We add more only for a clear reason, such as separating reporting tables, isolating a module with its own owners, or some approaches to serving several customer organizations. Extra schemas add permission and migration overhead, so we avoid them by default.

What Are Database Schemas Used For?

A database schema defines what data a system stores and the rules that keep it valid. It names the tables, columns, types and relationships. We also use it as documentation, because a well-designed schema tells a new engineer how the business works faster than most written specs.