# How to Structure my Database for a HTML turn-based strategy game?

I'm making simple HTML socket game that I want to record over time.

It's a "risk" type game where individual tiles can be conquered, and the team with the most tiles is winning.

As of right now, I've finished most of the game, but the problem is that every time the server restarts all the server data is lost.

I want to use the database to store all the different teams and also of all the tiles that have been conquered, but I am also thinking of storing all the tile conquerings with timestamps so I can see the progression of how the map was conquered. (so I can theoretically play it back in time and see how the map began to evolve).

I also want it so on the event that the server crashes, the server can retrieve data from the database and go back to a previous world state (with which tile has been conquered by whom, etc.)

I'm very new to databases, and have only learned SQL in school because I had to. I was considering using MongoDB (noSQL) for this, and seeing that I will only write at most every few seconds to the board, is this the right approach?

It really doesn't matter which database you use for this. You can use pretty much any.

The sql-by-the-book solution for storing the current state of all games would be to create a table with these fields:

• game_id (primary_key)
• tile_id (primary_key)
• owner

The game history would look like this:

• game_id (primary_key)
• move_id (primary_key)
• timestamp
• tile_id
• new_owner

Actually, the first table with the current state could be redundant, because you can construct the current gamestate by simply reconstructing the whole game from its history. But that might be quite performance-intense for such a common operation (you will want to suspend any games to the database where nobody made a move for a while in order to save memory on your gameserver).

But if retrieving the whole game is your primary use-case, then you might decide to deviate from SQL-by-the-book for the current state table and store the gamestate serialized to binary and store it as a BLOB:

• game_id (primary_key)
• gamestate

You then can no longer do queries for individual tiles, but saving and restoring the complete gamestate will be a much faster operation, because it's just a single row.

The question also mentioned MongoDB. How would this look there? MongoDB emphasizes nested documents over relations. Anything which has a relation should by default be stored in the same document (there are exceptions, see later). The default game document would look like this:

{
_id: ObjectId(),
player1: <player identifier>,
player2: <player identifier>,
tiles: [
// array representing the states of all tiles
],
history: [
{
timestamp: Date(),
tile_id: <index in the tile-array above>,
new_owner: <player identifier>
},
/*...and more such objects...*/
]
}


However, MongoDB has a problem: It doesn't like documents which grow, because when a document grows beyond its initial size it sometimes needs to be relocated to a different hard drive location. This harms write performance. So when you notice that updating the game history becomes a bottleneck, you might want to move it to a different collection where each game event is one document:

{
_id: ObjectId,
game: <ObjectId of the game where the event happened>
timestamp: Date(),
tile_id: <index in the tile-array above>,
new_owner: <player identifier>
}


Usually you can achieve the same thing by different approaches. It all depends on what you want to do and what you choose of: programming language, server, database, etc.

Anyway, if you're using websockets, consider using a real-time database, like RethinkDB (NoSQL) or Firebase Realtime Database.

AFAIK Mongodb doesn't support realtime, and I didn't use Firebase myself, but I have experience with RethinkDB and is definitely cutting-edge. Note: the syntax of RethinkDB is ugly (IMHO) and because it's relatively new in the market you might not find much of help in stackoverflow (you're on your own).

TL;DR:

IMHO, in your case, if you can avoid using any database, just don't use any. Because if the server crashes or some players got discounted, they will easily restart the game and play again.