Most database design mistakes in business software do not show up on launch day. They show up six months later, when a report counts the same revenue two different ways, when a simple delete takes minutes, or when a developer cannot explain what half the columns are for. By then, every fix is a migration, and every migration carries risk. This plain-English guide covers the mistakes we see most often, what each one costs you, and how to fix it without stopping the business.
This guide is for business owners who commission software and the developers who build it. You do not need to know SQL: by the end, you will be able to spot the warning signs in your own system and judge whether a proposed fix is safe.
Why database design mistakes surface so late
A database schema is a set of decisions about what your business facts are and how they relate: customers, orders, invoices, products, stock. Early on, almost any schema works. Problems grow with the data. A table with a hundred rows forgives a missing index; a table with two million rows does not. A duplicated customer name is a typo at ten customers; at ten thousand, it is two records for the same person, and nobody agrees which is right.
The second reason is that bad design hides behind working software. The application can paper over a weak schema for a long time with extra code and manual corrections. That hidden labour is the real cost. Budget for the design phase: it is the cheapest work in the project, and the most expensive to redo.
Mistake 1: Storing the same fact in two places
This classic error usually starts innocently. A customer address is copied onto every order so invoices can print it; a product price is stored on the order line and in the catalogue; a department is written into three tables. Each copy seems convenient until one changes and the others do not. Then the same question — what did this customer actually pay, where do they actually live — has two answers, and the business has to guess.
The fix has a formal name, normalisation, but the idea is plain: every fact should live in exactly one place, and everything else should point to it. A customer record holds the address; an order holds a reference to the customer. If you need the address as it was at order time — legitimate for invoices — snapshot it deliberately, in a clearly named field, with a note saying why. Accidental duplication is the problem; deliberate snapshots are a design choice.
There is a counterweight. Taken too far, normalisation splits data into so many tables that every simple report needs a dozen joins, and performance suffers. The working rule most experienced developers use is: normalise to third normal form first — no duplicated facts, no columns that depend on something other than the row’s key — and then denormalise selectively, on purpose, where a measured query is genuinely slow.
Mistake 2: Relationships that exist only in the code
Every order belongs to a customer; every invoice belongs to an order. These relationships are facts about your business, and they should be declared in the database itself, as foreign keys, not merely assumed by the application code. When they are not declared, nothing stops an order from pointing at a deleted customer, or an invoice from referencing an order that never existed. These orphaned rows corrupt reports long before anyone notices.
Declared constraints do three jobs at once. They prevent bad data from entering the system, they document the business rules for the next developer, and they let the database optimise queries, because it knows how tables relate. The usual objections — that constraints slow bulk imports, or that the application already checks — do not survive contact with a real system. Imports can be staged; application checks can be bypassed by the next script or integration that touches the database directly. The database is the last line of defence for your data, so put the rules there.
The same logic applies to simpler rules: unique constraints on email addresses and order numbers, not-null constraints on fields the business truly requires, and check constraints on values with a known range. Each is a one-line decision at design time that prevents a class of data-quality tickets later. Ask your developer: if I delete a customer, what happens to their orders? If the answer is a shrug, the relationships are not declared.
Mistake 3: Indexing by guesswork
An index is like the index at the back of a book: it lets the database find rows without reading the whole table. Without one on the columns you filter, join, and sort by, every query scans everything, and that cost grows with your data. The mistake most teams make is a lack of method: either nobody indexes anything until the system grinds to a halt, or somebody indexes every column just in case, which slows down every write.
The method is straightforward. Index the columns that appear in WHERE clauses, JOIN conditions, and ORDER BY clauses for your most common queries, and index foreign key columns, which are joined constantly. Then measure: check the slow-query log, and remove indexes that nothing uses. Indexing is maintenance, not a one-time decision — as your most-used features change, the right index set changes with them. A quarterly review of slow queries and unused indexes costs almost nothing and is one of the highest-value habits a team can adopt.
Mistake 4: Wrong data types and the junk-drawer column
Choosing the wrong data type looks harmless and costs in surprising ways. Money stored as a floating-point number accumulates rounding errors — a classic bug no front-end formatting can fix; money belongs in a fixed decimal type. Dates stored as text cannot be sorted reliably, and a phone number stored as a number loses its leading zero. Each is a one-line decision at design time and a migration project later.
The modern version of this mistake is the junk-drawer column: a single text or JSON field into which developers stuff whatever does not fit elsewhere. JSON columns have legitimate uses, but when the data inside them is filtered, reported on, or required to be valid, it should be a real column with a real type and a constraint. Data buried in a JSON blob is invisible to reports, easy to corrupt, and painful to migrate. If a value matters to the business, give it a column.
Mistake 5: No naming convention, no documentation, no migration discipline
The least technical mistakes cause the most long-term pain. A schema where one table uses customer_id, another custID, and a third customer_ref forces every developer to guess. A column named status holding five undocumented values forces every report writer to reverse-engineer its meaning. A schema with no documentation has a useful life equal to its builder’s tenure. The fix is simple: one naming rule, applied consistently; a comment on every table and column; and a living data dictionary the team actually updates.
Migration discipline is the other half. A schema should change the way code changes: as small, reviewed, reversible steps, tracked in version control, with a tested rollback. The alternative — somebody editing the live database directly — is how production systems end up in states nobody can reproduce. If your team cannot rebuild its database by running its migrations, fix that before the next feature, not after.
From our experience: fixing a bad schema without stopping the business
Over 9 years and 300+ projects, we have inherited schemas with every problem above. The approach that works is boring, and that is the point. First, measure: which queries are slow, which tables hold orphans, which reports disagree. Second, fix data before structure — clean up duplicates and orphans with the business owner deciding which record wins, because that is a business decision, not a technical one. Third, migrate in small steps, each deployed separately: add the new column, backfill it, switch the code to use it, then remove the old one. Never do all four in one release.
Two honest warnings. First, a schema fix always takes longer than the estimate, because the cleanup finds surprises — the duplicate customer who is actually two people, the order total nobody can reconcile. Budget for the surprises. Second, sometimes the right answer is a controlled rebuild of one bounded part, such as the reporting layer, while the transactional core stays untouched. Be wary of anyone who proposes rewriting the whole database at once.
For the recurring schema errors and their one-line fixes, DbSchema’s guide to database design errors covers the same ground from a tooling perspective and is worth a read alongside this article.
When to hire a data architect
Most small business systems do not need a dedicated data architect on day one — they need a competent developer who follows the rules above. Bring in specialist help when your data model spans multiple systems that must agree, you are designing for millions of rows or heavy reporting, you handle regulated data where integrity failures have legal consequences, or you are about to migrate a system the business cannot take offline. At that point, a few days of specialist review is far cheaper than a year with the wrong schema. If you are scoping a new system, our custom software development team can help, or talk to us about your project and we will tell you honestly whether you need us at all.
Frequently asked questions
What is the most expensive database design mistake?
Storing the same fact in two places without a rule for which copy wins. It corrupts reports silently, and fixing it needs business decisions about which records are correct, not just a technical change. Missing constraints are a close second, because they let bad data accumulate unnoticed.
Should a small business normalise to third normal form?
As a starting point, yes. Third normal form removes duplicated facts and the inconsistencies they cause, and it is the default for most business systems. Denormalise afterwards only where a measured, slow query proves it necessary, and document each exception.
How do you fix a bad schema on a live system?
In small, reversible steps: measure the problem, clean the data with business owners deciding conflicts, add new structures alongside old ones, backfill, switch the code over, and only then remove the old ones. Never rewrite the whole schema in one release, and always test on a copy of production data first.
SQL or NoSQL for business software?
For most business software — customers, orders, invoices, inventory — a relational SQL database is the right default, because the data is structured and the relationships matter. NoSQL earns its place for unstructured or extreme-scale workloads, such as event streams. Choose based on your access patterns, not on trends.
How much does a database redesign cost?
It depends on the size of the data and the surprises hiding in it, which is why honest estimates come after a data audit, not before. A small, well-understood schema can be repaired in days; a large, undocumented one is a project. The audit itself is usually the best money spent, because it turns an unknown into a plan.
Database design is not glamorous work, but it is the foundation everything else stands on. Get the facts in one place, declare the relationships, index what you actually query, use honest data types, and write down why. Do that, and your database stays an asset as the business grows — instead of the reason every new feature takes longer than the last.
If your system shows any of the symptoms above — reports that disagree, slow queries, a schema nobody can explain — get in touch. We will audit the schema, show you exactly what is wrong, and give you a fix plan in plain English before you commit to anything.