noSQL/SQL/RoR: Trying to build scalable ratings table for the game

Question

I am trying to solve complex thing (as it looks to me).

I have next entities:

1. PLAYER (few of them, with names like "John", "Peter", etc.). Each has unique ID. For simplicity let's think it's their name.

2. GAME (few of them, say named "Hide and Seek", "Jump and Run", etc.). Same - each has unique ID. For simplicity of the case let it be it's name for now.

3. SCORE (it's numeric).

So, how it works.

Each PLAYER can play in multiple GAMES. He gets some SCORE in every GAME.

I need to build rating table -- and not one!

Table #1: most played GAMES Table #2: best PLAYERS in all games (say the total SCORE in every GAME). Table #3: best PLAYERS per GAME (by SCORE in particularly that GAME).

I could be build something straight right away, but that will not work. I will have more than 10,000 players; and 15 games, which will grow for sure. Score can be as low as 0, and as high as 1,000,000 (not sure if higher is possible at this moment) for player in the game. So I really need some relative data.

Any suggestions?

I am planning to do it with SQL, but may be just using it for key-value storage; anything -- any ideas are welcome.

Thank you!

Answer 1

I would say two things.

First my answer to your question. Secondly what I think you should do instead.

1. Answer:

SQL, its easy to develop and test + production for some time. A table for Players, with INT or some other uniq value, not strings. (I know you said its a sample, but go for "long word" ints that ought to give you enough unique ID's Same goes for Game. Now the thing to keep the highscores together would be to have a relation between the two.

Score (Table relation):

[Player ID][Game_ID][Score]

Where score is a numeric value... I dont know the max score of each of your games, so you figure out what type is enough.

Now, this should be quite easy to implement for a start. Get that to work. But dont make every call directly to the database.

Make a 3-TIER architecture. Make a datalayer and a businesslayer and then the "game" layer. So every game calls the businesslayer with its own "game ID" like:

PlayerSaveScore(int gameID, int playerID, int score)

The Businesslayer then checks that the "parameters" are of the correct size and are valid ID's, perhaps validates that this player actual has been in a session the past 5 minutes etc.

After validation, then the Businesslayer calles the datalayer for "update table" where the datalayer first looks if the record exists. IF not, then it inserts it.

Tier design Once you are "online" (in air) and the games becomes popular, then you can start to "upgrade", but you are still able to get going now with a "furture scaleable solution". Just remember that EVERY game MUST call to the business object/layer, not directly - NEVER!

I've been in the same "thought ooh so many times" but I kept getting into one simple loop called preparation, but that has almost never gotten me into a realistic solution thats up and running fast.

So get 100000 players first! then start worrying when it grows beyond.

2. Part to... how to scale... suggestion:

So here is my reason for all the trouble of building the "businesslayer/webservices"... And best of all, your speed problems can be solved nicely now.

You can implement "cache" quite simple.

You make an extra table, if you only have 15 games, you dont need a table pr. game, but you decide. That one ONLY keeps the TOP 100 of each game. each time you post a new record from a player, you make a select on this "top 100" and checks if the posted value comes into the list. if it does, then handle that by updating the top 100 table and for extra speed purpose.

Build the extract of Top 100 as a static datalist, eg. XML or similar static data. Depending on your platform, you pick the right "static format" for you.

You could even improve speed further. Just keep the smallest value needed to get on top 100 of each game. That would be a record pr. game.

Then match the player score against the game's "lowest score in top 100"... if its above, then you have some "caching/indexing" to do and THEN you call the "giant sort" :o)

Get the point? I know its a very long answer, but I wanted to post you a "complete" solution.

Hope you will mark this as your answer :o)

Answer 2

I don't see why this can't be solved with one score table and simple SQL queries:

(Untested pseudo-SQL)

create table scores {
  player_id as integer,
  game_id as integer,
  score as integer
}

most played games: SELECT count(*) AS c FROM scores GROUP BY game_id ORDER BY c DESC

best player: SELECT sum(score) AS s FROM scores GROUP BY player_id ORDER BY s DESC

best player in a given game: SELECT * FROM scores WHERE score=(SELECT max(score) FROM scores WHERE game_id=$given_game) LIMIT 1

If you need to get a list of the best players across all games simultaneously, you can extend that last query a little (which can probably be optimised with a join, but it's too early for me to think that through right now).

The number of rows you're talking about is tiny in database terms. If you cache the query results as well (eg. via something like memcached, or within your RoR application) then you'll barely touch the database at all for this.