Database Design Principles

Why Most Database Projects Fail Before They Ship

A 2023 survey by CockroachDB across 2,100 engineers found that 68% of schema problems surface only after a system has been in production for six months or more. By then, a bad table design is no longer a rebuild—it's a multi-week migration touched by every team in the company. The good news is that most of these failures trace back to a handful of repeatable mistakes: denormalizing too early, ignoring query patterns, and treating every column as a first-class citizen. This article walks through the design principles that separate schemas people throw away from schemas that survive seven years of production traffic with barely a structural change.

Start From the Queries, Not From the Entities

The single most common error new designers make is listing business objects (Customer, Order, Product) and then forcing relationships between them without asking how the data will actually be read. PostgreSQL's official documentation makes the point bluntly: the physical layout you choose is a function of the workloads that hit it. If 90% of your traffic reads a customer profile alongside the last five orders, a normalized three-table join on every read is the wrong starting point—even though it looks "correct."

A practical way to anchor this is the read/write ratio. OLTP systems that are write-heavy benefit from narrow rows and enforced normalization. Read-heavy reporting systems want denormalized summaries and materialized views. The trap is assuming one database can be both. Before you create a single table, write down the three heaviest queries you expect to run, note their filters and sort orders, then design so those queries touch the fewest rows possible.

Normalize for Integrity, Denormalize for Speed—Deliberately

Third normal form is a starting gate, not a finish line. The mistake is treating it as a religion. A 2022 analysis of open-source Rails applications found that roughly one in four schemas contained a denormalized counter or cached summary column, and the ones that performed best had documented the reason for every denormalization. That documentation is the differentiator: an undocumented denormalized column is a landmine for the next engineer; a documented one is a deliberate trade-off.

When you do decide to denormalize, you must answer three questions. First, who owns the source of truth? Second, what job keeps the copy in sync—a trigger, an application write path, or a scheduled task? Third, what happens when the copy drifts during an outage? If you cannot answer all three, keep the data normalized and pay for the join. This decision logic is central to the material covered in our database design basics guide, which walks through the normalization levels with concrete table examples. If you are still deciding between relational and document stores, the database design basics guide also compares schema styles for different workloads.

Choose Data Types That Match the Data, Not the Ambition

Type choice is where small decisions compound into big costs. A classic production incident involved a team storing monetary amounts as FLOAT because "the numbers are small anyway." After two years, a rounding difference of 0.03 per transaction, compounded across 40,000 transactions a day, produced a $438,000 discrepancy in reconciliation reports. The fix required rewriting the ledger column to NUMERIC(12,2)—a migration that took nearly three weeks because every dependent view and report referenced the old type.

The principle is simple: use the narrowest type that cannot lose information for the range you actually need. Use INT/BIGINT for whole numbers, NUMERIC with explicit precision and scale for money, DATE for calendar dates, TIMESTAMPTZ for absolute instants, and UUIDs or BIGINT for primary keys on high-volume tables. Timestamps deserve special attention—a naive TIMESTAMP without time zone +offset is a leading cause of multi-timezone reporting bugs. Store everything in UTC at the database layer and convert only at display time.

Primary Keys: Prefer Surrogate Keys, But Not Blindly

Natural keys—like an email address or a government ID—feel elegant until the real world intervenes. A person changes their email, a product line changes its SKU format, a customer ID gets reused after a data cleanup. Every one of those changes damages every foreign key that references the natural key. Surrogate keys (an auto-increment BIGINT or a UUID) are stable, compact, and free of business meaning, which is precisely why they make better join columns.

That said, the choice is not universal. PostgreSQL's `gen_random_uuid()` avoids lock contention on hot tables in a way that a monotonic BIGSERIAL cannot, because a UUID has no order and therefore no central sequence to contend on. Modern PostgreSQL and MySQL 8+ versions use IDENTITY columns rather than legacy SERIAL/AUTO_INCREMENT for better standards compliance and sequences. If you choose UUIDs, weigh the happy side effect of offline-friendly generation against the cost of larger indexes—each UUID eats 16 bytes per index entry compared to 8 for BIGINT.

Index With the Full Query in Mind

Indexing is the difference between a schema that feels fast and one that collapses under load. The mental model that helps most is the leftmost-prefix rule: a composite index on (a, b, c) serves queries filtering on a, on (a, b), and on (a, b, c)—but not, by itself, on b alone. Getting this right is the subject covered in depth in our database indexing basics article, including how to read execution plans to verify that an index is actually being used.

Consider a concrete case: an orders table filtering on (customer_id, created_at) for "recent orders by customer." A single composite index on those two columns in that order beats two single-column indexes on the same table. The keyed index lets the engine seek to the customer and then read rows in descending date order without a sort. One useful heuristic is to look at your slow-log and identify the top five queries, then design composite indexes that serve the highest-frequency filtering and sorting patterns rather than indexing every column that appears in any WHERE.

Foreign Keys: Integrity Costs Less Than You Fear

New designers often skip foreign keys "for performance," then spend weekends cleaning orphaned rows by hand. The performance argument is largely overstated in OLTP workloads. Referential integrity checks are only enforced on INSERT/UPDATE/DELETE, and a well-indexed FK lookup is a single tree probe. The real cost is not the constraint—it is the missing index on the referencing column. When you declare a foreign key in PostgreSQL or MySQL, the engine does not automatically index the referencing column for you, so a DELETE on the parent can trigger a full table scan on the child to check for violations.

Make FK constraints explicit and make sure each referencing column has an index. That combination gives you guaranteed integrity at negligible marginal cost. This is standard practice in any mature codebase and is a recurring theme in our database management guide, which covers day-to-day maintenance, monitoring, and backup practices alongside schema concerns. For the operational side of keeping those indexes healthy, see the database indexing basics guide, which explains when to rebuild indexes and how to read maintenance windows.

Plan for Changes You Cannot Predict

Schema design is not a one-time event; it is an ongoing negotiation between the data model and the queries that evolve around it. Two habits keep this sustainable. First, deploy changes as additive operations—new tables, new columns—rather than rewrites of existing ones. Second, keep a documented schema-migration log with a timestamp and an author for every change, so that six months later someone can reconstruct why a column exists and what it means. Version-controlled migration files (as used by tools like Flyway and Liquibase) turn schema evolution into a code-reviewed, testable artifact instead of a manual database session.

The most predictable thing about real systems is that the queries will change. Resist the urge to optimize for a hypothetical future; optimize for the queries you can articulate today, and keep the schema open to additive growth. A design that is boring, documented, and easy to extend will outlive a clever one that is hard to change.

Trade-Off Comparison: Design Choices at a Glance

For more, check out: and design skills.

For more, check out: .

Frequently Asked Questions

Should I use a surrogate key or a natural key for my user table?

Use a surrogate primary key (BIGINT identity or UUID) as the join column, and keep the natural identifier—email or username—as a separate column with a unique index when it must be unique. This decouples your identity from business values that can change, so a user changing their email does not ripple through every related table.

When is it actually worth denormalizing a column?

Denormalize only when you can demonstrate that a denormalized copy materially reduces the cost of your heaviest read path, and when you have a defined mechanism—trigger, event, or job—to keep it in sync. Document the source of truth and the sync mechanism in the migration comment.

What is the biggest mistake with timestamp columns?

Storing timestamps without time zone information or using a local time column instead of UTC. This creates silent, hard-to-trace bugs when data crosses time zones. Use TIMESTAMPTZ (or equivalent) and store UTC, converting only at render time.

How many indexes is too many for a single table?

There is no universal number, but each index adds write overhead and consumes storage. As a rule of thumb, keep the total index size under roughly half the table size and revisit periodically; drop indexes that duplicate the leftmost prefix of other indexes or that no slow-query report references.

Should I change my schema in production or use migrations?

Always use versioned, code-reviewed migrations rather than ad-hoc ALTER statements. Tools like Flyway and Liquibase make changes reproducible, testable, and reversible, and they carry the discipline of treating schema changes as first-class code.