A Database Full of Surprises: Migration Stories
The database is the heart of every application. And like a real heart — when it starts skipping beats, you've got a problem. Over years of practice, we've migrated, repaired, and rescued databases that shouldn't have existed. And yet they did, in production, with real data from real people.
These are stories that required coffee afterward. Sometimes something stronger.
The Table with Five Hundred Columns
Normalization is one of the fundamental principles of database design. There are textbooks, courses, certifications. And yet we encountered a table with exactly 487 columns. One table. For everything.
It was called data. Not users, not orders, not products. Just data. And it contained columns like: name, name2, name3, address_home, address_work, address_other, address_other2, phone1 through phone5, note1 through note20, and our favorite: temp_column_do_not_use_7.
A temporary column that wasn't supposed to be used. The seventh in line. Which means there were at least six previous temporary columns that also weren't supposed to be used.
The entire application was built on one enormous table instead of a relational model. Adding a new type of information meant adding another column. Finding data was like looking for a needle in a haystack — if the haystack had 487 needles and you didn't know which one was the right one.
Dates in Twelve Formats
A date is a date, you'd think. Simple. And yet in one database we found a column called order_date (VARCHAR type, naturally) with these values:
2023-01-1515.1.202315/01/2023Jan 15, 2023January 15th, 20231/15/2320230115Monday, January 15thlast weeksoon?- empty string
Twelve different formats (if we count "soon" and "?" as formats, which with some creativity we can). The reason? The field in the application was a plain text input without any validation. Users simply typed the date however they felt like. And the system happily saved it.
Migration to a proper date type took two weeks. Two weeks of parsing, guessing, and occasional phone calls to the client asking: "Do you know what an order dated 'soon' from 2021 means?"
The Address Field as a Universal Notepad
In one older application, there was a field called address that was supposed to — surprisingly — store addresses. In practice, it contained:
- Actual addresses (about 60% of records)
- Phone numbers ("602123456 — call in the afternoon")
- Email addresses
- Order notes ("Leave with neighbors, ring 3 times")
- Shopping lists (literally: "butter, bread, salami")
- Existential musings from one operator ("Why do I even work here?")
When we asked the client how this happened, they replied: "Operators complained they had nowhere to write notes. So we told them to use the address field and that we'd fix it later." That was in 2018. Nobody fixed it.
The isDeleted Column and Quantum Physics
Soft delete — marking a record as deleted instead of actually deleting it — is a sensible approach. But it requires consistency. In one project, we found an isDeleted column containing these values:
0(apparently no)1(apparently yes)true(as a text string)false(as a text string)yesnoYNmaybe(really)NULLdeletednot_deleted- empty string
The isDeleted column had thus become a quantum object — the record was simultaneously deleted and not deleted until someone observed it. And even then it wasn't certain.
We found out that seven different developers had worked on the project over time. Each had their own idea of what belonged in the column. And nobody bothered to check what the others had put there.
Fifty Gigabytes of Images in the Database
There's an eternal debate about whether to store images in the database or on the file system. Most sensible developers store images on disk (or in cloud storage) and put only the path in the database. But not all developers are sensible.
We took over an e-shop whose database was 53 GB. For an e-shop with a few thousand products, that was suspicious. After investigation, we found that every product had images stored as BLOBs directly in the database. But that wasn't the worst part.
Every image was stored in the original resolution from the camera — 24 megapixels, about 8 MB per photo. Nobody resized them. And each product had 5-10 photos. Plus the entire history — when a seller uploaded a new photo, the old one wasn't deleted, just a new record was added.
Database backup took four hours. Restoration took six. And the MySQL server would occasionally crash because fifty-three gigabytes of data isn't exactly what it's optimized for.
Production Without Backups
And now for the truly terrifying story. A client called us in a panic. Their web application wasn't working. The server had crashed. We asked about backups. The answer:
"Backups? The hosting does that automatically, right?"
No. It doesn't. Well — in this case, it didn't. The hosting provider had backups listed as a paid service. The client hadn't ordered it. And nobody else was handling backups.
The server's disk had a hardware failure. Part of the data was corrupted. The database couldn't be restored. Five years of customer data, orders, invoices — gone.
Unfortunately, we couldn't solve this situation with a magic wand. A specialized data recovery firm managed to salvage about 70% of the data from the damaged disk. At a price that was many times the annual cost of a backup service.
Why This Happens
Bad database design usually doesn't happen all at once. It's a gradual process. Someone creates a "temporary" solution that becomes permanent. Someone adds a column instead of a new table because it's faster. Someone turns off validation because "we don't have time to deal with that now."
And each of these shortcuts is fine if you go back and fix it. The problem is that nobody goes back. And two years later, you have a database where the "address" column contains shopping lists.
- ✗No indexes on tables with millions of rows
- ✗One giant table for all data
- ✗No backups or untested backups
- ✗No validation — database as dumb storage
- ✗Manual schema changes in production
- ✗Images and files stored as BLOBs in DB
- ✓Index key columns
- ✓Normalize your data model
- ✓Automated backups with tested restores
- ✓Database-level validation (NOT NULL, CHECK)
- ✓Version-controlled migrations
- ✓Binary data on file system or object storage
How to Prevent It
A few basic rules that will save hours of pain:
Design your data model upfront. Before the first line of code. Draw the schema, define relationships, choose the right data types.
Use migrations. Versioned, repeatable, reversible. No manual database changes in production.
Validate at the database level. NOT NULL, UNIQUE, CHECK constraints, foreign keys. Don't turn your database into a dumb storage bucket.
Back up. Automatically, regularly, and verify that backups can be restored. A backup you've never tested isn't a backup.
Don't store binary data in the database. Images, PDFs, videos belong on the file system or in object storage.
Back up your database at least once daily and regularly test restores on a staging environment. When migrating, always back up first, then migrate on staging, and only after verification move to production. An untested backup is worse than no backup — it gives you a false sense of security.
The Lesson
The database is the foundation on which the entire application stands. When the foundation is bad, sooner or later it shows — and fixing it costs orders of magnitude more than doing it right from the start.
Moral of the story: Data architecture isn't something you can postpone to "later." Invest in it from day one. Your future self — or the developer who inherits your project — will thank you. And you won't have to explain to anyone why the isDeleted column contains the value "maybe."
