Don't copy your old database: design the data model first

What a field-to-field migration copies

Old business systems collect fields that exist because the system had nowhere better to put something. A notes field ends up holding price exceptions. A product name ends up holding a roll number because there was no record for individual rolls. A checkbox exists only so a script can decide whether an order needs approval. Each one solved a real problem on the day it was added.

A field map copies each of them to a matching field in the new system. The new system starts life with the same structure, the scripts have to be rewritten to read the same checkboxes, and the people who understood the workarounds are still the only people who understand them. The business pays for a new system and keeps its old problems.

A field map also misses data that never lived in the main system. Many businesses keep part of their history in XLS spreadsheets, CSV exports, database backups, PDFs, shared folders and even web pages. If the plan covers only the main database, that data is left out or cleaned up by hand later.

Design the model first, then map the sources to it

A data model is a description of the things your business deals with and how they relate. A wholesaler's model names customers, products, vendors, orders, purchase orders and invoices, and states the links between them, such as which purchase order filled which sales order. It describes the data in the form it should take. We call this kind of model an ontology. What an ontology is, explained for business owners covers the idea without the jargon.

Once the model exists, the mapping works from the model outward. For each source, you ask which part of the model each column, table, field or page fills. The need behind a workaround becomes a proper field, a relationship or a stated rule, and the workaround itself is left behind.

The format of a source stops mattering at this point. An XLS sheet, a CSV export, a database backup, a PDF and a web page each get their own mapping, and all of them land in the same model.

Illustration titled Two ways to move the same records. Left panel, Copy the fields: old record cards holding a product name with a roll number, notes with a contractor price and a needs-approval checkbox read by a script are copied unchanged into the new system, with a warning that the workarounds move too. Right panel, Design the model first: Zoho, XLS, CSV, PDF, backup and web page sources all feed one data model with product, inventory piece, price rule and approval rule, and the new system's schema fits the model, so each need gets one proper place. A bar at the bottom reads: Describe the business first, then map every source to it. — Figure 1. The same old records, moved two ways. The examples are simplified for illustration.

A worked example: one product record

Here is a simplified product record of the kind flooring businesses often end up with, and where each part goes under each approach.

In the old system	What it really means	Copy the fields	Design the model first
Product name "Oak Natural 7in, Roll 4471"	A style and color in a vendor's line, plus one specific roll	Same text in the new product name	A product record for the style and color, and an inventory piece for roll 4471 linked to it
Warehouse field with free text	Where that roll is stored	Same free text	The inventory piece's location
Notes: "contractor price, ask Dave"	A customer-specific price	Same note	A price rule linking that customer to that product
"Needs approval" checkbox read by a script	A business rule about which orders need approval	A checkbox plus a script to rewrite	A rule stated in the model, which the system enforces

After the copy, you still cannot count how many rolls of a style you have without reading product names. Prices still live in notes, and the approval rule still depends on a script someone has to maintain. After the model-first move, each of those questions has one place to look. The product is what you can sell, and the inventory piece is what is on the shelf.

Illustration titled One product record, taken apart. An old product record has four rows: product name Oak Natural 7in, Roll 4471; warehouse, free text; notes, contractor price, ask Dave; needs approval, checked. Arrows lead from the rows to four linked records: Product, Oak Natural 7in; Inventory piece, roll 4471 with its location; Price rule, this customer and this product; Approval rule, stated in the model. A bar at the bottom reads: The product is what you sell. The inventory piece is what is on the shelf. — Figure 2. The worked example as a picture: one overloaded record becomes four records, each with one meaning. Simplified for illustration.

The same method at much larger scale

We have used the model-first approach at much larger scale. Intelligence Factory's published case study on integrating dozens of EHR systems describes normalization work across nearly three dozen EHR and EMR systems. The inputs included APIs, databases, CSV and Excel files, PDFs, ERA/EOB files, notes and scanned documents.

The case describes four steps: extract the data from each source, map it to one internal ontology, have people review the initial mapping, and reuse the normalized structure for eligibility, billing and reporting. The mappings for each source were kept separate from the rules that use the data. That separation is what makes the method scale. One system's quirks stay inside its own mapping, and adding a new source means writing one new mapping while every report and rule stays as it is.

Illustration titled One mapping per source, one model for everything. On the left, nearly three dozen EHR and EMR systems supply databases, CSV and Excel files, PDFs, ERA/EOB files, scanned documents and APIs. Each source connects to its own separate mapping. All the mappings feed one ontology, and a note says the initial mapping was reviewed by people. The ontology feeds eligibility, billing and reporting. A callout says a new source needs one new mapping and the rules stay the same. A bar at the bottom reads: Each system's quirks stay inside its own mapping. — Figure 3. The pattern from the EHR case study: one mapping per source, one shared ontology, and rules kept separate from the mappings.

The same thing happened on a much smaller scale for a flooring business. When a sister company started its own migration, the model already described the business, so the work was mapping the sister company's sources to it. The second migration was the easy one tells that story.

How to do it, step by step

We build the model, and change CommerceServer to fit it, with Buffaly, our own software engineering system. The steps are the same whoever does the work.

List every source. Include the main system, XLS spreadsheets and CSV exports, database backups, PDFs, email archives, shared folders and any web pages the business relies on. Each one is a source that has to be mapped. Combining spreadsheets, database backups and PDFs into one system covers deciding which source is the truth for what.
Define the model. Name the things the business deals with and the links between them. For the flooring wholesaler in our Zoho case study, this took a couple of days.
Map each source to the model. Keep one mapping per source. Where the new system's schema does not fit the model, change the schema. With CommerceServer we change the schema to fit the business, and this is the step where years of workarounds end.
Load automatically. Write the import as a pipeline that loads the full history and can be run again from the start. When a run takes minutes, every correction can be followed by a clean rerun. Why your data import should be rerunnable explains why that matters.
Compare, then trace every difference. Check every field brought across against a backup of the old data. Trace each difference to its source, fix the mapping and rerun. Make every fix in the mapping, because a hand fix in the new system is lost on the next run.

The comparison is where the model pays off. On the flooring migration, 293,986 fields were compared with a backup of the old data, and the comparison finished with zero differences. Getting there meant tracing 43 exceptions to source documents. For 35 orders the correct date was missing from the old records, and it was recovered from the time the original order email was received. A field copy would have carried the wrong dates across without anyone noticing.

After the load comes the cutover. Running old and new systems side by side without double entry describes the parallel run, How long should a system migration take? gives a realistic timeline, and the business data migration checklist puts every step on one page.

Signs your migration plan is a copy job

You can check a plan you have been given against this list. Two or three of these together usually mean the old system's structure is being moved as it is.

The main deliverable is a spreadsheet of old fields matched to new fields.
Only the main system's database is in scope. XLS and CSV files, PDFs, backups and web pages are listed as manual cleanup or left out.
Every custom field in the old system has a matching custom field in the new one.
Scripts are marked "to be rewritten" with no description of the business rule each one enforces.
Data is cleaned up by hand in the new system after the import.
Only open records are moving, and the history stays behind. Moving off Zoho without losing your history explains why full history is worth bringing.
Nobody can describe the business's data model on one page.

If the plan passes, ask one more question: how long does one full import take, and can it be rerun from scratch? If the answer is days, corrections will end up being made by hand.

Replacing a system full of workarounds?

If your business runs on an old system that has collected years of custom fields, scripts and side spreadsheets, we start by describing your business, build CommerceServer around that description and move your full history into it. Tell us about your current system, or read how full service works.