Database Schema Design: Decisions You Cannot Easily Undo

Schema decisions propagate into every query, report, and integration built afterwards, which is what makes them expensive to reverse. This guide covers the decisions worth taking carefully, drawing on FISTA Solutions' web and mobile work.

What should the model be built around?

The access patterns, which means knowing them before designing.

Decision	What it affects
Table structure	Every query and report
Normalisation level	Write consistency and read cost
Primary key type	Ordering, distribution, index size
Foreign keys	Integrity guarantees
Indexes	Read speed and write cost
Nullability and defaults	Application complexity

How do you model for access patterns?

List the queries the application will run and the reports the business will want, before drawing tables.

A schema designed conceptually and queried differently produces joins nobody anticipated, denormalised caches to compensate, and eventually a reporting database that duplicates everything.

That does not mean designing for one query. It means knowing the shape of the access so the model supports it naturally rather than by workaround.

When is denormalisation justified?

When a measured read pattern requires it and you accept the cost of keeping the duplicate consistent.

Denormalising in anticipation is the common mistake. It produces duplication that every write must maintain, for a read benefit nobody demonstrated, and the inconsistencies appear later as data quality problems.

Normalise first, measure, then denormalise the specific paths that need it with a comment explaining why. That comment prevents someone re-normalising it later.

Does the primary key choice matter?

More than it appears. Sequential integers order naturally and expose volume to anyone who sees an identifier. Random identifiers distribute writes and fragment indexes. Sortable identifiers give ordering without predictability.

The choice propagates into every foreign key, every URL, and every integration, which makes it close to irreversible.

For anything exposed externally, unpredictability matters. For anything high-volume, index behaviour matters. Sortable unique identifiers address both and are worth considering as a default.

Why should constraints live in the database?

Because they survive application bugs, background jobs, manual corrections, and the second application nobody planned for.

Foreign keys, uniqueness constraints, and check constraints are guarantees. Application validation is a convention that holds until something writes directly, which eventually something does.

The objection is usually performance or flexibility. Both are real and smaller than the cost of data that violates assumptions the application makes everywhere.

How should indexing be approached?

As a design activity informed by the access patterns, not as a response to slow queries.

Every index speeds reads and slows writes, and a table with a dozen indexes has a write cost nobody measured. Index the queries that matter and remove the ones nothing uses.

Review them periodically. Indexes accumulate — added for a query that was later changed — and unused indexes are pure write cost.

How do you make migrations safe?

Expand and contract: add the new structure, write to both old and new, migrate readers, verify, then remove the old.

That sequence allows each step to be deployed and reversed independently, which means schema changes do not require downtime or coordinated releases.

Schema changes that require downtime get deferred, and deferred schema changes accumulate until one becomes an emergency. Making them routine is what prevents that. See CI/CD for web apps.

What are the common mistakes?

Designing before knowing the queries. Denormalising in anticipation. Constraints only in application code. Indexes added reactively and never removed. And migrations that require downtime.

How do you test it?

Test migrations against production-scale data volumes, test constraint violations, and test query performance with realistic row counts.

Volume matters enormously. A query that performs well against ten thousand rows can be unusable against ten million, and the difference appears in production rather than in development.

What does it cost to operate?

Storage and compute, which are modest until volume grows, plus the operational cost of migrations.

The cost that dominates is the engineering time spent working around a schema that does not fit, which is invisible in any budget and substantial over years.

What should you measure?

Query latency at the tail for the important access patterns, index usage, migration duration at production scale, and constraint violation attempts.

What about vector storage for AI features?

It is a different access pattern and frequently a different store, and it carries the same design questions: what is the key, what metadata supports filtering, and how is tenancy enforced.

The common mistake is treating the vector index as a cache rather than as a data store with retention, access control, and deletion obligations. It holds the same content as the source and needs the same treatment. See what is a retrieval index.

When is this the wrong approach?

Heavy upfront schema design is wrong for a product still discovering its domain. There, a simpler model that is migrated frequently beats a comprehensive one built on assumptions that turn out wrong.

What should you do first?

Write down the ten queries your application will run most often. If the schema you are drawing does not serve them naturally, redraw it now rather than later.

How FISTA Solutions helps

FISTA Solutions builds and operates production systems through web and mobile, AI enablement, and staff augmentation: schemas modelled around real access patterns, constraints enforced in the database so guarantees survive application changes, decisions documented with their reasoning, and handover that leaves your team able to maintain what was delivered. The record is 150+ projects for 50+ companies across 12+ countries.

To scope this work, message FISTA on WhatsApp, or read caching strategy guide.