Database design and data cleanup

The short version

Most businesses that say they have a reporting problem have a data problem. The numbers disagree because the same customer exists four times, half the dates are stored as text, and the field called "status" contains eleven different spellings of three ideas. No dashboard fixes that. The schema underneath has to be right first.

• If two reports disagree, the question is not which is correct. It is why the data allows both.

• Good schema design is mostly about making bad states impossible, not about being clever.

• De-duplication is a business decision before it is a technical one — you decide what "the same customer" means.

• A cleanup and migration typically runs $3,000 to $12,000 depending on how much mess there is.

• Never destroy the original. The messy source is archived untouched, so every decision stays reversible.

Why you cannot trust your own data

Because nothing ever stopped it going wrong. Data that accumulates in spreadsheets and forgiving systems drifts, and the drift is invisible until you try to count something.

The four causes, in roughly the order we find them:

• No single identity — "Acme Inc", "ACME Incorporated" and "acme" are three customers to the database and one to you.

• Free text where there should be a fixed list — statuses, categories and units typed by hand and spelled differently every time.

• Wrong types — dates, money and phone numbers stored as text, so they sort alphabetically and cannot be compared.

• No relationships — the link between a job and its invoice exists only because somebody copied a reference number correctly.

The compounding cost is that people stop trusting the reports, and once they do they build private spreadsheets to check them. Now you have a second set of numbers, and no one can say which is right.

What good database design buys you

Certainty, mostly. What sits on top of it is usually an admin dashboard. A well-modeled schema makes whole categories of error impossible, not merely discouraged, which means you stop finding them in reports six months later.

Concretely, that means:

• One row per real thing, with a stable identifier that never changes even when the name does.

• Constraints in the database itself — a job cannot reference a customer who does not exist, an order line cannot have a null price.

• Fixed lists for anything you will ever group or filter by, so "status" has five values and always will.

• Correct types, so dates sort like dates and money never suffers a rounding surprise.

• Indexes on the columns you actually search, which is the difference between a report taking a second and taking a minute.

• History where it matters — what a price or address was at the time of the order, not just what it is today.

That last point catches people out. If your invoices read the customer's current address rather than the address at the time, last year's invoices quietly rewrite themselves. Deciding what needs history is a design conversation worth having once, properly.

What data cleanup involves

A pass over what you have, a set of rules agreed with you, and then a repeatable script — not a person editing rows by hand. Repeatability is the point: the import gets run dozens of times during a project, and manual edits cannot survive that.

The stages:

• Profile — count what is actually in there. How many customers, how many near-duplicates, how many rows fail a basic sanity check.

• Agree the rules with you — is matching on email enough? Do two locations of the same company merge or stay separate?

• Normalize — trim, standardize casing, parse dates and phone numbers, map eleven statuses onto five.

• De-duplicate — merge with a record of what was merged into what, so a wrong merge can be undone.

• Quarantine the rest — anything ambiguous goes on a list for a human, rather than being guessed at.

• Archive the original — the source data is kept untouched and unmodified, permanently.

Expect the quarantine list. On a real dataset a few percent of rows will need a human decision, and pretending otherwise is how you get silent data loss. It is usually an afternoon of somebody's time, once.

MySQL and PostgreSQL, and why the choice matters less than you think

For most small business systems, MySQL and PostgreSQL are both fine answers, and the choice matters far less than the modeling. We build on both, and we pick per project, not by habit.

The honest position is that anyone who tells you your choice of database is the important decision is selling something. What decides whether your data is trustworthy in three years is whether the tables were modeled around your actual business, and whether the constraints were written down.

What it costs

Data work is usually quoted alongside a build, but it is also a project on its own — and sometimes it is the only project you need.

What moves the number is the number of source systems and how much of the mess needs a human decision. Two clean exports are a two weeks. Eleven years of spreadsheets from four offices is not, and we would rather say so before quoting than after.

Keeping it from getting messy again

This is the part that gets skipped, and it is why businesses end up paying for the same cleanup twice. Clean data does not stay clean on its own; it stays clean because the system refuses to accept the things that made it dirty.

What that means in practice:

• Validate at the point of entry, not in a nightly job. Rejecting a bad ZIP code while someone is typing it costs a second; finding it three months later costs an hour.

• Drop-downs instead of free text wherever you will later want to group or count.

• Check for a likely duplicate at the moment a record is created, and show the existing one instead of silently creating a second.

• Import routines that quarantine instead of guessing — most re-contamination arrives through a bulk import nobody supervised.

• A monthly health check: duplicate counts, records failing validation, fields left empty that should not be. Five minutes, and it catches drift early.

The last one is worth automating even if you do nothing else. A simple report that tells you the duplicate rate is climbing is the cheapest data quality tool available, and it turns a future cleanup project back into an afternoon.

Migrating without a bad weekend

By rehearsing. The migration script is written early, run repeatedly against real data throughout the project, and checked with counts you agree in advance — how many customers, how many open jobs, what the outstanding balance totals.

By the time you go live, the import has run twenty or thirty times and the numbers match every time. Launch day is the rehearsal you have already done, not the first performance. If something does go wrong, the old system is still there and untouched, because we do not switch it off on day one.

The quickest way to find out how bad your data really is — usually less bad than feared — is to send a CSV export, anonymized if you prefer. The duplicate rate and the main structural problems are readable from the data itself, far faster than from a description of it.