Dynamic tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
sn4k3
Forum Commoner
Posts: 37
Joined: Tue Oct 16, 2007 3:51 pm

Dynamic tables

Post 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
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Dynamic tables

Post 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
(#10850)
sn4k3
Forum Commoner
Posts: 37
Joined: Tue Oct 16, 2007 3:51 pm

Re: Dynamic tables

Post 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
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Dynamic tables

Post 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.
(#10850)
sn4k3
Forum Commoner
Posts: 37
Joined: Tue Oct 16, 2007 3:51 pm

Re: Dynamic tables

Post by sn4k3 »

about 25/30 fields each table, is it ok?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Dynamic tables

Post by Christopher »

That's fine, especially since they are mostly INT fields. It is the total records size that starts to make a difference.
(#10850)
sn4k3
Forum Commoner
Posts: 37
Joined: Tue Oct 16, 2007 3:51 pm

Re: Dynamic tables

Post 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
Post Reply