Bad database design does not announce itself. The application works, the demo goes well, the client signs off. The bill arrives eighteen months later when there are four hundred thousand rows and every change requires a migration nobody wants to run.
These are the mistakes we see most often in Pakistani business applications, roughly in order of how expensive they turn out to be.
1. Storing money as a float
The most damaging entry on the list, and still common.
Floating point numbers cannot represent decimal fractions exactly. Add enough of them and your totals drift by a rupee, then by more. In an accounting or ecommerce system this destroys trust in the software permanently, and reconciliation becomes guesswork.
Instead: use a decimal or numeric type with explicit precision, or store the amount as an integer number of the smallest unit. Never float, never double, for money.
2. No timezone on timestamps
Storing local time without timezone information works perfectly until you have a user or a server somewhere else, or until a report crosses a date boundary and nobody can agree what “yesterday” means.
Instead: store everything in UTC with a timezone aware type, convert at display time.
3. Enum columns for things that change
Order status as a hard coded enum feels tidy. Then the business adds “awaiting supplier” and “partially dispatched”, and each one is a schema migration on a large table, plus a deployment.
Instead: a lookup table with a foreign key. Adding a status becomes a row insert that an administrator can do.
4. Deleting rows that other rows depend on
A customer is deleted. Their orders remain, pointing at nothing. Reports break, invoices show blanks, and nobody can explain last quarter’s figures.
Instead: soft delete anything referenced elsewhere. A deleted_at column, filtered out of normal queries. Financial and audit records should rarely be hard deleted at all.
5. Storing calculated values without a way to recalculate
An order total is stored on the order row. Then a line item is corrected and the total is not updated. Now you have two numbers that disagree and no way to tell which is right.
The nuance: storing totals is often correct, because historical invoices must not change when prices change. The mistake is not having a defined rule for when the stored value is authoritative and when it is recomputed. Write that rule down.
6. One table doing two jobs
A users table with thirty nullable columns, because twenty of them only apply to vendors and ten only to customers. Every query filters on a type column and half the fields are always empty.
Instead: a shared table for what is genuinely shared, separate tables for what is not.
7. No unique constraints where uniqueness matters
The application checks whether an email exists before inserting. Two requests arrive at the same moment, both check, both find nothing, both insert. Now there are duplicate accounts and your login logic picks one arbitrarily.
Instead: put the uniqueness in the database. Application checks are a convenience for the user, not a guarantee.
8. Comma separated values in a column
Storing “3,7,12” in a tags column. It works until you need every product with tag 7, at which point you are doing string matching across the whole table and cannot index it.
Instead: a join table. It is one extra table and every query gets simpler and faster.
9. Free text where a reference belongs
City typed by hand. Now you have Rawalpindi, rawalpindi, RWP, Rwp. and Rawlpindi in the same column, and no report can group them.
Instead: reference data in its own table. This is the single most common cause of unusable business reporting.
10. No indexes on foreign keys
Every join on that column scans the table. Fine with a thousand rows, painful with half a million. See our note on how to scale a web application, where missing indexes are the most common bottleneck by a wide margin.
11. No audit trail on anything that matters
A price changed. Nobody knows who, when, or what it was before. In inventory, finance and healthcare systems this is not an inconvenience, it is a compliance problem.
Instead: an audit table recording who changed what, when, and the previous value, for the handful of tables where it matters.
How to fix these once there is real data
Carefully, and in this order:
- Add the new structure alongside the old. New column, new table. Nothing breaks.
- Write to both for a period, so old and new stay in step.
- Backfill historical data in batches, not one enormous statement that locks the table.
- Move reads to the new structure and watch for a week.
- Remove the old column only after nothing references it.
Never do this in a single deployment on a live table with significant data. The approach is the same one used in migrating a legacy system without downtime.
Frequently asked questions
What is the most expensive database mistake?
Storing money as a floating point number. It corrupts financial data quietly and the damage is usually discovered during an audit.
Should I always normalise?
Normalise by default, denormalise deliberately where you have measured a problem. Denormalising first produces inconsistent data that nobody can reconcile later.
Can I fix these after launch?
Yes, with the expand and contract approach above. It takes longer than getting it right initially, which is the whole argument for spending an extra day on the schema at the start.
SQL or NoSQL for a business application?
Relational, in almost every case. Business data is relational, and transactions and constraints are exactly what you want. Our comparison of PostgreSQL, MySQL and MongoDB covers where each fits.
Ezitech has built and inherited business databases across 10 or more industries in fifteen years. Tell us what you are building, or what you have inherited.