20 Tips for Designing and Evolving Database Systems

I asked Claude to lay out the core principles for designing and evolving database systems. What came back was interesting enough to share. Some of it confirmed things I already believed. Some of it pushed back on them. Either way, it's a useful lens for thinking about one of the parts of software engineering that's hardest to undo once you get it wrong.

Schema Design

1. Model the domain, not the UI.Your schema reflects business reality, not whatever form or screen triggered the requirement. If a UI conflates two concepts, your schema shouldn't.

2. Name things precisely.user_id on a table that stores organizations is a lie. Names should be unambiguous across the whole system — created_by_user_id, owner_org_id. The schema is documentation.

3. Normalize to understand, denormalize to perform.Start in 3NF. Deviate only when you have a measured reason, not a speculative one. Every denormalization is a debt you'll pay on every write.

4. Every table needs a stable, surrogate primary key.Natural keys (emails, phone numbers, codes) change. Surrogate keys (UUIDs, sequences) don't. Use natural keys as unique constraints, not PKs.

5. Timestamps are non-negotiable.Every table gets created_at and updated_at at minimum. You will always need them. The cost is nothing; the regret of not having them is enormous.

6. Nullable columns are design questions, not defaults.NULL means "unknown" or "not applicable." If you're using it for "not yet set," that's either a missing table or a missing state column. Be intentional.

Integrity

7. Let the database enforce what it knows.Foreign keys, unique constraints, not-null constraints, check constraints — push rules as close to the data as possible. Application-layer enforcement alone is not enough.

8. Avoid soft deletes unless you have a real reason.is_deleted flags scatter complexity everywhere — queries, indexes, joins. If you need an audit trail, use an audit/event table. If you need recoverability, use backups.

9. Use enums and lookup tables deliberately.Database-level enums are hard to migrate. Lookup/reference tables are more flexible. Either way, make the valid value set explicit — never rely on implicit strings.

10. One source of truth per fact.If the same fact can be derived or stored in two places, you will eventually have two different answers. Computed values belong in queries or materialized views, not as redundant columns.

Evolution

11. Migrations are code — version them, review them, test them.A migration in production is as consequential as a deploy. It should go through the same discipline: reviewed, tested on a copy of prod data, rollback-considered.

12. Expand before you contract.When changing a schema: add the new thing first, migrate data, update the app, then remove the old thing. Never do a destructive change in a single step. This makes zero-downtime deployments possible.

13. Never rename or drop without a deprecation window.A column rename is a breaking change if anything reads it directly. Mark it deprecated, add the new column, migrate, then drop — across separate deploys.

14. Design for the query, not just the insert.Before you finalize a schema, write out the top 5–10 queries that will run against it. If they require tortured joins or full scans, the model is probably wrong.

15. Indexes are a contract with your query patterns.An index you're not using is overhead on every write. Know what queries drive each index. When query patterns change, audit your indexes.

Operations & Scale

16. Understand your cardinality before you index.An index on a boolean column is usually useless. Indexes shine on high-cardinality columns used in WHERE clauses and joins.

17. Long-running transactions are a hazard.They hold locks, block migrations, and cause replication lag. Design operations to be short and bounded. Batch large mutations.

18. Separate read and write concerns early.Not necessarily CQRS, but think about which tables are write-heavy vs. read-heavy. That shapes indexing strategy, replication topology, and caching decisions.

19. Plan for the data you'll never delete.Audit logs, financial records, event streams — they grow forever. Design their storage and access patterns separately from operational tables from day one.

20. The schema is a shared contract.Everyone who touches the database — app code, scripts, BI tools, migrations — is a consumer of that contract. Changes without coordination are breaking changes.

The meta-principle behind all of these: a database schema is harder to change than application code, so it deserves more upfront thought and more conservative evolution. Speed in schema design is usually false economy.