Member-only story
Many-to-Many/One Relationships are Simple in SQL, but Hard in NoSQL
A comparison of relationship modeling between SQL and NoSQL.
· Intro
· One-to-Many Relationship
∘ Referencing Pattern
∘ Embedded Pattern
∘ Partial Embedded Pattern
· Many-to-Many Relationship
∘ Intermediate Collection
∘ Bidirectional and Unidirectional Many-to-Many Relationships
· Conclusion
Intro
In this article, we will look at difference between modeling one-to-many and many-to-many relationship in SQL and NoSQL. Also, we will explore the different patterns that are used in NoSQL (document-oriented databases) and try to understand when which ones should be applied.
One-to-Many Relationship
Let’s imagine that we are developing an application that has Article and Comment entities. In addition, the requirements specify that:
- One article may include one or more comments.
- A comment can only belong to one article.
The above requirements mean that we should use a one-to-many relationship between two entities.
In a SQL databases, we usually create two tables Articles and Comments and create a foreign key (in the Comments table) to establish a one-to-many relationship. The client code will then have to join these two tables to get the article with comments.
When it comes to NoSQL databases, we have more options for defining one-to-many relationship — referencing, embedding, or a mixed approach.
Choosing between referencing and embedding is one of the key choices developers have to make when working with NoSQL document databases.
Referencing Pattern
Using referencing pattern in NoSQL is similar to defining one-to-many relationship in SQL. We can define two separate JSON…
