databases - How to choose how to store data?

Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime. - Chinese Proverb

I could ask what kind of data storage I should use for my actual project, but I want to learn to fish, so I don't need to ask for a fish each time I begin a new project.

So, until I used two methods to store data on my non-game project: XML files, and relational databases. I know that there is also other kind of database, of the NoSQL kind. However I wouldn't know if there is more choice available to me, or how to choose in the first place, aside arbitrary picking one.

So the question is the following: How should I choose the kind of data storage for a game project?

And I would be interested on the following criterion 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.


• Added Portability of data amongst many project.


• Added2 Silmutaneous use of the data amongst different project. (i.e. User data)


• Added How often should the data be accessed


• Added Multiple type of data for a same application


• Added2 Configuration with multiple data storage.



• Any other point you think is of interest when deciding what to use.

EDIT 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. Now if I am wrong, I would gladely be shown the error in my ways.

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

Answer

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.