Database Schema Design Principles We Never Compromise On

A slow feature is annoying. A corrupted database is a business emergency. Most of the engineering decisions that separate the two happen at the schema layer, months before anyone notices — which is exactly why we treat schema design as the one place we don’t negotiate on shortcuts, even under deadline pressure.

Foreign Keys Are Not Optional

We still encounter production systems where referential integrity is “enforced” entirely in application code — meaning it’s enforced only in the code paths someone remembered to add a check to. A foreign key constraint at the database level doesn’t care which code path wrote the record; it simply refuses to let an order reference a customer that doesn’t exist. That single guarantee eliminates an entire category of 2am debugging sessions.

Normalize First, Denormalize With Evidence

Every schema starts fully normalized. We only introduce denormalization — a cached total, a duplicated field for read performance — after a real query pattern demonstrates it’s needed, and always with an explicit mechanism keeping the duplicated data in sync. Denormalizing pre-emptively, “for performance,” before you’ve measured anything, is how systems end up with three different tables disagreeing about a customer’s current balance.

Every Multi-Step Write Is a Transaction

If an operation involves more than one write — debit one ledger, credit another; create an order, then decrement inventory — it happens inside a database transaction, full stop. Partial writes from an unhandled exception halfway through a multi-step operation are one of the most common root causes of the specific pain we get called in to fix: numbers that don’t reconcile, and nobody can explain why.

Indexes Follow Query Patterns, Not Guesses

We index based on actual query plans, not intuition about which columns “feel” important. An index on the wrong column set doesn’t just fail to help — it slows down every write to that table indefinitely, for a read speedup that never materializes. This is a five-minute check with a query planner and a genuinely common mistake in schemas built without one.

Every Column Has an Owner

Nullable columns that mean five different things depending on which code path wrote them are a slow-motion data integrity failure. We define what every column means, what can and can’t be null, and enforce it with constraints — not a comment in a wiki page nobody reads before writing new code against the table.

These aren’t abstract preferences — they’re the same rules behind the Business Technology ledgers and CRM engines we run in production today, including for our own products.