Database Design: Foundations That Last

Database design compounds like technical debt.

A poor schema decision in month one costs months to fix at scale.

Principles that last:

1. **Normalize for clarity, denormalize for performance** Your schema tells a story. Each table should represent one thing. Related-order items go in one table, not scattered across five.

But denormalize when performance demands it. Store the customer name on the order for fast queries. Just do it intentionally, not accidentally.

2. **Immutable events over mutable state** Don't just store "account balance = $1000." Also store "received $500, spent $200, received $300."

Immutable events let you recompute state, debug issues, and audit everything. Mutable state is brittle.

3. **Explicit nulls, not magic values** Don't use 0 or empty string to mean "not set." Nullable columns are clearer.

But make nullability intentional. Can this field be NULL? If yes, why?

4. **Foreign keys and constraints** Let the database enforce data integrity, not your application.

Lazy here and you get inconsistent data that breaks in production under weird circumstances.

5. **Soft deletes by date** Don't delete data. Mark it as deleted. You'll need to examine it later.

But actually, usually you don't. Purge data after retention period. Data you hide forever is still data you maintain.

Scaling considerations:

• Can you shard this table later if needed?
• Can you partition by time (useful for old data)?
• Are your indexes actually used?

Think about these early, not when a table has 500M rows.

**The meta principle:** A great schema is boring. Tables map clearly to concepts. Queries are easy to write. New engineers understand it immediately.

If your database feels complex, talk to customers more. You're probably storing the wrong things.

Simplify. Normalize. Constrain. Audit.

Good schemas last years. Bad ones last quarters before rebuilding.