CODEWITHSUNDEEP
mongodbmongoosenosql
ShintoTuna
ShintoTuna

Reputation: 3787

No-SQL database combine/count data

I am developing my first project with no-SQL database (mongoDB with mongoose ORM) and am in a bit of a trouble designing the data structure. Lets say I am developing an app for tracking game results and players statistics for a foosball game table for my office. The game consists of 4 players each either side A or B and can take an offensive or defensive positions. So i have this structure for storing data

Games collection:

Schema({
    id: String,
    side_a: {
        offense: {type: Schema.Types.ObjectId, ref: 'Player'},
        defence: {type: Schema.Types.ObjectId, ref: 'Player'},
        score: Number
    },
    side_b: {
        offense: {type: Schema.Types.ObjectId, ref: 'Player'},
        defence: {type: Schema.Types.ObjectId, ref: 'Player'},
        score: Number
    }
    winner: String, // side_a || side_b
    date: {type: Date, default: Date.now}
})

Players collection:

Schema({
    id: String,
    firstName: String,
    lastName: String
})

So my question is what would be correct way (or is it possible) to combine data from these collection so i would have following structure of JSON:

{
    "player_id": "123"
    "firstName": "Test",
    "lastName": "Test",
    "games_played": 280,
    "side_a": {
        "total_win": 100,
        "defence_win": 80,
        "offense_win": 20
    },
    "side_b": {
        "total_win": 84,
        "defence_win": 64,
        "offense_win": 20
    }
}

Upvotes: 0

Views: 54

Answers (1)

hecnabae
hecnabae

Reputation: 407

It depends. You could approach it in multiple ways:

1) Aggregation

This approach involves that you must include the fields "firstName" and "lastName" programatically, because mongodb doesn't allow join collections.

2) Embed stats in Player collection

Player data structure:

Schema({
    id: String,
    firstName: String,
    lastName: String,
    games_played: Number,
    side_a: {
        total_win: Number,
        defence_win: Number,
        offense_win: Number
    },
    side_b: {
        total_win: Number,
        defence_win: Number,
        offense_win: Number
    }
})

Disadvantage: With this approach you must update your Player collection every time the player plays a game.

Conclusion

If you wish to query stats quickly, use the second approach, but Keep in mind that this approach requires maintain the calculated fields manually.

On the other hand, if you use suitable index, you can get a good performance. The problem in this case is that you must include programatically "firstName" and "lastName" fields.

Upvotes: 1

Related Questions