MongoDB schema: store id as FK or whole document

Question

I am designing MongoDB structure (the models structure in NodeJS app actually). I will have players and matches collections.

Is it better to store only the ids of the matches the player joined,inside each player's object (like a FK in RDBM) or store the whole object of match inside the player object?

In the application one of the action would be to show the details of the match and on this view the user will see the players that joined this particular match (their names, country etc.). That makes me think that storing whole Match document inside the Player document is better.

Any tips?

Answer 1

This is a case of many-to-many relationship. I am guessing that there will be about 100 players and 100 matches data, initially. The design options are embedding or referencing.

(1) Embedding:

The most queried side will have the less queried side embedded. Based on your requirement (show the details of the match and on this view the user will see the players that joined this particular match and their details) the match side will have the player data embedded.

The result is two collections.The main one is the matches. The secondary is the players; this will have all the source data for a player (id, name, dob, country, and other details).

Only a few players data is stored for a match, and only a subset of a player data is stored in the matches collection. Results in duplication of player data. This is fine, it is mostly static info that will be duplicated; things like name and country. But, some of it may need updates over time and the application needs to take care of this.

The player data is stored as an array of embedded documents in the matches collection. This design is the possible solution.

matches:

_id
matchId
date
place
players [ { playerId 1, name1, country1 }, { playerId 2, ... }, ... ]
outcome

players:

_id
name
dob
country
ranking

(2) Referencing:

This will also have two collections: players and matches. Referencing can happen with either side, the matches can refer the players or vice-versa. Based on the requirement, the most queried side will have references of the less queried side; matches will have the player id references. This will be an array of player ids.

matches:

_id
matchId
date
place
players [ playerId 1, playerId 2, ... ]

The players collection will have the same data as in earlier case.

Answer 2

Storing whole Match document inside the Player document is not a good option I think. Your player document will need to be updated every time the player play in a match.

You have 2 main alternatives:

1-) Using child referencing. (referencing player in match).

So if we want to imlement this using mongoose models:

Player model:

const mongoose = require("mongoose");

const playerSchema = mongoose.Schema({
  name: String,
  country: String
});

const Player = mongoose.model("Player", playerSchema);

module.exports = Player;

Match model:

const mongoose = require("mongoose");

const matchSchema = mongoose.Schema({
  date: {
    type: Date,
    default: Date.now()
  },
  players: [
    {
      type: mongoose.Schema.Types.ObjectId,
      ref: "Player"
    }
  ]
});

const Match = mongoose.model("Match", matchSchema);

module.exports = Match;

With these models, our match document will be like this (referencing playerId's):

{
    "_id" : ObjectId("5dc419eff6ba790f4404fd07"),
    "date" : ISODate("2019-11-07T16:19:39.691+03:00"),
    "players" : [
        ObjectId("5dc41836985aaa22c0c4d423"),
        ObjectId("5dc41847985aaa22c0c4d424"),
        ObjectId("5dc4184e985aaa22c0c4d425")
    ],
    "__v" : 0
}

And we can use this route to get match info with all players info:

const Match = require("../models/match");


router.get("/match/:id", async (req, res) => {
  const match = await Match.findById(req.params.id).populate("players");

  res.send(match);
});

And the result will be like this:

[
    {
        "date": "2019-11-07T13:19:39.691Z",
        "players": [
            {
                "_id": "5dc41836985aaa22c0c4d423",
                "name": "player 1",
                "country": "country 1",
                "__v": 0
            },
            {
                "_id": "5dc41847985aaa22c0c4d424",
                "name": "player 2",
                "country": "country 1",
                "__v": 0
            },
            {
                "_id": "5dc4184e985aaa22c0c4d425",
                "name": "player 3",
                "country": "country 2",
                "__v": 0
            }
        ],
        "_id": "5dc419eff6ba790f4404fd07",
        "__v": 0
    }
]

2-) Embedding players inside match, and still keeping a independent players collection. But this will need more space than first option.

So your a match will look like this in matches collection:

    {
        "date": "2019-11-07T13:19:39.691Z",
        "players": [
            {
                "_id": "5dc41836985aaa22c0c4d423",
                "name": "player 1",
                "country": "country 1",
                "__v": 0
            },
            {
                "_id": "5dc41847985aaa22c0c4d424",
                "name": "player 2",
                "country": "country 1",
                "__v": 0
            },
            {
                "_id": "5dc4184e985aaa22c0c4d425",
                "name": "player 3",
                "country": "country 2",
                "__v": 0
            }
        ],
        "_id": "5dc419eff6ba790f4404fd07",
        "__v": 0
    }

But this may be a little faster when getting a match info, since there is no need to populate players info.

const Match = require("../models/match");

router.get("/match/:id", async (req, res) => {
  const match = await Match.findById(req.params.id);

  res.send(match);
});

Answer 3

The way I see it, the matches collection here is a collection of documents that exists independently and then connected with the players that participates to the matches. With that said, I would do an array of match keys.

I would suggest going for a nested document structure if the document being nested can be considered as "owned" by the parent document. For example, a todo nested document inside of a todoList document.