Sign in to follow this  
WireAlbatross

Gathering Game Statistics for a Data Base

Recommended Posts

WireAlbatross    134
Hello All, I'm working on a multi-player flash game that uses SmartFoxServer. The game matches up two players to battle each other. There is a map which can be thought of as 1 dimensional since it is a side scrolling game. I'd like to record specific events that occur and associate them with a region on the map and a slice of time and store this data in a database. I came up with something like this: EventTable [MapID] - the map [TimeRegion] - an index into a set of time divisions (i.e. 0=<30sec, 1=<60 sec, etc.) [MapRegion] - the map chunk index (each chunk is say 200 pixels wide) starting at the left side [EventNumber] - what event [Count] - number of times the event occurred in this time and map region. I've done a little DB development, but I'm really struggling with a design approach to solving the problem. In the worst case, each game just does an UPDATE each time an event occurs (obviously this is unacceptable). For performance, I need a to cache these events some how and update in batches so updating Count is not by just 1 each time. I'm looking for ideas about going about this. Right now I'm considering creating a matrix H where H(i, j) represents the event i's count for the current time region and map region j. When the time region changes H is sent to the db and reset to zero (i * j UPDATE calls per time region per game). Is this still way too much DB traffic? There may be potentially hundreds of games simulated. Any ideas? Thanks.

Share this post


Link to post
Share on other sites
Spodi    642
I guess it depends on how you want to use your information. Going one event per row could result in a lot if theres a ton of events. You could group up events on a per-match basis (assuming they're shorter matches), then each match takes just one row. Of course this wouldn't work well for just using SELECT to grab all events of a certain ID or time frame, but you could easily write your own custom parser for this. I just could see having a new row per event being a bit overkill since you will have a LOT of repeated data. This would also allow you to keep more information on the match itself without repeating it, such as the MapID, the user's name or IP, their scores, etc.

I think the most important thing before deciding on how you structure your database is what do you plan on using it for? Debugging purposes only? Frequent, real-time SELECTs? If its for debugging, you'll want to keep it compact so its more transparent. If its going to be used to display data back to the user, you might want to structure it to for optimal read performance... though not sure why a user would need to be able to see their or other user's events. ;)

Share this post


Link to post
Share on other sites
WireAlbatross    134
Thanks for your comments.

The game is simple. You control a dinosaur that can have weapons strapped to its back. You start off with 3 villages and your opponent starts with 3. They are arranged like so: C B A 1 2 3, where your villages are C B A when your enemies are 1 2 3 or the other way around. You can equip new weapons when standing at your villages. Villages A and 1 are considered tier 1, B and 2 are tier 2, and C and 3 are tier 3. Each tier gives you better weapons.

The data will be used to analyze game strategies to aid us in improving the balance of the game. For instance, many players might start with the cannon (a tier 1 weapon) and rush to tier 2, and they may ignore the other weapons completely. We would like to balance the numbers since there are 3 weapons per tier. On the other hand players may fall back right away to a better tiered village and equip a better weapon.

Basically we would like to present this information in graph form doing queries like so:

SELECT Count FROM EventTable WHERE MapID=0 MapRegion=5 TimeRegion=0 EventNumber=3

This, say, would give us the number of Dino deaths (event number 3) in the middle of the map (region 5) during the first 30 seconds (time region 0) of the match. There is a small set of events representing village captures, Dino deaths, and the 9 different weapons firing (per player).

This data is not per game its per map. Maps are basically heights along a terrain and village locations. Different setups might be more or less difficult or call for different strategies. Thats what we are looking to find out.

Thanks.

Share this post


Link to post
Share on other sites
Kylotan    9860
What is the actual question? It's impossible to say whether something is too much if we don't know how how often the somethings are going to occur. Produce some estimates with concrete values and we can give a meaningful answer.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this