Optimal Table Structure for league game stats

[falltime]

I have a database that is used to store information for a gaming league - particularly game-by-game statistics. The purpose of this database is to: 1. Store information and statistics for each game played in the league. 2. Store individual game-by-game statistics for each player in the league. Individual game stats such as: kills, deaths, hero, level, creep kills, etc.

 

I have two tables for the statistics:

 

A game table:

 

CREATE TABLE `games` (
  `HOST` varchar(16) NOT NULL default '',
  `GID` mediumint( unsigned NOT NULL auto_increment,
  `LOBBYID` int(10) unsigned NOT NULL default '0',
  `REALM` tinyint(2) unsigned NOT NULL default '0',
  `team1Kills` tinyint(3) unsigned NOT NULL default '0',
  `team2Kills` tinyint(3) unsigned NOT NULL default '0',
  `TYPE` tinyint(3) unsigned NOT NULL default '0',
  `GAMETIME` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `lastReport` mediumint(10) unsigned NOT NULL default '0',
  UNIQUE KEY `GID` (`GID`),
  KEY `TYPE` (`TYPE`),
  KEY `HOST` (`HOST`),
  KEY `LOBBYID` (`LOBBYID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=468 ;

 

A player table:

 

CREATE TABLE `players` (
  `GID` mediumint( unsigned NOT NULL default '0',
  `PLAYER` varchar(16) NOT NULL default '',
  `kills` tinyint(3) unsigned NOT NULL default '0',
  `deaths` tinyint(3) unsigned NOT NULL default '0',
  `ckills` smallint(3) unsigned NOT NULL default '0',
  `cdenies` tinyint(3) unsigned NOT NULL default '0',
  `color` tinyint(2) unsigned NOT NULL default '0',
  `hero` varchar(7) NOT NULL default '',
  `level` tinyint(2) unsigned NOT NULL default '0',
  `REALM` tinyint(2) unsigned NOT NULL default '0',
  KEY `GID` (`GID`),
  KEY `PLAYER` (`PLAYER`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 

 

When a game is created, a row is inserted into the game table with all of the pertinent game information. Then a row for each player in the game (generally 10) is inserted into the player table. The GID primary key is used to link the two tables. Initially this appeared to be the best way to structure the database. I didn't see any other way to easily track statistics for each player on a game-by-game basis for efficient queries later. However, it has occurred to be that the player table is going to balloon extremely quickly. I can assume that at least 100 games get played a day in the league. Generally there are 10 players in each game, so that comes out to about 1000 new rows in the player table a day - 7000 a week. 100 games a day is also a rather light estimate. 

 

I have a lot of experience designing databases for small companies to store information on a rather simple, small-scale basis. But never have I ever dealt with anything on such a large scale, and I have absolutely no idea how the database will hold up... Maybe it will be totally fine. Maybe the structure is as efficient as it can be.

 

If anyone has any advice or pointers to effectively optimize this structure keeping my initial purposes in mind, I would love to read them. If the structure is fine, and 7000-10,000 new rows a week for a single table this is perfectly manageable, I would like to know that too.

 

Thanks for any help!

 

[Barand]

Normalization rules apply regardless of the size of the DB

[TheFilmGod]

Yes, I do have an optimization tip. Use the engine Innodb instead of myisam. They do basically the same thing, but innodb locks data by rows while myisam by the whole table. This means when you update information another user trying to update that data can't b/c its locked. Locking it by row enables others to update the table simultaneously, - which is more efficient for you.

 

Hope that helps! 

[php_dave]

Im quite new to normalisation myself - the only thing I would suggest looking at your table sctructure is you are storing Player as a VARCHAR?

 

Im guessing there is a user table somwhere out there with a numeric id key? I would use that instead of a text player representation - if the table is going to get big then use reference data where ever possible!

[fenway]

Yes, I do have an optimization tip. Use the engine Innodb instead of myisam. They do basically the same thing, but innodb locks data by rows while myisam by the whole table. This means when you update information another user trying to update that data can't b/c its locked. Locking it by row enables others to update the table simultaneously, - which is more efficient for you.

 

Hope that helps! 

FYI, InnoDB is much slower that MyISAM for typical web applications.

[TheFilmGod]

Yes, I do have an optimization tip. Use the engine Innodb instead of myisam. They do basically the same thing, but innodb locks data by rows while myisam by the whole table. This means when you update information another user trying to update that data can't b/c its locked. Locking it by row enables others to update the table simultaneously, - which is more efficient for you.

 

Hope that helps! 

FYI, InnoDB is much slower that MyISAM for typical web applications.

 

Maybe so, but according to many articles on the net and to the executive senior programmer at digg.com say that innodb engine helps a lot in reducing bottlenecks and taking away slow db queries.