Database design mistakes that cost years

The recurring mistakes

No stable identifier on entities that people talk about, such as customers or invoices. Status stored as free text, so reports need string matching. Derived values stored without a rule for recalculation, so totals disagree with line items.

Storing dates without time zones, and money as floating point, round out the list.

Model the rules, not the screens

A screen is a view of the data, and screens change faster than rules. If the model encodes workflow states and relationships, new screens become cheap. If it encodes a particular form, every change becomes a migration.

Migration discipline

Every schema change should be a versioned migration that runs the same way in development, staging and production. Ad hoc changes made directly in production are the reason environments drift.