Need to know the best way to store some info

So I'm creating a database that is going to store 7k games in it. For each game it will have the id, title, cover, trophy count, and it also stores info about each trophy. some games could have up to 64 trophies. so that means 64 trophy names, 64 trophy images, 64 trophy types, 64 trophy descriptions.

Would I be better off using one table and adding everygame to it or would it be better to make a new table for everygame we add?

If I was to usee one table I would I run the sql query, some games could have 10 trophies and some could have 64, I'm not sure how to return them info

 

You definitely should seperate them, so use two tables, one for the games and one for the trophies.

I would use something like these:

CREATE TABLE IF NOT EXISTS `games` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `cover` varchar(255) NOT NULL,
  `trophy_count` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `trophies` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `image` varchar(255) NOT NULL,
  `type` varchar(15) NOT NULL,
  `description` text NOT NULL,
  `game_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `game_id` (`game_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Code (markup):

One thing I don't really understand. Do you have 64 trophies for every game (with different types, descriptions etc - means the maximum number of trophies 7,000 X 64) or just 64 maximum?

 

A game could have 10, 20, 30, etc. Each game is different with the amount of trophies, if a game did have 64 trophies that is 256 columns. so in your example when I do my query I would select from the trophies table where game id =$gid and it will just display the trophies for that game? Then I can order them by id so they will display in the order I inserted them into the databse?

 

Exactly, game_id in the trophies table is the same as the id in the game table.

Yes ,you can order by id or you can add a new field, something like `level` tynyint(2) NOT NULL that range from 1 to 64, and order by them.

 

What if I don't use a order in the sql statement, would they be in order the way I enter them. I need them to be in a certain order as they would appear on the game console.

 

I need some more help!!! Say I add a game that has 12 games, is there a way when I load the php form to load the trophies to the DB it will only display 12 fields then if I go to the the next game and it has 34 games it will display 34 fields on the php page?

 

We have reviewed your query and understand that it would be beneficial if we create 3 tables i.e. Game, Trophy and Game_Trophy_Relation
Game: [ID], [Title], [Cover], [TrophyCount]
Trophy: [TrophyID], [TrophyName], [TrophyImage], [TrophyType],
[TrophyDescription]
Game_Trophy_Relation: [GameID],[TrophyId]