Workflow for authoring Pokémon-like game database content

Question

I'm programming on a game that relies on a database for a lot of the creatures and items in the game (think Pokémon).

Currently, we have a Google Sheet that the designers are making. They will be using it to populate all of the items necessary for the game, so it acts as a 'source of truth'. However, to translate this into the database (SQLite) we'll need to write some code to pull the data, format it and put it into the database. The maintenance of this will also be pretty annoying, as we already need to maintain the database schema and the code representation (Unity C#). DB tables are can be static or dynamic, and the implementation on the Unity side for the DB is not an issue.

Before writing this code I'm exploring other options. I've looked into Microsoft Access (and similar programs) but they seem to be geared towards making applications now and don't have much in the way of SQLite integration. I'm leaning towards using a visualizer tool like DB Browser for SQLite or SQLiteStudio, but the lack of cloud syncing might mean a janky solution with Google Drive or AWS.

I'm looking for a workflow that:

1. will be accessible for game designers (who have little to no SQL experience, but willing to learn)
2. will keep changes between designers synced
3. will be easy to manage and maintain for the codebase and SQLite database.

How can we solve this problem?

Answer 1

I would generally not use a SQL database (not even SQLite) for handling static data in a singleplayer game. The only exception would be if I had so much data that it won't all fit into RAM. But when the data is less than a couple hundred MB, I would look for a solution which loads all the data when the game starts and then allow the game to access it directly.

Solution within Unity: Scriptable Objects

A solution completely within Unity would be to work with scriptable objects. A scriptable object is a game-specific asset type. So you could create a class CreatureType : ScriptableObject. This enables you to create "CreatureType" assets in the Unity editor which you can then manage in the Unity project outline like any other Unity asset.

You can then assign these assets to inspector properties of MonoBehaviour's and thus assign them to prefabs or objects within your scene. You can also have ScriptableObjects referencing other ScriptableObjects. So you can, for example, have another ScriptableObject type for the various attacks your creatures can perform and then assign attacks to creature types via drag&drop.

When the standard controls for public fields are insufficient for your purpose, you can use Editor Scripting to enhance the inspector of these assets to better fulfill your game designers needs.

Solution with files

However, scriptable objects have their limitations when it comes to editor support. While the Unity editor is very extensible, it can take a lot of development resources to essentially reinvent what you can already do in a spreadsheet application.

So another solution is to keep the data in a file format editable with a spreadsheet application, import that file into a Unity project as a Text Asset and then load and parse that file at game start.

Perhaps the most developer-friendly format for that is CSV, because:

• It can be handled by most spreadsheet applications, but can also be edited with a simple text editor when in a pinch.
• It is very easy to write a parser for it.
• The raw files are pretty human-readable and use one text-line per data entry, which means it can be handled well by text-based version control systems like Git.

But depending on the needs of your designers you might need to compromise and use a more advanced spreadsheet file format.

Answer 2

Multi-user concurrent transaction support will be required if you have multiple designers who are not actively deciding who will work on the data at such-and-such a time. Without this, data can be corrupted. Implementing such an advanced feature yourself, would consist of far more effort than it would be worth, when you are only trying to write a game. Large / proprietary DBs do support this transaction model, but you'd still be stuck with designers using a raw DBMS to do their work.

On the other hand, Git is very good at synchronising the work of multiple users commiting at any time they like, if your designers are willing to work in pure text files. This provides a simple solution and your designers will quickly enough learn to work with CSV. Besides, you will not have to pay for expensive services or worry about (any single revision of) your data ever being lost.

CSV (text), import CSV into Excel / Calc, then export to CSV before they commit/push to the git repository. This will also tie in easily with your development pipeline, since devs these days use Git for source control of code. Indeed - you could be using the very same source repository for everyone - major win.

JSON (text) is another solution if your data is inherently more hierarchical in nature, as opposed to flat. This eliminates the traditional DB concept of joins, allowing for an easier-to-follow setup if designers are willing to use JSON editors, instead (there are some nice ones out there). This also is text, and so can also be pushed to Git and kept safe at every single revision.