mysql - SQL storing large amount of data for each user -

i'm developing website, people able play small games. @ end of each game user given score, wish store in mysql database timestamp. allow me present data in nice way further down line.

i use facebook login system, have table consists of user id, name, , facebook id. have table looks this:

id------name------uid  1      bob     123123  2      marley   23134 

then, each user need this:

game1time    game1score   game2time   game2score etc....   3292          400           10           7824    129           32          101            231 

i've looked @ of other related posts, , seems people think it's bad idea have table each user, me, seems easiest way.

i this, store data in 1 table:

id------name------uid-------game1time-----game1score----game2time-----game2score etc...  1      bob     123123      3291, 129      400, 32       10, 101         7824, 231  2      marley   23134 

but seems might cause problems, when people play lot of same game.

i feel i'm missing smart way of doing this, if have recommendation, please tell me. i'm new sql i'm missing stupid

i recommend normalization here.

your users table fine. create games table store information games. create table combines information users , games , results. on lines of:

create table users (   id int primary key,   name varchar(100),   uid int );  create table games (   id int primary key,   name varchar(100) );  create table users_games (   id int primary key,   userid int,   gameid int,   startdate datetime,   enddate datetime,   score bigint,   constraint fk_users_games_userid foreign key (userid) references users(id),   constraint fk_users_games_gameid foreign key (gameid) references games(id) ); 

using startdate , enddate can calculate time user on game. can run statistics distinct users on given hour of day , whole bunch more reporting based on users_games table.

you can create indexes on users_games desire. table can grow big, no problem. can use partitioning userid if or archive data systematically data gets stale used on regular basis.

example schema here: http://sqlfiddle.com/#!9/315a5.