Save into 1 table or create multiple tables?

Question

Hi, I am in dilemma situation, and do not know which one is better. Lets say I have 100++ (or more) games on my website. I want to store highscore for each game. Should i store all the highscore in 1 table or each game has its own table?

Compare:

1 table: 1 table contains a lot of rows(data). Every user play different game will submit score to this table. Dont know what will happen if many users submit at the same time. This table structure should be: gameID, name, score. So i just make 1 query to view what game's highscore in 1 php page.

If use multiple tables (each game has its own table), I would have many more tables but much lesser rows(data).

Which one is better in long run? How about the sql performance?

i am using php and mysql

Answer 1

I would make a table for players, one for games and a many-to-many table for highscores with player_id, game_id and the highscore value.

Discussion

If you have 100.000 players and 500 games you can get to have a maximum of 50.000.000 entries. But they are numerical and very short. It will be a good idea NOT to serve this highscores using live queries but results from cached files. This is how almost everybody is doing. You update the cached lists of highscores each day. This way you do the writes live and the reads from cache and they don´t interfere.

Another suggestion it will be to reset the highscore table each month, or to keep older data separate based on access frequency. Facebook is doing something like this, as I´ve seen in a presentation of their lead developer.

Answer 2

You should go with the single table option. That way when you add more games you don't need to change your database schema.

Databases are designed to cope with data like this. Having one table with a lot of data will not cause you any problems as long as it is indexed correctly.