As the name implies, Relational Databases (RDBMSes) maintain relationships between the tables to organize the data together in meaningful ways. Document databases such as MongoDB are sometimes called "schema-less" due to the fact that they don't really enforce relationships like RDBMSes do. However, while document databases don't require the same predefined structure as a relational database, that doesn't mean that they don't support it. In fact, MongoDB allows relationships between documents to be modeled via Embedded and Referenced approaches. In today's blog, we'll give each a try using Navicat for MongoDB.
The Test Case
As an example, we'll consider the use case of the ACME corporation. They need to store the addresses in such a way as to link them to employees. One employee can have multiple addresses, making this a one-to-many (1:N) relationship. That's no problem, as relationships in MongoDB can be any of one-to-one (1:1), one-to-many (1:N), many-to-one (N:1) or many-to-many (N:N), just as in relational databases.
Here is the document structure of the employees document in Navicat's JSON view:
And here are is the addresses document:
Creating Embedded Relationships
Using the embedded approach, we would embed the addresses document directly inside the employees document. We can easily do that in Navicat for MongoDB as follows:
- Open the addresses collection in JSON view and copy the last two documents:
- Switch to the employees collection and edit the first document:
- Paste the addresses into the employee document that you want to associate them with and enclose them within a "address" array element:
Pros and Cons
This approach maintains all the related data in a single document, which makes it easy to retrieve and maintain. The whole document can now be retrieved in a single query:
The drawback to Embedded Relationships is that if the embedded document keeps on growing too much in size, it can negatively impact read/write performance.
Creating Referenced Relationships
Using this approach, both the employee and address documents would be maintained separately, but the employees document would contain a field that references the address document's id field:
As shown above, the employee document contains the array field "address_ids" which contains ObjectIds of corresponding addresses. Using these ObjectIds, we can query the address documents and get address details from there.
Pros and Cons
Although this approach keeps document sizes to a more manageable size, we now need two queries to fetch address details: one to retrieve the address_ids fields from employees document and another to fetch the addresses from the addresses collection:
var result = db.employees.findOne({"name":"Tom Smith"},{"address_ids":1})
var addresses = db.addresses.find({"_id":{"$in":result["address_ids"]}})
Going Forward
In the next blog, we'll learn how to use MongoDB Referenced Relationships (also referred to as Manual References) and DBRefs in Navicat for MongoDB. You can learn more about it on the product page. You can download the fully functional application and use it FREE for a fourteen day trial period!
