Quick note on the database engine

I met up with Jeff Nimeroff briefly because I thought I was about ready to set up the actual database tables and start using those in place of the test tables with which I'd been working.  I'd forgotten which engine he had suggested to use, and why, from a previous discussion.

Though there are a number of engines that MySQL supports, Jeff explained that the two main engines used are MyISAM and InnoDB.  The key difference between them is that MyISAM locks entire tables during queries, whereas InnoDB only locks at the level of a single row.  The functions of my game require running various polling functions that query the database so that players check for and receive updates on each others' actions.  Therefore it probably does not make sense to lock entire tables when, for example, up to 4 client instances of the game are making calls to the same table at once.  Thus he suggested I go with the InnoDB engine.

However, because this is a turn-based game, for the most part only one client can be making an update or insertion query at a time.  Therefore it's not totally critical to avoid table-locking.

Finally, he informed me that I can switch the database engine at any time--I'd thought it was set in stone, which is why I'd wanted to check in with him about which to use prior to creating the "real" database tables.

Meeting with Dr. Nimeroff: Database structure and linking multiple players

I met with my adviser, Dr. Nimeroff, for a bit the other day and we mainly discussed two related points.  Since it's been awhile since I've tried to design a database, we first talked over my ER diagram.  He didn't see any major potential issues with it right off the bat, so that was a good start.  We focused on the 'player_group' relation between the 'games' and 'users' entities.  I had not been sure whether it would be necessary to store player state information, like the x- and y-position, for a running game--in my newness to Flash/Actionscript, I'd been thinking something like that could be stored in Actionscript variables.  Dr. Nimeroff explained, though, that the Flash swf and associated Actionscript are run locally on each user's browser, so when values and visual elements change for one player, they aren't automatically updated for other players.  The key here is that whatever information is needed to update players' states on each others' "copies" of the swf and of variable values in the code must be saved into the database (since that, of course, is stored on the server) and then retrieved by calls from the other players' browsers to receive the new state.  Thus, it was a good thing I included such state information in my ER design.

Along these lines, I then asked about an issue that has been a "hole" in my mental plan of implementation since the beginning: how do other players' local machines find out that an update has been made to the database and take appropriate action to update their own copy of the game state in the code and swf?  I do want the game to be real-time, though it is turn-based, which makes it far easier than having to support multiple players doing things at the same time.  The only thing I could think of was polling; the Actionscript would just ping the database every fixed interval of time to look for a change and pull the new info if necessary.  I had also wondered whether the reverse idea would be feasible: could the database notify the game instances when an update was ready?  Dr. Nimeroff said that the former (games-poll-database) method was definitely the way to go about it.  Since the game is turn-based, it's not creating a constant stream of updates that need to be passed around; thus, a polling request every couple of seconds or so would not be a strain on the server.  Plus, the amount of data to be transferred per request is very little.  Finally, this is much simpler than the database trying to keep track of which instance to notify, where to send the notification, and the like.  This conclusion was very exciting for me, as I am no longer in the dark about a doable and sensible approach the implementation of multiplayer support.  And I think I might even know at a high level how to go about it.  Yay!