Avoid SQL-Like Joint Entities in MongoDB

[Ingvord]

Issue Name (title)

Avoid SQL-Like Joint Entities in Document Oriented MongoDB

Summary

Our current MongoDB data model includes SQL-like joint entities, such as the UserRole entity. This approach can lead to complex queries, reduced performance, and suboptimal data modeling. To fully leverage MongoDB's document-oriented model, we may consider refactor our data model to use embedding and referencing instead now and in the future.

Steps to Reproduce

1. Inspect the current data model in MongoDB.
2. Identify the use of joint entities like UserRole.
3. Observe the complexity and performance of queries involving these joint entities.

Current Behaviour

• User document:

  {
   "_id": "userId1",
   "name": "John Doe",
   "email": "[email protected]"
   "etc": "..."
  }
  

• Role Document:

  {
   "_id": "roleId1",
   "name": "admin"
  }

• RoleUser:

  {
   "userId": "userId1",
   "roleId": "roleId1"
  }

Using joint entities leads to:

Complex queries that mimic SQL joins.
Reduced performance due to the need for multiple queries to retrieve related data.

Expected Behaviour

• Simplified data model using embedding for one-to-many relationships and referencing for many-to-many relationships.
• Improved query performance and maintainability.

Details

Embedding

For one-to-many relationships, consider embedding related documents.

Example: Embedding Roles within User Document

{
  "_id": "userId1",
  "name": "John Doe",
  "email": "[email protected]",
  "roles": [
    {
      "roleId": "roleId1",
      "roleName": "admin"
    },
    {
      "roleId": "roleId2",
      "roleName": "editor"
    }
  ]
}

Referencing

For many-to-many relationships or when the related data is large, an approach based on references is more appropriate.

Example: Referencing Roles in User Document

User Document:

{
  "_id": "userId1",
  "name": "John Doe",
  "email": "[email protected]",
  "roleIds": ["roleId1", "roleId2"]
}

Action Items:

• Refactor Current Data Model: Review and refactor the current data model to use embedding and referencing appropriately.
• Update Queries: Modify existing queries to align with the new data model.
• Test and Validate: Ensure that all functionalities work as expected with the refactored data model.

Benefits:

• Simplified Data Model: Easier to understand and maintain.
• Improved Performance: Faster queries and better utilization of MongoDB’s strengths.
• Enhanced Flexibility: More natural representation of relationships using MongoDB’s document model.