[color=#000000][font=Arial,]I'm looking for a bit of guidance on designing an efficient turn based system using mysql. The requirements and game flow would be similar to the game "Words with Friends". But here are the requirements of the system off the top of my head:[/font]

• History of each battle must be stored so battle statistics can be calculated. At the moment this would simply be for determing win/loss count.
  
• The current state of battle must always be available (a user should be allowed to close the app and resume play by choice or by push notification).
  
• Must be efficient and have as minimal database requests as possible. Potentially there could be many requests per second with a larger client base so the less requests the better!
  
  [color=#000000][font=Arial,]The general game flow:[/font]
  
  
  • Player selects another player to battle and a turn-timeout value, then waits for confirmation.
    
  • After confirmation succeeds the initiating player must take their turn. Taking a turn simply means selecting an attack/move to do.
    
  • The damage dealt etc is calculated by the player taking their turn and propagated to the database. A push notification is then sent to notify other player it is their turn.
    
  • Process is repeated until a win condition is met (most likely when a particular players health is at a certain level or if their turn times out).
    
    [color=#000000][font=Arial,]Can anyone suggest ways to incorporate this efficiently into a database?[/font]

Some of your requirements are worrying.

• If the game is played online with alternating turns and a tight turn timeout, both individual turns and a whole game must be very short, and meant to be played in a single session. Then why do you need "push notifications" and support for closing the application without quitting and automatically losing?
  
• How can you trust the active player to calculate damage dealt and other authoritative information? At the very least, make all players compute the game state and verify that they agree exactly.
  

Games could be short or long it depends on the turn timeout set and how active the two players are. I could set a minimum on the timeout to enforce a chance of games running longer. The idea is that games won't be played and finished in one session, you could play one session for a day or more simply because people might take their time taking their turn - the timeout is there so that you don't get games locked up waiting forever for someone to make a move. This kind of gameplay obviously works well as games like Words with Friends is very successful.

Push notifications are required to simply tell the other player it is their turn without them having to go into the game and check the status. Seems obvious to me why that is required, and the game should persist upon application close/minimise because like I mentioned above, the games aren't necessarily short - you take your turn then go back to reading a book or whatever you're doing.

As for trusting the player, I was kind of under the impression it'd be pretty hard to hack an iPhone app. Maybe I'm wrong? In any case there could be some validation done on both sides. You must also have a user account which has authentication tokens to make web service requests so not anyone can just hack up requests.

Anyway, that isn't really the answer I was looking for. I'm looking for advice on how to design the database tables to handle this kind of gameplay. I think I have something worked out now but I'm still open to input if anyone has any.

Have you done schema design before? If not, I can give you a few pointers to get you started.

If you want to design a database schema, you need to start by thinking about the nouns involved in your game and how they relate to each other. So far, here is what I'm seeing:

• Players
  
• Games - each record would be related to two players
  
• Battles - each record would be related to a single game record and should also contain a sequence number (first battle for game 7, second battles for game 7, etc.) and would contain all of your stats (who won, what were the losses, etc.); if you want to track a lot of stats, you may want to break this out into a separate table
  
  Does that help?

The problem is that a few of your requirements are counter to each other. Either you can save all the moves and keep all the data at all times, or you can make a few database calls.

Next, it is not that hard to emulate the iPhone and apps, also hacking the iPhone is not that hard either. A significant number of iPhones and iPads are cracked. But the reality is that I don't need your app at all to play the game. All I need to do is send data to your server in a format that is expect. As long as I do that, then you have no idea if I am even using an iPhone.

j.

All I need to do is send data to your server in a format that is expect. As long as I do that, then you have no idea if I am even using an iPhone.

If you read what I actually said - that isn't possible because you need proper authentication tokens and a user account. Even if you could work out the correct format your data would be rejected.

Anyway this has gone way off topic to what I was actually wanting help with. I think I've found a solution on how to lay out the data in the database now so we can consider this closed.

Cheers.

If you read what I actually said - that isn't possible because you need proper authentication tokens and a user account. Even if you could work out the correct format your data would be rejected.

But cheaters would have a user account with proper authentication tokens. They would use an hacked client with the valid credentials of their valid account.