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.player开发者_JAVA百科s by msgs.gameid
   • etc.,

---

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.

---

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.