Daily Weekly Monthly Scores

[glenngould]

I have a word game which stores user data in a MySQL database.

I need to display daily, weekly, monthly... leaderboards in the game.

I plan to use a single table for all, structured like:

Table: gameScores
Columns: userID scoreType dailyScore weeklyScore monthlyScore totalScore

In this structure I'll need to use cron jobs to reset all users' scores with an update query, running daily, weekly, monthly...

Another way I can think of is to use separate tables for each time interval. This way I can truncate the tables using cron jobs. I wonder will this be better in terms of performance?

I'm looking for suggestions on how to structure this kind of a database.

[r937]

my advice: just store the raw scores, and do the counting (daily, weekly, etc.) in SELECT queries

[glenngould]

As discussed here before each word built is stored in a table like:

Table: wordsMade
Columns: userID word score ...

There are different types of scores (score, totalWords ...) and they are stored in a column for each user; not retrieved from the wordsMade table each time.

What do you mean by a raw score?

[r937]

What do you mean by a raw score?

i was just going by the information, sparse as it was, presented in post #1, without the familiarity that you obviously have with your own data

[glenngould]

Okay, sorry for not describing it clearly. As you can see it's not possible with this structure to use SELECTs to get timely scores. The wordsMade table could be used to gather all kinds of statistics but it would result in performance problems wouldn't it?

[r937]

...but it would result in performance problems wouldn't it?

that depends on two factors -- how often you want to display those statistics, and whether the statistics query is properly optimized (i.e. indexes on the appropriate columns for that particular query)

[glenngould]

The leaderboard(s) is shown on almost every page of the application.

So to display each leaderboard (different types and timeframes), we need to calculate a lot for each user, and sort each time, if we were using the words made table (that contains every word/score pairs for all users) to do that.