structure

[dose]

What would be the best way anyone can think of for me to store the following info.

 

Game

achievments

earned

users

 

I have no idea how i should store all this info.. Im doing more than just 1 game could be hundreds same with the achievments and users..

 

ive thought about 1 table per user till i found out its bad.. then i was thinking of 1 table per game then a row for each achievment.. but i dunno where to go from there how i would add the rest of the info or to where and then link it all together..

[agrant]

First thing you have to know is what are the relations to each other?

 

Example... a game can have zero to many different achievements or a user can only have one game... etc once you clarify that it is much easier to specify a data structure.

[ignace]

game (id, name)

achievement (id, game_id, title, description, image_uri)

user (id, username, password)

achievement_collection (user_id, game_id, achievement_id, data)

 

Don't worry about the logic behind achieving an achievement as the game is responsible for this your database is responsible for storing it.

 

A query to select all achievements for user $uid filtered by game $gid:

SELECT a.*
FROM achievement_collection ac, achievement a
WHERE ac.user_id = $uid AND ac.game_id = $gid AND ac.achievement_id = a.id