SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Guru glenngould's Avatar
    Join Date
    Nov 2005
    Posts
    661
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Daily Weekly Monthly Scores

    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.
    Tweep List adds an avatar menu to Twitter (open source)
    Word Stats shows your most used words on Twitter

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    my advice: just store the raw scores, and do the counting (daily, weekly, etc.) in SELECT queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru glenngould's Avatar
    Join Date
    Nov 2005
    Posts
    661
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    Tweep List adds an avatar menu to Twitter (open source)
    Word Stats shows your most used words on Twitter

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by glenngould View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru glenngould's Avatar
    Join Date
    Nov 2005
    Posts
    661
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    Tweep List adds an avatar menu to Twitter (open source)
    Word Stats shows your most used words on Twitter

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by glenngould View Post
    ...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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru glenngould's Avatar
    Join Date
    Nov 2005
    Posts
    661
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Tweep List adds an avatar menu to Twitter (open source)
    Word Stats shows your most used words on Twitter


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •