How to choose how to store data?

Question

Until recently, I used two methods to store data on my non-game project: XML files, and relational databases. I know that there are other kinds of database as well: the NoSQL kind. However I don't know if there are more choices available to me, or how to choose in the first place, aside from arbitrarily picking one.

How should I choose the kind of data storage to use for a game project?

I am interested in the following criteria when choosing:

• The size of the project.
• The platform targeted by the game, as well as the development platform used.
• The complexity of the data structure.
• Portability of data amongst many project.
• Simultaneous use of the data amongst different project. (i.e. User data)
• How often should the data be accessed
• Multiple type of data for a same application
• Configuration with multiple data storage.
• Any other point you think is of interest when deciding what to use.

I know about Would it be better to use XML/JSON/Text or a database to store game content?, but thought it didn't address exactly my point.

Furthermore, I would be interested to hear what other options are available, aside from flat file storage and relational databases. What about non-relational databases, for example? When is it relevant to use such a database over the formerly mentioned other options?

Answer 1

Your question is really broad because of the sheer number of genres out there, but here's the perspective of a professional software developer.

You provided a list of criteria that you want to use to determine which data persistence mechanism you use. Those were:

• The size of the project.
• The platform targeted by the game.
• The complexity of the data structure.
• Portability of data amongst many project.
• How often should the data be accessed
• Multiple type of data for a same application
• Any other point you think is of interest when deciding what to use.

First we need to establish which options are available to us. Since you didn't specify a language or technology, it's hard to say exactly, but you are probably trying to decide between XML and relational database storage.

An important distinction to make is that XML isn't really a storage mechanism so much as a serialization technique. It's a way to represent in-memory structures for your game. Given that, you're really talking about flat file vs. relational database storage. These aren't the only options, but they're the most common, so I'm going to use them.

For flat files:

• XML
• JSON
• YAML
• XAML
• Plain Text

For databases:

• SQL Server
• MySQL
• DB2
• Oracle
• Many More

EDIT: You asked about other types of mechanisms aside from files and relational databases. The only other notable type of database that I have seen used on a regular basis are "Berkeley-style" databases, which are essentially key-value based. These tend to use B-trees to structure data so lookups are fast. These are great for configuration/setting lookup where you know exactly what you want (e.g., give me all the telemetry data for "Level 1").

Now that we have all the basics out of the way, let's touch on some of your criteria.

The size of the project.

Some might disagree, but the size of your project won't necessarily have a huge impact on your data persistence mechanism. You will want to build a reusable library of functions that store/load data from whichever mechanism you want. I would even suggest implementing an abstraction layer (check out the Adapter pattern) so that you could easily change your persistence mechanism if you needed to.

Having said that, for small projects, using XML on the file system can potentially work well, but you will want to address some of your security concerns (i.e., encryption) so that players can't change data at will.

The platform targeted by the game.

Platform isn't going to be a huge issue either. You should be more concerned about your development platform than the target platform. The reason for this is that some languages handle certain types of markup or databases better than others out of the box. That isn't to say that you couldn't use any of the above in almost any language, but sometimes it's best to use the supported tools that are available to you. Any platform will support flat files and parse XML, but on mobile platforms you might want to consider binary serialization if possible, or at least optimize your XML for storage.

The complexity of the data structure.

This is sort of a tricky one. Relational databases are great for just that...storing entities and their relationships. You have a better ability to enforce structure using a relational storage repository than you do with files on a file system. Consider the types of relationships between your entities as well as how often you're changing them or finding related entities. For extremely complex structures, I would suggest going the database route.

Portability of data amongst many project.

When it comes to portability, you should consider the fact that databases are naturally more heavyweight than files. There's installation and configuration overhead, different databases will be available for different platforms, etc. SQLite is a pretty good way around this. However, when it comes to portability, you will likely have an easier time with file-based solutions like XML.

EDIT: There are some other concerns you mentioned about portability in one of your comments. Ultimately you don't want your data to be coupled too tightly to any product or file type. It's ultimately best if you can store stock data (levels, enemies, etc.) in some kind of abstract format (tab delimited files, XML, etc.) that you can easily parse and store in a database/file system at compile or load time. This means that you can swap out your storage mechanism on a whim and just rewrite the parsing piece.

How often should the data be accessed

Lots of data access means lots of I/O unless you have some kind of a caching mechanism. Databases keep structures in memory and are great for data manipulation and retrieval. If you're really persisting data constantly, you might want to stick with a database.

Multiple type of data for a same application

Volume is certainly a consideration, but unless you're talking about persisting thousands or millions of objects, file system will still be acceptable as a solution.

Game Type

The type of game you're building can have a huge influence on the platform you choose. Yeah, for most client-only single-player games, you're going to be fine using a compressed or encrypted file system-based solution. If you're talking about games with an online component, though, that would be crazy. Go the database route and save yourself the headache. Let the server manage all of your data using a back-end cluster.

Hope some of this feedback helps. It's by no means completely comprehensive, and making the decision is ultimately up to you, but my commentary should give you some things to think about.

EDIT: There are some times where taking a hybdrid approach makes a lot of sense. For example, let's say you are developing an MMORPG. On the client side, you might store cached data about other players in a non-relational database (as mentioned above). On the server side, you're storing all game data in a relational database to persist it. And then again on the client side you're probably storing log data, configuration data, etc. in XML/flat files for easier accessibility.

Another poster also mentioned that sometimes it's nice, even if you're storing data for production in a database, to have a way that you can use flat files for development instead...it can just be easier to remove another product from the mix.

Answer 2

Lately I've found myself constrained by the rigidness/difficulty of adding to relational databases. I'd love to explore document-based databases (e.g. couchdb), as they seem very suitable for games, state, and flexibility. But it's not really practical to set up multiple database types for a small project. As a result, I've set up a hack similar to just using a binary blob field in certain fields of the database.

What I'm doing is using json encoded data in the database and a wrapper that converts the data into json and back out whenever it's needed. So a basic character profile might look like this in the database:

{"char_id":24,"char_name":"tchalvak","level":43,"profile":"Some profile here."}

Json is still nicely readable in the database so if you're working bare to the metal with the sql and sometimes access the database directly as well, it's manually modifiable if you wish to.

Now, all of that is a hack, and I don't advise copying it, but here's the overall point: Don't rely only on one system. Use a relational database and fudge the system. Or use two different types of data storage systems (e.g. flat file and relational database?), so that you can get the most benefit from both. No matter what people suggest, or what one system you go with, down the line you're going to find circumstances where another approach would work better, so be prepared to add an alternative and see where it takes you.

Answer 3

Why use one method of data storage?

In most cases, the game you release to the world does not need the same data storage functionality that the game needs during development.

Here's a comparison of some release / development requirements:-

Requirement      |  Release  |  Development  |  QA
==================================================
Multi user       |    No     |      Yes      |  No
Writable         |    No     |      Yes      |  No
Revision Control |    No     |      Yes      | Yes (but only reading)
Compact          |   Yes     |       No      |  No
Fast             |   Yes     |       No      |  No

Ideally then, you would define an interface to your data loading module and then create multiple versions, each tailored to specific needs.

On one project I worked on many years back, loading level data off CD was taking far too long (off HD wasn't great either). So I came up with a the following. I had three methods for loading level data:-

1. Normal loading - for development time when assets were changing
2. Pre-release - effectively a method to convert normal data into fast loading data
3. Release - fast data only (not editable)

This reduced loading times from minutes to seconds.

Answer 4

In the RTS gene, game data has been stored in files.

The units and attributes have been stored in INI or XML files or other text-based formats, and the models have been a mix of binary or even text-based formats, and the textures and sounds and other media in mainstream formats. Sometimes it has been in a container that has crude encryption - Westwood's mix format comes to mind. But this is mostly obfuscation - if you look inside them, they are just normal files in easy to handle formats.

With the advent of online play it has become increasingly common for the game data to be provisioned post-installation over the internet, although often this is still stored on the filesystem.

Answer 5

An approach I recently used for a hobby project RPG is the following ...

Data for entities I store in CSV files, which can be thought of as a simple textual database. For my use-case I use | as a seperator.

I've added a shell script that converts each entry in the CVS file into a Lua file, as the game is written in Lua with LÖVE.

Since all generated files for entities are in Lua, these files can be loaded directly into the game.

One might wonder: why not just add Lua files directly for each entity? Well, it's more error prone to ensure all keys and values are entered correctly. Especially when each entity is defined in its own file. The tabular format of CSV makes it easy to check if no field is missing, the correct value is used in each column (when comparing to other columns), etcetera.

One might also wonder: why not just use a real database like SQLite? Personally, I just find it easier to modify tables directly in a text editor instead of requiring special tools.

My creature.csv looks as such:

ID$|NAME$|HD$|AC#|WEAP@|SPEED#|SIGHT#|CR#|SPRITE$|SOUND_HIT$|
skel_warr1|Skeleton Warrior|1d12|15|scimitar1,claw1|30|60|1/3|skeletal_warrior_new.png|growl6.wav|
goblin1|Goblin|1d8+1|15|morningstar1,javelin1|30|60|1/3|goblin_new.png|paind.wav|
wolf1|Wolf|2d8+4|15|bite1|50|60|1|wolf.png|monster-8.wav|
ogre1|Ogre|4d8+11|16|greatclub1,javelin1|30|60|3|ogre_new.png|ogre4.wav|

NOTE 1: The weapon types are IDs based on another weap.csv file. Again, in a way this makes these CSV text files a database as these IDs can be thought of as foreign keys.

NOTE 2: I've added type annotations in the header field, which are used by the shell script to generate values of the correct type. I've added type annotations for string ($), number (#) and array (@). If no type annotation is defined, will assume string.

NOTE 3: Instead of editing text files directly, one could also use a tool like Excel. This might be useful when dealing with non-programmers. Likely for most people also easier than using an actual database.

The generator shell script is defined as such:

#!/bin/bash

# the script directory
DIR="$(dirname "$0")"

TARGET_DIR="${DIR}/gen"

# arrays to manage CSV column names & related type info
COL_NAMES=()
TYPE_INFO=()

# set Internal Field Seperator to: |
OIFS=${IFS}
IFS="|"

isnumber() {
    awk -v a="$1" 'BEGIN {print (a == a + 0)}';
}

function generateEntity() {
    declare -a FIELDS=(${1})

    local CSV_PATH="${2}"
    local ID=${FIELDS[0]}
    local TYPE=$(basename ${CSV_PATH/\.csv/})
    local TYPE_DIR="${TARGET_DIR}/${TYPE}"
    local FILE_PATH="${TYPE_DIR}/${ID}.lua"

    # constants for newline and tab - these are escaped for use with awk script
    local nl="$(printf '\nq')"
    local tab="$(printf '\tq')"

    # notify on the entify file we're generating
    echo "${tab%q}${FILE_PATH}"

    # create an entity type directory if none exists already
    mkdir -p "${TYPE_DIR}"

    # generate LUA string for entity file
    S="return {${nl%q}"

    # get field count from array
    local N_FIELDS=${#FIELDS[@]}
    for (( i = 0; i < $N_FIELDS; i++ )); do
        local COL_NAME=${COL_NAMES[i]}
        local TYPE=${TYPE_INFO[i]}
        local VAL=${FIELDS[i]}

        # based on type, add value as number, array or string
        if [[ $TYPE == number ]]; then
            # assign default value of 0 if not defined          
            if [[ -z ${VAL} ]]; then VAL="0"; fi

            # append to Lua string
            S+="${tab%q}${COL_NAME} = ${VAL},${nl%q}"
        elif [[ $TYPE == array ]]; then
            # variable to store result
            local OUT=""

            # temporary use comma seperator for fields
            IFS=","; 

            # loop through each item in fields
            declare -a VALS=(${FIELDS[i]})
            for VAL in "${VALS[@]}"; do 
                if [[ `isnumber ${VAL}` == "1" ]]; then
                    OUT+="${VAL}, "
                else
                    OUT+="'${VAL}', "
                fi 
            done

            # trim comma-space (, ) at the end
            OUT="${OUT%%, }"

            # restore pipe as field separator in order to parse columns properly
            IFS="|"

            # append to Lua string
            S+="${tab%q}${COL_NAME} = { ${OUT} },${nl%q}"
        else
            # append to Lua string
            S+="${tab%q}${COL_NAME} = '${VAL}',${nl%q}"
        fi
    done    

    S+="}"

    # write the LUA entity string to the entity file
    echo "${S}" > "${FILE_PATH}"
}

function configureColumns() {
    local CSV_FILE="${1}"

    # extract the header rows
    HEADER=$(head -1 $CSV_FILE)
    
    COL_NAMES=()
    TYPE_INFO=()

    # convert column names to lower case and store in array
    for COL in $HEADER
    do
        # parse header type annotations: 
        # - #: number
        # - @: array of strings
        # - $: string
        # default is string if no annotation provided
        if [[ $COL == *\$ ]] ; then
            TYPE_INFO+=(string)
            COL=${COL%?} # remove last character
        elif [[ $COL == *\# ]] ; then
            TYPE_INFO+=(number)
            COL=${COL%?} # remove last character
        elif [[ $COL == *\@ ]] ; then
            TYPE_INFO+=(array)
            COL=${COL%?} # remove last character
        else
            TYPE_INFO+=(string)
        fi

        # store the column names in an array
        COL_NAMES+=(`echo $COL | awk '{ print tolower($1) }'`)
    done
}

function parseCSV() {
    local FILE_PATH="${1}"

    # notify on the CSV file we're processing
    echo "${FILE_PATH}"

    # add newline at end of file in order to parse CSV properly
    [[ -n "$(tail -c1 ${FILE_PATH})" ]] && echo >> "${FILE_PATH}"

    {
        # ignore the header line
        read

        # parse remaining lines
        while read -r LINE; do
            # skip empty lines
            [[ -z "${LINE}" ]] && continue

            generateEntity "${LINE}" "${FILE_PATH}"
        done
    } < "${FILE_PATH}"
}

function generateEntities() {
    # clear target directory if exists
    if [[ -d ${TARGET_DIR} ]]; then rm -Rf ${TARGET_DIR}; fi

    # parse CSV files
    for FILE_PATH in "${DIR}"/*.csv; do
        configureColumns "$FILE_PATH"
        parseCSV "$FILE_PATH"
    done
}

generateEntities

# restore internal field seperator
IFS=$OIFS

The shell script will look for any CSV files in same directory as the script file. For each CSV file it will generate a subdirectory inside an entities/ directory. For example, for a creature.csv file an entities/creature/ directory will be generated and for each creature a Lua file is generated inside the directory based on its ID value.

The first entry of creature.csv results in the following entities/creature/skel_warr1.lua file after being parsed by the generator script:

return {
    id = 'skel_warr1',
    name = 'Skeleton Warrior',
    hd = "1d12",
    ac = 15,
    weap = { 'scimitar1','claw1' },
    speed = 30,
    sight = 60,
    cr = 1/3,
    sprite = 'skeletal_warrior_new.png',
    sound_hit = 'growl6.wav',
}

FINAL NOTE: I don't use these generated entities directly in the game. Inside the game I still treat these files as data files. I make use of an entity factory to parse values from Lua data files to generate Entity classes.

The Entity class itself is based on ECS principles. For each Entity components can be added based on type. Some examples:

• Creature types will have a CPU control component added so the computer can handle movement.
• Player types will have a keyboard control component added, so the player will respond to keyboard input.
• Door types will have an interactable component added, so players and creatures can interact with the door to open or close it. And so on and so forth ...