User as stored in a seat is duplicated and can go out of sync

[merowin]

When a user takes a seat, the whole user document is copied into the game document. So the data is (unnecessarily) duplicated, which makes it possible to go out of sync (e.g. when the rating updates).

This can be reproduced by taking a seat, then finishing a game -> the user in the game document still has the old rating (observable in network tab).

A proposal to fix this is to only store the user id in the game document. Then we can maybe use lookup to join the two documents: https://www.mongodb.com/docs/manual/reference/operator/aggregation/lookup/

[benjaminpjones]

Makes sense, and it sounds like this would fix the "deleting user leaves user in games issue"

the data is (unnecessarily) duplicated

There is actually a reason I went this way, instead of querying the user table. Load is theoretically faster if all the data lives in a single document/collection. See Improve Your Schema: Reduce $lookup Operations:

$lookup operations can be useful when your data is structured similarly to a relational database and you need to model large hierarchical datasets. However, these operations can be slow and resource-intensive because they need to read and perform logic on two collections instead of a single collection.

If performance were critical, I'd say we need to optimize for game load over user edits. However, the performance gains are difficult to see at our scale, and the benefit w.r.t. keeping data in sync without additional code is probably worth the tradeoff.

[benjaminpjones]

This can be reproduced by taking a seat, then finishing a game -> the user in the game document still has the old rating (observable in network tab).

This was also intended (though I am open to changing the intended behavior). I feel this gives the proper context on the game "as it happened". Note: OGS also does it this way - if you go to an old game, you'll see the "historical" rating rather than the current rating.

[merowin]

Oh hm, if it's intended, please feel free to close this issue.

I'm still trying to wrap my head around how relations can / should work in MongoDB.

[benjaminpjones]

Oh hm, if it's intended, please feel free to close this issue.

It's fine for us to change the intended behavior, especially if it's perceived as a bug 😅 I just wanted to provide some context on why it was originally implemented this way.

I'm still trying to wrap my head around how relations can / should work in MongoDB.

I think the strength of "non-relational" databases like Mongo is exactly that relations are not necessary at all. But we can take a balanced approach if the use-case requires it.