I am modelling a game which can be single (player1 vs player2) or double (pair 1 vs pair 2, where each pair contains 2 players) and each game has a score. The game is group and date based e.g. Group A on date_n can contain n games, on date_m can contain m games. In firestore, I use a game collection which looks like this:
game =
{
date : '2019-01-14',
groupId : 'abcd',
player1 : 'Bob',
player2 : 'John', // can be null
player3 : 'Tony',
player4 : 'Mike', // can be null
score1 : 15,
score2 : 21
}
To easily query the ranking statistics (see below), I separate the game score int to two fields score1 and score2 where the final score would simply be score1:score2. Note that player2 and player4 are null for a single game, whereas player1 and player3 are never null/empty.
In a relational database e.g. SQL, it's just the same thing but in a game table. So far so good I think! The tricky part is when querying and writing game statistics for each player and pair. The player/pair ranking stats are also time-range based e.g. daily/monthly/annually/alltime. To simplify, let's say I just want to rank each player/pair based on how many games each player/pair played and how many games each player/pair won.
In firestore, to avoid nesting the data too deeply, I use 8 collections:
playerdailystats
playermonthlystats
playerannuallystats
playeralltimestats
pairdailystats
pairmonthlystats
pairannuallystats
pairalltimestats
and they all have the same structure, using playerdailystats as an example:
playerdailystats =
{
date : '2019-01-14',
groupId : 'abcd',
Bob : {
played : 1,
won : 0,
},
John : {
played : 1,
won : 0
},
Tony : {
played : 1,
won : 1
},
Mike : {
played : 1,
won: 1
}
}
where each player field (e.g. Bob) is an map itself; and for monthly/annually stats collections the first day of the month/year will be used for the date field. For player/pair alltimestats collection, I chose a const date randomly e.g. 1983-10-06 for the date field though it doesn't require a date field but it's added in order to reuse the same query on all these stats collections (see below). For pairstats collections, just like player stats collections, each pair field is still a map though instead of using a player's name, I use a pair name which is combined of two player's names in the form of name1,name2. Since this can be in a form of either player1Name,player2Name or player2Name,player1Name, I sort two names first then use combined the names separated by comma.
Now I can use a single function to query the stats for daily, monthly, annually and alltime for each group with a given date because all these stats collections have the same structure. On the UI, it only shows one time-range stats at a time e.g. monthly. So the query function is like this (I am using dart but the language doesn't matter):
Stream<Map<DateTime, List<Ranking>>> getRanking(String groupId, String collection) {
return Firestore.instance.collection(collection)
.where('groupId', isEqualto: groupId)
// why alltime stats collection need a date field.
.orderBy('date', descending: true)
.snapshots()
.take(5) // only show the latest 5 ranking.
.map((snaoshot){
// deserialize the data into domain models and return the result.
//in each stats collection, any fields that are not 'groupId' and not 'date' must be a stat field for a player or pair
});
}
The querying part isn't bad either since only one stats collection will queried at any time. Now the part that I think is potentially problematic: add a new game!
Any time adding a new single or double game for a given group, it doesn't only mean updating the game collection, but also mean updating ALL 4 or 8 stats collections of the group to keep the stats synchronised. Though I can again use one function to update all 8 collections due to their identical structure. The function for adding a new game result looks like this:
Future<OperationResultBase> addNewGameResult(GameResult gameResult) async {
try {
// first add the game result
await Firestore.instance
.collection(FirestoreName.GAMES)
.add(gameResult.toJson());
// then update player daily stats
await _updateRankingStatsNoTransaction(
gameResult,
FirestoreName.PLAYERDAILYSTATS,
gameResult.date,
RankingBase.parseGameResultToPlayerRanking);
// then update player monthly stats
await _updateRankingStatsNoTransaction(
gameResult,
FirestoreName.PLAYERMONTHLYSTATS,
_firstDayOfTheMonth(gameResult.date),
RankingBase.parseGameResultToPlayerRanking);
// then update player annually stats
await _updateRankingStatsNoTransaction(
gameResult,
FirestoreName.PLAYERANNUALLYSTATS,
_firstDayOfTheYear(gameResult.date),
RankingBase.parseGameResultToPlayerRanking);
// then update player all time stats
await _updateRankingStatsNoTransaction(
gameResult,
FirestoreName.PLAYERALLTIMESTATS,
DateTime(1983, 10, 6), // use a special const date
RankingBase.parseGameResultToPlayerRanking);
// if it's a double game,
if (gameResult.isDoubleGame) {
// then update pair daily stats
await _updateRankingStatsNoTransaction(
gameResult,
FirestoreName.PAIRDAILYSTATS,
gameResult.date,
RankingBase.parseGameResultToPairRanking);
// then update pair monthly stats using 1st day of the month.
await _updateRankingStatsNoTransaction(
gameResult,
FirestoreName.PAIRMONTHLYSTATS,
_firstDayOfTheMonth(gameResult.date),
RankingBase.parseGameResultToPairRanking);
// then update pair annually stats using 1st date of the year.
await _updateRankingStatsNoTransaction(
gameResult,
FirestoreName.PAIRANNUALLYSTATS,
_firstDayOfTheYear(gameResult.date),
RankingBase.parseGameResultToPairRanking);
// then update pair all time stats using const date.
await _updateRankingStatsNoTransaction(
gameResult,
FirestoreName.PAIRALLTIMESTATS,
DateTime(1983, 10, 6),
RankingBase.parseGameResultToPairRanking);
}
return OperationResultBase.ok();
} on PlatformException catch (error) {
return OperationResultBase(success: false, message: error.message);
}
}
The _updateRankingStatsNoTransaction function looks like this:
Future<void> _updateRankingStatsNoTransaction(
GameResult gameResult, // contain game result data
String collection, // which collection to update
DateTime date, // which date
// a function to parse game result to ranking map for a player or pair.
// hence I have two such parser functions, one for player, one for pair.
Map<String, Map<String, int>> Function(
GameResult, Map<String, Map<String, int>>)
rankingParser) async {
// firstly check whether there is a document already for the given group
// and date.
// if not, create a new document and insert the parsed ranking stats.
// if yes, update the existing document to store the updated ranking stats.
}
My questions:
Obviously I want to know whether there is a better way of doing all this. I feel like there needs a balance between making querying data and writing data relatively easy. Some way of designing the data structure could lead to querying easier but writing harder, or writing easier but querying harder.
Would a relational or graph database suit this kind of problem better? I did try to use SQL to model this before though can't find the code anymore. But I did have to use a lot of long nested queries/views/store procedures, which feels ugly.
