2

Im making a simple score keeping system and each user can have stats for many different games But i don't know if each user should have his own score table or have one big table containing all the scores with a user_id column

Multiple Tables:

table name: [user id]_score

game_id | high_score | last_score | average_score

Single Table:

user_id | game_id | high_score | last_score | average_score

Ilia Choly
  • 18,070
  • 14
  • 92
  • 160

4 Answers4

4

Definitely have a single table with the userID as one of the fields. It would be very difficult (and needlessly so) to deal with multiple tables like that.

You will likely want at least one index to include the userId field, so that the records for each userId can be quickly found by queries.

Andrew Barber
  • 39,603
  • 20
  • 94
  • 123
2

Go with a single table and a composite primary key around user_id and game_id. You may also want to create a supplementary index around game_id so that lookups of high scores by game are fast. (The composite key will not suffice if user_id is not part of the criteria as well.)

Using a one-table-per-user approach would be cause for some LARTing.

cdhowie
  • 158,093
  • 24
  • 286
  • 300
  • Judging by some of the fields in the example table, I don't think the composite key you suggest here would work; It seems to me that `game_id` is not referring here to a game event (eg. Sunday's football game), but instead a game title (eg. Tetris) – Andrew Barber Nov 10 '10 at 20:50
  • The column names suggest to me that he will be store one record per user-game combination. But if this is not true then, yes, the primary key would have to be changed. – cdhowie Nov 10 '10 at 20:52
  • Now when I read it, it seems like it could be, too. +1 anyway! heh – Andrew Barber Nov 10 '10 at 21:10
0

Keep one table with all of the user_ids in them, definitely. Generally, you want to stay away from any solution that involves having generated data (userIDs) in table names (there are exceptions, but they're rare).

Paul Sonier
  • 38,903
  • 3
  • 77
  • 117
0

you'll probably want:
a user table user_info[], user_id
a game table game_info[], game_id
a user_game_join table that holds user_id, game_id, score, insertdate

that way you have all the data to get high score by game, by user, by game & user, averages & whatnot or whatever else.

always build extensibly. you never know when you will need to refine your code and redoing a database is a real PITA if you don't lay it our right to start with.

FatherStorm
  • 7,133
  • 1
  • 21
  • 27