Search code examples
mysqlmongodbrdbms

Whats the most efficient way to migrate this relational structure to Mongo documents?

I have the following structure in SQL that I am looking to move into Mongo for a project using the MEAN stack. Basically its related to a user being able to write a review for a game. So I have a User table that has a relationship to a child Review table. A User can have many Reviews. The Reviews are joined to a Game table which is reference to all of the Game details for the ones that have been reviewed. I need the Games table to be able to store the Game details and have those appear in searches so that a Game can be selected and from there you can see all of the Reviews for said game.

As someone still new to the document style nature of Mongo, what would be the most efficient way to setup this up? Would I have 3 documents that mirror the tables below and just reference each other or would it be better to have separate documents for each game where each review is stored there as well? Reading through the documents on MongoDB's website for migrated from RDBMS dont really give me a clear picture of how I should move forward. Thanks for any input guys!

enter image description here

Solution

  • It depends on how many reviews will be. As this page says, if there is only a few I would include the reviews inside the game collection.

    games collection

    {
  "_id": ObjectID(123)
  "type": "action",
  "reviews": [
    {
      "rating": 8,
      "user": ObjectID(1)
    }, {
      "rating": 3,
      "user": ObjectID(2)
    }
  ]
}

    The same review won't be repeated through games, but users will, so keep users in other collection and reference them, this way won't be inconsistency or duplicated data.

    users collection

    {
  "_id": ObjectID(1)
}