0

I have a system where users can make predictions on events. There are about two events a week. When the event ends the system scores all the predictions and updates this in the database. Here is an approximate representation of my current DB strucutre:

Users table

+----+-----------+--------+
| ID |  username |  score |
+----+-----------+--------+
|  1 |  alice    |     12 |
|  2 |  bob      |     22 |
+----+-----------+--------+

Games table

+----+-----------------+----------------------+------------+----------+
| ID |    gameTitle    |       gameTime       |  questions |  answers |
+----+-----------------+----------------------+------------+----------+
|  1 |  Football match |  2019-08-11 14:00:00 |  {json}    |  {json}  |
|  2 |  Hockey game    |  2019-07-11 15:00:00 |  {json}    |  {json}  |
+----+-----------------+----------------------+------------+----------+

Predictions table:

+----+--------------+--------------+-------------+--------+-------+
| ID |  gameID (FK) |  userID (FK) |  prediction |  score |  rank |
+----+--------------+--------------+-------------+--------+-------+
|  1 |            1 |            1 |  {json}     |      6 |     1 |
|  2 |            1 |            2 |  {json}     |      4 |     2 |
|  3 |            2 |            1 |  {json}     |      2 |     2 |
+----+--------------+--------------+-------------+--------+-------+

So initially 'score' and 'rank' in predictions are NULL, and then after the event ends I run a script that goes through all entries for that game and scores it for the user and generates a rank.

With this structure I can do things like:

  • List the highest scoring users of all time (SELECT from USERS, score descending)
  • List the rankings for each event (SELECT rank from PREDICTIONS where gameID = X, descending)

However, what I now want to do is be able to get time-constrained rankings. So I want to be able to see which users scored highest for all events in the current month, or for the month of February etc.

I know I could do this manually by going through the Games table and getting all gameIDs that are in the given month, and then going through the Predictions table for all predictions with these IDs, and adding up the score for each user and then sending this. However that seems crazily inefficient and resource-intensive. Especially considering that this will be an API call

I'm therefore wondering how I can accommodate this in my current database - if it's possible and/or advised, or if I should use some other technology.

  • One idea would be have generate a new table for each month, and update and pull from this when relevant.

  • Another would be to have a 'monthly score' column in the User table, that resets each month. However this wouldn't have historical data, which would be nice (though I guess could always be generated)

I feel like there might be some database paradigm trick that I'm missing, so it'd be great to hear a proposed solution for this.

0

After thinking around a bit more, a solution I think could work would be to have a new table of monthlyScores, structured like this:

+----+--------------+----------+--------+
| ID |  userID (FK) |   month  |  score |
+----+--------------+----------+--------+
|  1 |            1 |  03/2019 |     12 |
|  2 |            1 |  04/2019 |     23 |
|  3 |            2 |  03/2019 |      3 |
|  4 |            1 |  05/2019 |     22 |
|  5 |            2 |  06/2019 |     11 |
+----+--------------+----------+--------+

Where month is the current month, I could increment this value for the current months' games, thus meaning I could provide a leaderboard of the leaders for the current month. Similarly I could display scores for each user for previous months.

Posting this here as an answer as I feel like it could work. That said, as you can probably gather I'm very new to databases and so would be interested to hear about other solutions, and/or if this is advisable!

  • Your scheme is already redundant. Adding more redundant data only worsens this schema. – Akina Aug 15 at 10:00
0
SELECT ...
    FROM ( getting all gameIDs that are in the given month ) AS x
    JOIN ... ON ...

See how you can use a "derived table" to do the first step of your chain? It is a simple SELECT gameIDs FROM ... WHERE ....

You may end up with

SELECT ...
    FROM ( SELECT ...
               FROM ( SELECT ...
                    ) AS x
         ) AS y

or even deeper.

  • Thanks for your help. I was able to accomplish the desired result with the query I've posted as an answer. I'm not sure how efficient this approach is considering it will be called several times a minute, but I feel like it's more correct than having a new table for the scores. – David Aug 16 at 14:05
0

I was able accomplish this with the following query:

SELECT u.username, SUM(score) as monthlyScore
FROM ( SELECT id FROM `games` WHERE MONTH(gameTime) = 8 ) g
INNER JOIN `predictions` p
    on g.id = p.gameId
INNER JOIN `users` u
    on p.userId = u.id
    GROUP BY userId  
ORDER BY `monthlyScore`  DESC

I'm not sure how this ranks in terms of efficiency. This query will called every time a user loads the leaderboards page, but it works.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.