10 Database Design Mistakes That Cause Problems as an Application Grows

The mistakes that hurt most as an application grows are storing money as floating-point numbers, leaving integrity to application code instead of constraints, using keys that change, storing times without time zones, indexing poorly, hiding structured data in JSON blobs, letting duplicated data drift, deleting records with no history, retrofitting multi-tenancy, and changing the schema in ways that break running code. Each is cheap to avoid at the start and expensive to fix once there is real data and several systems depend on it.

This list is for technical founders, product owners reviewing a build, and developers early in a project. Examples use PostgreSQL, but the principles apply to most relational databases. For speeding up an existing database, see database performance optimisation.

Floating-point types store approximations. PostgreSQL's documentation says that some values cannot be stored exactly, that comparisons for equality may not work as expected, and that for exact amounts such as money you should use the numeric type. Small rounding errors add up across invoices, tax and refunds, and reconciliation stops matching.

Instead: use a fixed-precision decimal type, or store amounts as whole minor units (paise, cents) in an integer column, and always store the currency alongside the amount.

Without foreign keys, unique constraints, NOT NULL and check constraints, the database accepts anything. Orders end up pointing to deleted customers and the same email appears twice. It works while one careful application writes to the database; it fails once imports, scripts, background jobs or a second service write too.

Instead: declare relationships and rules in the schema. Constraints are documentation that cannot go out of date.

Using an email address, phone number or product code as the primary key seems natural until someone changes their email or a supplier renames a SKU, and every related record must be updated.

Instead: use a surrogate key (an auto-generated identity or a UUID) as the primary key, and put a unique constraint on the natural identifier.

Timestamps saved without time zone information are ambiguous once you have users, servers or offices in more than one zone, or when daylight saving changes. The PostgreSQL community's "Don't Do This" guidance advises against timestamp without time zone for recording moments in time.

Instead: store moments as timestamps with time zone (in practice, UTC), and convert to local time for display. Store the user's time zone separately when you need it for scheduling.

Missing indexes on columns used in joins and filters make queries slow as tables grow; foreign key columns are a common gap. Indexing every column slows writes and wastes storage.

Instead: index for the queries you actually run, check them with the query planner, and review regularly. See how to find and fix slow queries.

JSON columns are useful for genuinely variable data, such as an integration's raw payload or optional settings. Problems start when core fields like status, amount or customer reference live inside JSON: constraints cannot protect them, reporting gets harder and different code paths write different shapes.

Instead: give important, queried fields their own typed columns, and keep JSON for the truly flexible remainder.

Copying a customer's name and address onto every order, or caching totals in several tables, can be sensible for performance or history. Without a clear rule about which copy is the source of truth, copies drift and reports disagree.

Instead: normalise by default, and when you duplicate deliberately, document why and whether the copy is a snapshot (an order's address at the time of purchase) or must stay in sync.

Hard deletes and in-place updates lose the information you later need for support, audits and disputes: who changed this price, when was this account closed? Adding a "deleted" flag everywhere creates other problems, such as unique constraints that block re-creating a record.

Instead: decide per table: keep an audit log of changes for important records, use soft deletes where you need to restore, and plan data retention and deletion to meet privacy obligations. See audit logging.

A product built for one customer and later sold to many needs every relevant table to know which tenant owns each row, and every query to respect it. Adding that later is one of the most expensive changes, and a missed filter leaks one customer's data to another.

Instead: if there is a realistic chance of serving several organisations, include a tenant identifier from the start and enforce isolation centrally, for example with row-level security. See multi-tenant SaaS architecture.

Renaming or dropping a column in the same release that changes the code causes errors during deployment, when old and new versions run side by side, and makes rollback hard. Large migrations can also lock busy tables.

Instead: use migrations in version control and the expand-and-contract approach: add the new structure, deploy code that writes to both, backfill, switch reads, then remove the old structure in a later release. Test migrations on a copy of production-sized data.

A sound data model is one of the cheapest investments in an application's future. Use exact types for money, let the database enforce integrity, keep keys stable and times unambiguous, index deliberately, plan for history and tenants, and change the schema in backward-compatible steps. For how the database fits into the wider system, see web application architecture patterns.

PostgreSQL documentation, numeric types (arbitrary precision numbers and floating-point types). PostgreSQL wiki, Don't Do This. Accessed 28 October 2026.