WIS and Studio 3T:

A SQL-to-MongoDB migration case study.

The fact is that without Studio 3T’s SQL Migration tool it would have been impractical to run this development the way we have. […] It is a sheer pleasure to use.

Rand Nix – IT Director, WIS

A World of Cotton

Wakefield Inspection Services (WIS) operates with offices in 85 different countries around the globe, and each office is inputting and maintaining detailed information on cotton shipments around the world daily — to include shipment status, expected delivery dates, even down to the weight of individual bales of cotton within a shipment.

The business of cotton inspection and data tracking is a bewitching blend of the traditional up to 400 years traditional – and the super modern – pulling data from APIs being rewritten hourly. And the range of data that is being gathered is highly specific to the nature of the cotton plant itself.

In addition to the usual Import / Export requirements such as Bills of Lading and Port of Departure, cotton comes with its own needs. Rand Nix, IT Director at Wakefield Inspection Services, explains how cotton differs from soy in the variety of data needing to be stored on it. “Cotton is traded on the strength of several distinct characteristics such as fiber length, fiber strength, color, ability to absorb dye and of course weight — all details we may verify and manage on behalf of our clients both pre and post shipment in addition to general shipment information.

Rand Nix, IT Director at Wakefield Inspection Services, explains how cotton differs from soy in the variety of data needing to be stored on it.

“Cotton is traded on the strength of several distinct characteristics such as fiber length, fiber strength, color, ability to absorb dye and of course weight — all details we may verify and manage on behalf of our clients both pre and post shipment in addition to general shipment information.”

Relational Table Sclerosis

In 2010 when Rand Nix and his colleagues were starting to build the WIS cotton inspections application, the document store model of database was only just beginning to get adopted. NoSQL was the future back then. And the present decreed that a regular MySQL database would be the sensible way to go.

Within a few years, as the number of WIS clients soared, and the range of data that needed to be tracked and interrogated multiplied, it became clear that MySQL was slowing them down.

“It’s things like the JOINS in RDBMS that are a pain. We really don’t need something like an overgrown spreadsheet for the hundreds of varieties of simple, flat documents that we work with. A document store like MongoDB makes it so much easier and faster.

“Here’s a simple example: say we have a client, you guys at 3T, whose portal carries data across 89 different fields. And we need to migrate only a portion of that data on a weekly basis, say the top ten fields that we really need. If you’re doing that in MySQL, the JOINS and indexing you need to keep on top of, are a big overhead. In MongoDB you just put what you want into a nested field in the document. Could not be easier.”

“We could have built it from scratch, but it would have taken over 100 man hours.”

A Cutting Edge Solution

With the objective of a full migration out of MySQL and onto MongoDB in mind, time was key when creating a state of the art system that could evolve with the needs of clients and sectors of the industry.

The fact that Studio 3T makes it so easy to set up and run a migration, reset the data selection, configuration and mappings, and rerun the migration again, makes it easy to iterate improvements to the system design step by step – and saved the development team time, and freed them from worrying about the complexity of the task ahead. Instead, they could focus on the new features in the updated system they were building.

“The fact is that without Studio 3T’s SQL Migration tool it would have been impractical to run this development the way we have. We probably would not have attempted it. But Studio 3T makes it so easy to customise the migration, that every time you think, “Oh, we could try it that way”, you can just go ahead and try it out, and oftentimes you find you’ve got a better solution as a result. It is a sheer pleasure to use.”