Best way to store multi-dimensional array on a server?

Question

I'm developing a web-based, turn-based strategy game which takes place on a 10x10 board.

Each tile represents an area of land and has its own set of statistics (eg, income, population, fertility, natural resources).

Each time the client loads it will download all of the information for the game (ie, loading the stats of only one tile is not necessary). The board size is static, that is, there are always exactly 100 tiles.

What would be the best option for storing this data on the server? (ignoring keys and such)

• In a database: a table with (num of statistics per tile) fields and 100 records for each game.
• In a database: a table with one record for each game and 100*(num stats/tile) fields
• In a flat file.

Answer 1

I would create a table where each row represents a single tile in the game.

Then, I would have a column for X (from 1 to 10) and Y (from 1 to 10). Depending on your requirements then you could also store which game it is (if you want to keep multiple games at once) and which turn it is (if you want to keep a history of the games).

So you would have:

Tile: GameID
Turn int X int Y int Income Population Fertility etc

The primary key is the first 4 columns. GameID would probably end up being a foreign key to the Game table.

Answer 2

start with an entity-relationship diagram between entities. I can see a few from your brief description:

one board per game game has a timestamp showing when it was initiated board has many tiles - 10x10 now, might be arbitrary a tile has many statistics

there are others that you seem to leave off. turns imply more than one player. a game will have many players.

you might want to timestamp statistics and keep history of updates.

is this an object-oriented system? have you come up with an object model? I'd start with that first.

Answer 3

If you're not going to search or order the data on the boards or some such I'd say that a flat file would work. But your users might "want" to live in a database? Then the board data fits there too. Maybe just store the board in one varchar field in whatever format fits you best, XML or serialized or whatever.

Answer 4

Your question is incomplete.

A database is for (1) querying and (2) updating. You've mentioned neither the queries or updates to your data.

Storing data is not a particularly good use for a database. If you want to store data, you generally use simple files. Databases are regularly backed up to simple files for long-term storage, backup and restore.

If there are no queries and no updates, than a simple file is fine.

If, however, there are queries or updates, then a database might make sense. The specific structure would depend, of course, on nature of the queries or updateds.

Answer 5

This exact question was asked on the #django IRC channel a while ago, although the asker wanted a variable sized game board for different screens.

The overall recommendation was to go with a nice, clean, normalized method first and optimize to a denormalized state if performance issues are encountered. The main reason to go the normalization route first is that the database is built to handle lots of relationships fairly easily and can easily cache the results it gets if they don't change too often.

What I mean by a normalized setup is (pseudo code):

Table Board
{
    BigInt id;
    String name;
    Date date_played;
    etc...
}

Table Tile
{
    BigInt id;
    ForeignKey board;  // what board the tile belongs to
    ManyToMany items;  // items on this tile
    ManyToMany players;   // players on this tile
    etc...
}

Table Item
{
    BigInt id;
    String name;
}

Table Player
{
    BigInt id;
    String name;
}

// many-to-many link table
Table TileItem
{
    BigInt id;
    BigInt tile; // tile id
    BigInt item;  // item id
}

// do the same thing for players
Table TilePlayer
{
    BigInt id;
    BigInt tile;
    BigInt player;
}

You will be storing 100 records per board, but its worth it if you ask me. This makes finding all game board tiles easy. Writing to an individual tile can be done with just its ID, no difficult row interpretation or anything.

Answer 6

If a player's actions on a single "turn" can only affect a single cell (or small group of adjacent cells) then you'd want to store the data in a way that allows partial updating without having to rewrite the entire board to disk (and read the entire board) every time.

Given the small number of cells per game, I'd lean toward a database with a key of {game ID, x coordinate, y coordinate}.

Answer 7

Go with the database, one row per tile. Since each game turn probably doesn't modify a lot board tiles, it is best to separate them. Similarly, if you need to

Tiles (
    tile_id bigint autoincrement;
    game_id bigint; // foreign key to game table
    x tinyint;
    y tinyint;
    fertility type;
    // etc.
    primary key (tile_id)
    unique key (game_id, x, y)
);

Games (
   game_id bigint autoincrement;
   status enum(pendingplayers, started, finished, pendingdeleteorarchive)
   turn_number int;
   date_started date;
   date_ended date;
);

One of the nice things about having this stuff in the database is it saves you some effort when updating stuff. For example, if one of your land's characteristics changes each turn, you can just do an update of the database rather than looping through the tiles:

Update tiles set fertility = fertility * 0.99 where game_id = :x and land_type = 'forest';