Page 1 of 1

Dynamic tables

Posted: Sat Oct 15, 2011 9:50 pm
by sn4k3
Hi,

i have multiple games which are added by an website admin
each game have many gameypes.

for each gametype stats will differ, i need to keep all stats from each game and each gametype

current i have a different table for each game with specific stats:
CREATE TABLE `stats_css` (
`id` int(10) unsigned NOT NULL,
`reliability` tinyint(3) unsigned NOT NULL DEFAULT '100',
`ragequit` tinyint(3) unsigned NOT NULL DEFAULT '0',
`played` int(10) unsigned NOT NULL DEFAULT '0',
`won` int(10) unsigned NOT NULL DEFAULT '0',
`drawn` int(10) unsigned NOT NULL DEFAULT '0',
`lost` int(10) unsigned NOT NULL DEFAULT '0',
`assists` int(10) unsigned NOT NULL DEFAULT '0',
`kills` int(10) unsigned NOT NULL DEFAULT '0',
`deaths` int(10) unsigned NOT NULL DEFAULT '0',
`roundswon` int(10) unsigned NOT NULL DEFAULT '0',
`roundsdrawn` int(10) unsigned NOT NULL DEFAULT '0',
`roundslost` int(10) unsigned NOT NULL DEFAULT '0',
`headshots` int(10) unsigned NOT NULL DEFAULT '0',
`damagegiven` int(10) unsigned NOT NULL DEFAULT '0',
`damagerecived` int(10) unsigned NOT NULL DEFAULT '0',
`bombsdefused` int(10) unsigned NOT NULL DEFAULT '0',
`bombsplanted` int(10) unsigned NOT NULL DEFAULT '0',
`1man_played` int(10) unsigned NOT NULL DEFAULT '0',
`1man_won` int(10) unsigned NOT NULL DEFAULT '0',
`2man_played` int(10) unsigned NOT NULL DEFAULT '0',
`2man_won` int(10) unsigned NOT NULL DEFAULT '0',
`3man_played` int(10) unsigned NOT NULL DEFAULT '0',
`3man_won` int(10) unsigned NOT NULL DEFAULT '0',
`4man_played` int(10) unsigned NOT NULL DEFAULT '0',
`4man_won` int(10) unsigned NOT NULL DEFAULT '0',
`5man_played` int(10) unsigned NOT NULL DEFAULT '0',
`5man_won` int(10) unsigned NOT NULL DEFAULT '0',
`maps` text COLLATE utf8_unicode_ci,
PRIMARY KEY (`id`),
CONSTRAINT `stats_css_ibfk_1` FOREIGN KEY (`id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
that was the first design but it only cover one game with one gametype
others gametype can have different stats fields

like gametype Standard and Capture the flag for example
where capture the flag will keep flags caputured but standard dont

so in conclusion what i need is:
different Games have different stats fields
different gametypes for a game have more different stats fields

what will be best way to get this working for multiple gametypes?

Thanks

Re: Dynamic tables

Posted: Sun Oct 16, 2011 2:13 am
by Christopher
Do you need to search on any of this game data? Or just load/save it for each game session? If you don't need to search on it then you could store the game data as serialized or JSON data stored in a single TEXT field:

Code: Select all

CREATE TABLE `game_stats` (
`id` int(10) unsigned NOT NULL,
`user_id` int(10) unsigned NOT NULL,
`game_id` int(10) unsigned NOT NULL,
`data` text,
) ENGINE=InnoDB

Re: Dynamic tables

Posted: Sun Oct 16, 2011 8:58 am
by sn4k3
Thats the problem since there are public rank

serialized array would fit, but i need search for data

i only see a way to do this, but i dont like it:

add one table per gametype, stats_css_standard, stats_css_freeforall, stats_css_oneonone for exemple

but in a future it can create about 5*3 = 15 aditional tables
also have very tables in a database is bad?

Thanks

Re: Dynamic tables

Posted: Sun Oct 16, 2011 12:23 pm
by Christopher
sn4k3 wrote:but in a future it can create about 5*3 = 15 aditional tables
also have very tables in a database is bad?
More, smaller tables is not a bad thing. The tables will have fewer records so will perform better.

Re: Dynamic tables

Posted: Sun Oct 16, 2011 12:47 pm
by sn4k3
about 25/30 fields each table, is it ok?

Re: Dynamic tables

Posted: Sun Oct 16, 2011 9:45 pm
by Christopher
That's fine, especially since they are mostly INT fields. It is the total records size that starts to make a difference.

Re: Dynamic tables

Posted: Sun Oct 16, 2011 10:50 pm
by sn4k3
true, thanks for your help, very usefull :)

also can you take a look at other my post, about mysql and cache?

viewtopic.php?f=1&t=132169&p=662581