CODEWITHSUNDEEP
mysqldatabase-designdatabase-schemaranking
maplater
maplater

Reputation: 171

Mysql database design for storing user scores over time

I'm creating a site where all of the users have a score that is updated everyday. I can easily create rankings from this score, however I'd like to be able to create a "Hot" list of the week or month, etc..

My brute force design would be each day for every user, calculate their score and put it into the "Scores" table. So everyday the Scores table would increase by how many users there are. I could rank users by their score deltas over whatever time period.

While I believe this would technically work I feel like there has to be a more sophisticated way of doing this, right? Or not? I feel like a Scores table that increases everyday by how many users there are can't be the way other sites are doing it.

Upvotes: 3

Views: 7310

Answers (2)

Joel Brown
Joel Brown

Reputation: 14408

You get the most flexibility by not storing any snapshots of score at all. Instead, record incremental scores, as they happen.

If you have tables like this:

USER

  • user_id
  • name
  • personal_high_score
  • {anything else that you store once per user}

SCORE_LOG

  • score_log_id
  • user_id (FK to USER)
  • date_time
  • scored_points

Now you can get a cumulative score for a user as of any point in time with a simple query like:

select sum(scored_points)
from SCORE_LOG
where user_id = @UserID
  and date_time <= @PointInTime

You can also easily get top ranking scorers for a time period with something like:

select
  user_id
, sum(scored_points)
from SCORE_LOG
group by
  user_id
where date_time >= @StartOfPeriod
  and date_time <= @EndOfPeriod
order by
  sum(scored_points) desc
limit 5

If you get to production and find that you're having performance issues in practice, then you could consider denormalizing a snapshot of whatever statistics make sense. The problem with these snapshot statistics is that they can get out of sync with your source data, so you'll need a strategy for recalculating the snapshots periodically.

It's pretty much a truism (consider it a corollary of Murphy's Law) that if you have two sources of truth you'll eventually end up with two "truths".

Upvotes: 4

AlexP
AlexP

Reputation: 9857

Barranka was on the right track with his comment, you need to make sure you are not duplicating any of the data wherever possible.

However, if you are looking to be able to revert back to some old users score or possibly be able to pick out a day and see who was top at a certain point i.e. dynamic reporting, then you will need to record each record separately next to a date. Having a separate table for this would be useful as you could deduce the daily score from the existing user data via SQL and just enter it in to the table whenever you want.

The decision you have is how many users record do you want to maintain in the history and how long. I have written the below with the idea that the "hot list" would be the top 5 users, you could have a CRON job or scheduled task running each day/month to run the inserts and also clean out very old data.

Users

  • id
  • username
  • score

score_ranking

  • id
  • user_id (we normalise by using the id rather than all the user info)
  • score_at_the_time
  • date_of_ranking

So to generate a single data ranking you could insert into this table. Something like:

INSERT INTO
  `score_ranking` (`user_id`, `score_at_the_time`, `date_of_ranking`)
SELECT
  `id`, `score`, CURDATE()
FROM
  `users`
ORDER BY
  `score` DESC
LIMIT
 5

To read the data for a specific date (or date range) you could then do:

SELECT * FROM score_ranking 
WHERE date_of_ranking = 'somedate' 
ORDER BY score_at_the_time DESC

Upvotes: 2

Related Questions