Jump to content
GriefCo.de
Sign in to follow this  
  • entries
    2
  • comments
    0
  • views
    80

Better & more persistent data storage

Sign in to follow this  
GriefCode

60 views

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

Sign in to follow this  


0 Comments


Recommended Comments

There are no comments to display.

Guest
You are commenting as a guest. If you have an account, please sign in.
Add a comment...

×   You have pasted content with formatting.   Remove formatting

  Only 75 emoticons maximum are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×