Industry Voice: Mapping The Great Relational Migration

clock • 3 min read
Industry Voice: Mapping The Great Relational Migration

The cliché is that data is the new oil and if recent events have taught us anything, it is that it can get very expensive, very quickly. This is especially the case if it's not refined the right way for what people actually need. And what people need more and more is data refined for the document model. Databases such as MongoDB offer global scalability combined 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 of storing and accessing their information. For them the issue is how to move their data to the document model.

The obvious, but incorrect, answer is to import the data straight into the document database. This is akin to taking diesel and putting it into the tank of a petrol engine. Although both are hydrocarbon fuels, they have physical attributes which 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 over and feeding it into a document database will clog up the database's engine with relational lookups which, while you can do them, are counter to an efficient document database. What you need to do is to refine the data into documents, documents that match both your business and database.

That's why the real answer to moving data from table-centric relational stores to a document database is to perform a relational migration. At Studio 3T we've developed SQL to MongoDB and MongoDB to SQL migration tools. These let you take your SQL database's foreign keys and other tables and turn them into embedded arrays or documents within your new database's collections. This mapping of relational to document is under your complete control so you can fine-tune the results.

So the "customer account" records and table of "all transactions" can turn into a document per customer, with all the customer's transactions embedded in that document. This highly localized model for data works well because once you've retrieved 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's not a one-shot process; we've worked with developers who've used Studio 3T's Tasks to automate and tune their migrations and perfect their mapping so they can obtain the optimal document structure for their data in a document world. And we've worked with Hackolade to bring their powerful modeling tool into the process, allowing a user to graphically redesign their models then passing that redesign onto Studio 3T to put it into practice.

A similar mapping technology also powers Studio 3T's Reschema tool. That can be applied to already migrated documents so they can be restructured in-situ and reduce the number of times the whole migration has to be run. We think that the more opportunities for tuning the data and its schema, the better for everyone.

We've been doing this for a while, so we were pleased to see the behemoth of document databases, MongoDB themselves, announce that they plan to release their own relational migration product at some point in the future. It validates what our customers have been doing with great efficiency and success for years using our battle-tested tooling. And the great thing is the tools are available for download today.

It also shows that the document database is expanding its reach, from being the choice of plucky startup, to the next database paradigm for enterprises.

Legacy relational data is facing 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 here.

This post was funded by 3T Software Labs

More on Threats and Risks

Zhao was a Communist Party posterboy for years, but recently criticised the way the Government operated

Chinese chip mogul Zhao Weiguo disappears

Zhao's criticism of the government-led reorganisation of his company may be related

clock 08 August 2022 • 2 min read
Microsoft email users targeted in new phishing campaign that can bypass MFA

Microsoft email users targeted in new phishing campaign that can bypass MFA

Fintech, insurance, accounting, lending and credit union entities in the US, UK, New Zealand and Australia have been targeted in what seems to be an effort to steal funds

clock 05 August 2022 • 3 min read
Politicians and security experts have raised concerns over TikTok's treatment of user data for years

UK Parliament shuts TikTok account over China concerns

The account only lasted a week.

clock 04 August 2022 • 3 min read