I'm building a gaming backend with Mongo, and have some issues on how to best design the schema to maximize performance and database size.
My models:
User
Match
-ReferenceMany (User)
-ReferenceMany (Score)
Score
-ReferenceOne (Match)
-ReferenceOne (User)
It takes 2 users to start a match. Say we get 10.000 users, and all play one match each day against another user, we get 5000 games a day.
Each match has three turns, which gives 6 scores, so for 5000 games we get 30,000 scores. These scores can be inserted simultaneously (in each game), so I have to make sure one user doesn't overwrite another users score. I believe I've solved that by having the scores in their own collection, and embedding them on the match, like so:
$score = new Score();
$score->setUser($user);
$score->setScore($playerScore);
$score->setGame($game);
$score->setMatch($match);
// Save score.
$dm->persist($score);
// Add score to match.
$match->addScores($score);
My issue with this approach though is that it takes 6 queries just to insert a score (there are some validating queries before the above code). With 30.000 scores a day, that's a whole lotta queries.
My initial thought was to just add the scores to the match as an array, but what will happen the if two users submit at the exakt same time, updating the same match?
I'd be really interested in hearing some opinions on this. Let me know if I need to clarify anything. I'm using Symfony2 with Doctrine ODM if that helps.
Best Answer
Why do you have Score model? Why not keep only User and Match models and for each have a score attribute? In this schema you have only the updates per score. If its due to redundancy of attributes, please note this is common in a document based schema design.