Mapping the great relational migration

The cliché is that data is the new oil, and if recent events have taught us anything, it’s that it can get very expensive, very quickly. This is especially true if it hasn’t been fine-tuned properly for what people really need. And what people need more and more is data refined for the document model. Databases like MongoDB offer global scalability coupled with rapid development.

While relational databases work for a number of scenarios, more and more companies are discovering the document model as a more flexible way to store and access their information. The question for them is how they can move their data to the document model.

The obvious, but incorrect, answer is to import the data directly into the document database. This is similar to taking diesel and putting it in the tank of a petrol engine. While both are hydrocarbon fuels, they have physical properties that make them work very differently. They are not, as many drivers discover, interchangeable.

The same goes for data. The essential components are the same, but just swapping it out and entering it into a document database will clog the database engine with relational searches that, while you can do them, conflict with an efficient document database. What you need to do is refine the data into documents, documents that fit both your business and your database.

Therefore, the real answer to moving data from table-facing relational stores to a document database is to perform a relational migration. Bee Studio 3T we have developed SQL to MongoDB and MongoDB to SQL migration tools. This allows you to take the foreign keys from your SQL database and other tables and convert them into embedded arrays or documents within your new database’s collections. This relational to document mapping is under your full control, allowing you to refine the results.

So the “customer account” records and the “all transactions” table can be converted to a document by customer, with all of the customer’s transactions embedded in that document. This highly localized data model works well because once you get the customer’s account, you don’t have to do an expensive join (in CPU and IO terms) to pull in all the transactions. They are already in the document you just retrieved.

It is not a one-time process; we’ve worked with developers who have used Studio 3T’s Tasks to automate and fine-tune their migrations and perfect their mapping so that they can obtain the optimal document structure for their data in a document world. And we have worked with Hackolade to bring their powerful modeling tool into the process, allowing a user to graphically redesign their models and then pass that redesign to Studio 3T to put it into practice.

Similar map technology also supports Studio 3T’s Reschema tool. That can be applied to documents already migrated, allowing them to be restructured in-situ and reducing the number of times the entire migration has to be performed. We believe that the more opportunities to fine-tune the data and its schedule, the better for everyone.

We’ve been doing this for a while, so we were excited to see the hulking document databases, MongoDB itself, announcing that they plan to release their own relational migration product in the future. It confirms what our customers have been doing with great efficiency and success for years using our proven tooling. And the great thing is that the tools are available for download today.

It also shows that the document database is expanding its reach, from the choice of a bold startup to the next enterprise database paradigm.

Legacy relational data faces the combined challenges of global agility, global compliance, and the need for rapid development.

The tools to enable an effective migration to the document model are already there.

This post was funded by 3T Software Labs

Leave a Comment

Your email address will not be published. Required fields are marked *