NoSQL / SQL / RoR: attempt to create a scalable rating table for a game

Question

I am trying to solve a difficult thing (it seems to me).

I have the following objects:

• PLAYER (several of them, with names like "John", "Peter", etc.). Each of them has a unique identifier. For simplicity, think that this is their name.

• GAMES (some of them, for example, are called "Hide and Seek", "Jump and Run", etc.). The same thing - everyone has a unique identifier. For simplicity, let it be the name for the moment.

• SCORE (this is a numeric value).

So how does it work.

Each player can play several games. He gets SCORE in every game.

I need to create a rating table - and not one!

Table No. 1: the most popular games Table No. 2: the best PLAYERS in all games (let's say, the general SCORE in each game). Table No. 3: the best PLAYERS for the GAME (according to SCORE especially that GAME).

I could create something direct right away, but that won't work. I will have more than 10,000 players; and 15 games that will grow for sure. The score can be either 0 or up to 1,000,000 (not sure if more is possible at the moment) for a player in the game. So I really need some relative data.

Any suggestions?

I plan to do this using SQL, but I can just use it to store keys; anything - any ideas are welcome.

Thank!

Answer 1

I would say two things.

. -, , .

1. :

SQL, + . INT uniq, . ( , , , " ", . , , .

( ):

[Player ID][Game_ID][Score]

- ... , , .

. , . .

3-TIER. datalayer -, "". - " ", :

PlayerSaveScore(int gameID, int playerID, int score)

Businesslayer , "" , , , 5 ..

- datalayer " ", , . , .

" " ( ), , "", " ". , -/, - !

" ", "", , .

, 100000 ! , .

2. ... ... :

, "-/-"... , .

"" .

, 15 , pr. , . -100 . , , "-100" , . , , 100 .

Top 100 datalist, . XML . " ".

. , , 100 . . .

" -100"... , "/", " ": o)

? , "" .

, : o)

Answer 2

, SQL-:

(Untested pseudo-SQL)

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

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

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

: SELECT * FROM scores WHERE score=(SELECT max(score) FROM scores WHERE game_id=$given_game) LIMIT 1

, (, , , , ).

, , . (, memcached RoR), .