Databases: the modeling that sustains (or sinks) the product
The database tends to be the most permanent part of a system. Interfaces change, languages get replaced, services get rewritten — the data, and how it was modeled, stays. That's why modeling decisions deserve the kind of care usually reserved for overall architecture.
Modeling means describing business rules, not drawing screens
A good model expresses what exists in the domain and which relationships are mandatory. When modeling starts from the screen, the result is a set of tables that mirrors month one's interface and holds up poorly against the second feature.
Keys, uniqueness constraints and referential integrity aren't bureaucracy: they are business rules written in the one place no part of the application can accidentally bypass.
- Database constraints guarantee invariants even with multiple services writing.
- Normalize first; denormalize later, with measurement and an explicit reason.
- State fields should have a closed domain, not free text.
Indexes solve reads and charge on writes
An index is a trade-off: it speeds up specific queries and adds cost to every write. The choice should come from the product's real queries, observed in production, not from assumptions made before the first user.
Analyzing the execution plan of a slow query usually reveals more in ten minutes than a week of optimizing by intuition.
SQL, NoSQL and the right question
The useful question isn't which technology is better, but which guarantees the domain requires. Wherever there's money, inventory, contracts, or any invariant that can't break, transactions and strong consistency are worth more than schema flexibility.
Modern relational databases handle documents, search and time series competently. Introducing a second technology should be a response to a measured limit, not an aesthetic preference.
Migrations and evolution without stopping the product
Schemas change. What separates a calm team from a panicked one is the process: versioned migrations, applied in steps compatible with the previous code version, always reversible and tested before touching production.
In short
The data model is a long-term decision: write the domain's rules into the database, measure before optimizing, and treat migration as a normal part of the delivery cycle.
Use cases
Multi-tenant SaaS
Isolation by organization with a key in every table and access policies enforced in the database itself.
Heavy reporting
Separation between transactional and analytical load, with materialized views refreshed in a controlled window.
Work queue
Explicit states, row-level locking and idempotency to prevent duplicate processing.
Common mistakes
- Storing monetary values as floating point.
- Using free text for statuses and then relying on string comparison.
- Creating an index for every column, degrading writes with no real read benefit.
- Running a destructive migration with no rollback plan.
Best practices
- Correct types from the start, including time zone on dates.
- Integrity constraints in the database, not only in the application.
- Queries monitored with time and frequency metrics.
- Backups tested via actual restoration, on a defined schedule.
Recommended books
Designing Data-Intensive Applications — Martin Kleppmann
Explains consistency, replication and partitioning trade-offs with rigor and clarity.
SQL Performance Explained — Markus Winand
Makes the effect of indexes and execution plans on performance concrete.
Database Design for Mere Mortals — Michael J. Hernandez
A solid modeling foundation for those just starting out.
Go deeper
Frequently asked questions
- Should I choose SQL or NoSQL?
- Start with the guarantees the domain requires. If there are critical invariants and rich relationships, relational tends to be the safer choice.
- When should I create an index?
- When a frequent, slow query is proven by measurement, and the read gain outweighs the write cost.
- Should I always normalize?
- Normalize by default. Denormalize selectively, with measured data and explicit update control.
- How do I avoid data loss during a migration?
- Migrate in compatible steps, rehearse execution on a production copy, have rollback ready and verify the backup beforehand.
References
- Martin Kleppmann — consistency guarantees in distributed systems
- Markus Winand — using indexes and reading execution plans
- PostgreSQL documentation — transactions and isolation
Original content by the i9 Conecty team. Classic concepts are explained in our own words and credited to their authors.