Database design decisions made early in a project are among the most difficult to reverse. A table structure that seemed reasonable for the initial requirements becomes a constraint as the application grows. A data model that was not normalized becomes a source of inconsistency. An indexing strategy that was not thought through becomes a performance problem at production scale. The mistakes are invisible during development and become visible in the worst possible way — in production, under load, when the business is dependent on the system.
Normalization: Getting It Right the First Time
The most consistently consequential database design mistake is under-normalization — storing related data in ways that allow inconsistency. When customer address information is stored in multiple tables without a single source of truth, updating a customer’s address requires updates in multiple places, and a partial update produces inconsistency. When product pricing is embedded in transaction records rather than referenced from a pricing table, changing a price requires updating historical transactions rather than a single record. These design choices feel harmless when the data volume is small and the development team controls all update paths. They become serious problems when the data volume is large, the update paths are multiple, and the inconsistencies have accumulated over years.
The Indexing Decisions That Determine Performance
A database that performs well in development and poorly in production is almost always a database where the indexing strategy was designed for development data volumes rather than production data volumes. The queries that drive user-facing features — the search, the list view, the dashboard — need to be analyzed for their execution plans against realistic data volumes, and the indexes required to support those execution plans need to be designed before the system goes to production. Adding indexes to a production database that is already under load is possible but disruptive. Designing them correctly from the start is significantly cheaper.
Designing for Reporting From the Beginning
Business applications generate transactional data. The business needs to report on that data. These two requirements have different and sometimes conflicting database design implications. Transactional database design optimizes for write performance and data integrity — normalized structures, foreign key constraints, transaction isolation. Reporting optimizes for read performance across large volumes — denormalized structures, pre-aggregated summaries, minimal joins. Designing a single database to serve both requirements well is difficult. The architecture decision about whether to separate transactional and reporting databases should be made at the beginning of the project, not as an emergency response to a reporting performance problem six months after go-live.