In NoSQL data modeling I analyzed NoSQL data modeling and ways to model many to many relationships. In another post I added a few examples of 1-to-many bound and unbound cases.
In this post, I’ll illustrate bound and unbound cases for many-to-many. Plus I’ll identify an instance where use of associative collection becomes necessary.
Many To Many Bound
Let’s take a practical example of Cab is assigned to many Drivers and Driver is assigned to many Cabs. Obviously, this relation is bound on both ends i.e. at most a driver can be assigned to a limited cabs and similarly a cab can not be driven by more than a handful of drivers. Due to the said limitation, we’re certain that assignment on either side cannot even exceed 10 let alone thousands or millions. Therefore, this is the case of bound.
In such cases, we can always keep array of references on either side as shown below: (Note that associative collection explained later is also possible in bound cases, but a new collection is usually an overkill).
Option 1
Note cab1 and cab2 both have overlapping drivers assigned to them.
Cabs
{
id: "cab1",
.
.
.
.
drivers: ["driver1", "driver2", "driver3"]
}
{
id: "cab2",
.
.
.
.
drivers: ["driver1", "driver2"]
}
Drivers
{
id: "driver1",
.
.
.
}
{
id: "driver2",
.
.
.
}
{
id: "driver3",
.
.
.
}
Option 2
Note driver1 and driver2 are assigned to same cabs
Drivers
{
id: "driver1",
.
.
.
.
cabs: ["cab1", "cab2", "cab3"]
}
{
id: "driver2",
.
.
.
.
cabs: ["cab1", "cab2", "cab3"]
}
Cabs
{
id: "cab1",
.
.
.
}
{
id: "cab2",
.
.
.
}
{
id: "cab3",
.
.
.
}
Many To Many Unbound
Let’s take another example: User can like many Posts and Post can be liked by many Users. Notice the limitlessness here. A user possibly can like millions of posts, and similarly a post can be liked by millions of users. For this reason it’s an unbound relation.
Reference array in such cases cannot be placed on either side of relation (user or post) as the ever-increasing references will exceed the size-limit of a document in no time.
The only feasible solution is an associative collection (similar to associative class in OOP and associative table in SQL) in between, that keeps references of both sides.
Notice below how both user1 and user2 like the same post 1 (post1) and we don’t have to keep array of references. We use like1 and like2 documents instead.
Users
{
id: "user1",
.
.
.
.
}
{
id: "user2",
.
.
.
.
}
Post
{
id: "post1",
.
.
.
}
Likes
{
id: "like1",
user: "user1",
post: "post1"
.
.
.
}
{
id: "like2",
user: "user2",
post: "post1"
.
.
.
}
Conclusion
To sum up many to many examples above:
-
If bound and small, array of references can be placed on either side
-
If bound but too large to keep references array or if unbound, reference should be placed in associative document in between the two
