CODEWITHSUNDEEP
mysqlmultiple-tables
Ilia Choly
Ilia Choly

Reputation: 18557

mysql multiple tables vs 1 table

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

Upvotes: 2

Views: 340

Answers (4)

cdhowie
cdhowie

Reputation: 169008

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.

Upvotes: 2

FatherStorm
FatherStorm

Reputation: 7183

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.

Upvotes: 0

Paul Sonier
Paul Sonier

Reputation: 39480

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).

Upvotes: 0

Andrew Barber
Andrew Barber

Reputation: 40150

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.

Upvotes: 4

Related Questions