CODEWITHSUNDEEP
performancejoinmongodbschema
Justin
Justin

Reputation: 555

Mongo multiple queries or database normalization

I'm using MongoDB for my database. The query that I'm currently working on revealed a possible deficiency in my schema. Below is the relevant layout of my collections. Note that games.players is an array of 2 players since the game is chess.

users {_id, username, ...}
games {_id, players[], ...}
msgs {_id, username, gameid, time, msg}

The data that I need is:

All msgs for games which a user is in which is newer than a given timestamp.

In a SQL database, my query would look similar to:

SELECT * FROM msgs WHERE time>=$time AND gameid IN
    (SELECT _id FROM games WHERE players=$username);

But, Mongo isn't a relational database, so doesn't support sub-queries or joins. I see two possible solutions. What would be better performance-wise and efficiency-wise?

  1. Multiple Queries
    • Select games the user is in, then use $in to match msgs.gameid by.
    • Other?
  2. Normalization
    • Make users.games contain all games a user is in.
    • Copy games.players to msgs.players by msgs.gameid
    • etc.,

Upvotes: 2

Views: 610

Answers (2)

Chris Nitchie
Chris Nitchie

Reputation: 557

I'm a relative newbie to MongoDB, but I find my self frequently using a combination of the two approaches. Some things - e.g. user names - are frequently duplicated to simplify queries used for display, but any time I need to do more than display information, I wind up writing multiple queries, sometimes 2 or 3 levels deep, using $in, to gather all the documents I need to work with for a given operation.

Upvotes: 2

Michael Gray
Michael Gray

Reputation: 611

You can "normalize" yourself. I would add an array to users that list the games he is a member of;

users {_id, username, games={game1,game2,game3}}

now you can do a query on msgs where the time>time$ and the {games._id "is in" users.games}

You will have to maintain the games list on each user.

Upvotes: 0

Related Questions