From Tables to Documents Considerations
January 30, 2019

From Tables to Documents: Considerations on Migrating from RDBMS to NoSQL


Relational database management systems have been the gold standard for backend data systems for decades. From single instance Microsoft Access databases in the early 90s to the large multi-instance Oracle systems of today, RDBMS have been the tried and true data systems utilized by developers to meet application requirements. However, with the advent of NoSQL systems such as MongoDB, the data landscape has started to change dramatically.

While databases like MongoDB have become the “new hotness” and have received a lot of “buzz” in recent years, their concepts and methodologies have been around for several decades as well. With current advancements such as multi-document ACID transactional support, being able to leverage the flexibility and scalability of NoSQL is making the transition to database systems like MongoDB much more common in the enterprise. In this blog post, we will outline some considerations your organization will want to take into account when making such a transition.

Schema Design

Data modeling and design for NoSQL databases has some strong fundamental differences when compared to the traditional data modeling we see in relational systems. Below are some common terminologies differences you will see when moving to a NoSQL system.

Join$lookup, embedded documents

Schema design in these migrations will require a change in how your DBAs and your organization look at your data. While specific use cases when modeling your data will always be different, there are some common methodologies to follow when going through these exercises. In relational databases, joins are done with multiple tables; however, in NoSQL databases, embedded documents take the place of joins. The figures below illustrate the difference between the two methodologies. 

relational joins

Figure 1 - Relational joins with RDBMS

Embedded Doc

Figure 2 – Embedded document model   

In figure 1, we have an employee table that has a relationship with an asset table joined by the employee_id column in both tables. In figure 2, we can see how these two relational tables can be collapsed into a single document with an embedded “assets” document array. In this example, we are only using two tables; however, the concept remains the same with 10s or 100s of relational tables. The two different methods show a different way of thinking about the data model and how a document model schema is a much more natural way to think about your organization’s data. Instead of having data normalized across multiple tables, the employee data is collapsed into a single rich document.

A common approach to define the document schema when migrating from an RDBMS to a NoSQL system is to review your existing application’s most frequently run queries and identify groups of data that are commonly accessed at the same time. When groups of data are commonly accessed together, they generally make good candidates for being grouped together in a single document.

Analyzing your current access patterns and data operations will go a long way in ensuring the success of any migration plans your organization may be considering.

If you need assistance with those, OpenLogic offers professional services and support. Please don’t hesitate to contact us to see how can help your organization meet your Big Data/NoSQL goals.