We're building a fantasy application and trying to figure out what kind of architecture to use when attributing users with points.
Today we have a MySQL database to persist point events alongside a Redis sorted set to cache positions for users.
The process looks something like this:
Results come in -> Players are attributed points -> Users get points based on their picks and points events are persisted in the MySQL database -> Redis cache is updated with the latest positions
(Players are not our users, they are the professionals which you can pick in a fantasy tournament)
It works great in a smaller environment, but as we are going to scale there will also be more users, and I'am afraid that when we store an event for each user that the database will be crowded with events. Say that we have 1 000 000 users, each user picks 5 players, and there are 20 matches in a tournament. That would mean that we would have to persist 100 000 000 rows for a single tournament.
We are considering moving to a process where we skip the 3rd step and do not persist the user events, then we compute the points when a requests comes in and store it in redis cache.
It would look something like this:
Results come in -> Players are attributed points -> Redis cache is updated with the latest positions
The downside I can see with that is that we do not save user events anymore and thus lose some data.
Which of the alternatives would be best practice to use? And also, are there any better solutions that we could utilize than those?