Blogs

Our community blogs

  1. Hey,

    I just thought I should share some information I recently made on storing the game data, especially for DotA games in a better way to have it:

    1. Better accessible, not across 4 different tables. & having at least 2 queries
    2. Faster
    3. Lower disk usages for database servers
    4. & maybe some more

    So let's start up on getting into details.
    The current situation is that our data is stored across 6 tables:

    • dotaplayers
    • gameplayer
    • games
    • dotagames
    • gameevents
    • gamechatevents

    This is all currently merged and selected when we want to get the data of one game. The first step would be to merge 4 major tables:

    • dotaplayers
    • gameplayer
    • games
    • dotagames

    The solution is pretty simple. Since MySQL 5.7 JSON is supported which is perfect for this solution, we can just put each player or all players into one column and store them there. dotagames & games can be easily merged and would not be a big deal.

    A player database set could look like this for now:

    {
    	"2k":3,
    	"3k":0,
    	"4k":0,
    	"5k":0,
    	"gain":23.915174363808,
    	"gold":1924,
    	"hero":"EC77",
    	"name":"eliteshadow",
    	"items":[
    		"I0P1",
    		"I08O",
    		"I05Y",
    		"I09H",
    		"I095",
    		"I0AI"
    	],
    	"kills":15,
    	"level":18,
    	"realm":"europe.battle.net",
    	"colour":1,
    	"deaths":0,
    	"assists":6,
    	"firstdead":0,
    	"leavetime":1713,
    	"creepkills":90,
    	"firstblood":0,
    	"leftreason":"has left the game voluntarily",
    	"towerkills":2,
    	"creepdenies":3,
    	"neutralkills":1
    }

    While we got rid of some columns that are not required anymore, we also have a whole player with all information, which is easy to extend, adding new information to a player would no longer require to change the database structure. 

    The entries in the old tables can be removed after the transformation.

    So far, I found only one issue across the system.
    Players would be no longer trackable, especially by ip, as I have found a solution for that already, I will create a new blog entry for that to explain details and database structures.

    The whole system will be fully implemented with IPS-Stats 1.2.

     

    Greetings,
    Grief