6
\$\begingroup\$

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?

\$\endgroup\$
  • \$\begingroup\$ why start with sql instead of a bunch of flat arrays? \$\endgroup\$ – ratchet freak Oct 26 at 9:19
  • \$\begingroup\$ Welcome. Wait for better answer, but as @ratchetfreak suggested, crude arrays on memory are valid, and I'm sure most released games followed that design. Also dictionaries, depending on programming language. It's what I would do if cloning Pokemon. Now, query a database on disk, handled by a reliable library/controller like SQLite, is not hard to do either. You can use something like sqliteman to populate the database, and SQLite lib to access it in-game. \$\endgroup\$ – Hatoru Hansou Oct 26 at 9:50
  • \$\begingroup\$ It seems that the kind of database you are making is "static"; Something that will not change as the game runs. If that's true, then you do not really a database management system at all (SQLite, mySQL, etc.). Although SQL is great for querying records, do you have that many creatures that a custom search algorithm is too difficult? Do you have any reason to modify the data at runtime? Can it be packaged in something easier to de-serialize from, like XML or JSON? If those formats can work for you, your options for software to author the data open up greatly. Google sheets can even export XML \$\endgroup\$ – Romen Oct 26 at 13:59
  • \$\begingroup\$ Are you already completely sold to the idea to use SQLite? I would generally not use a database for developing a singleplayer game, unless I have so much data that it won't fit into RAM. When the data is less than a couple hundred MB, I would just load it from files into appropriate data-structures when the game loads and then work with those data structures in RAM. \$\endgroup\$ – Philipp Oct 26 at 14:39
  • \$\begingroup\$ Thanks for your comments! We're not making a Pokémon clone, it was just a good reference point for creatures/items. I should have mentioned that there is static data and dynamic data (let's say levelling up a piece of armour). We're not married to the idea of SQLite but we're targeting mobile platforms so we want to save RAM for stuff in the game world. We have an asset for communicating with an SQLite DB, and will be caching stuff anyway. \$\endgroup\$ – maxresdefault Oct 26 at 23:09
5
\$\begingroup\$

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.

| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ How would your advice change if the DB was also using dynamic data? Thanks for your answer, I didn't consider Scriptable Objects before and CSV might be the way to go. \$\endgroup\$ – maxresdefault Oct 26 at 23:18
  • 2
    \$\begingroup\$ @maxresdefault My advise would not change much. The only niches in game development I could think of where databases are really invaluable are online account management, persistence in massive multiplayer and analytics. Whenever your data fits into RAM, databases are usually more trouble than they are worth. And when it doesn't, then you usually also have very unusual performance requirements, so you will likely have to develop an unique solution tailored to your problem domain and benefit little from an off-the-shelf database solution. \$\endgroup\$ – Philipp Oct 27 at 11:03
4
\$\begingroup\$

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.

| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ I've used a multi-user transaction database with MySQL before and I definitely would not want to code one myself haha. I wonder why there isn't one for SQLite. The schema we've crafted so far has a lot of 'one to many' links and joins so I don't think JSON will be useful as there will be a lot of duplicated information (if I'm on the same page as you with that). CSV does sound like a good path though \$\endgroup\$ – maxresdefault Oct 26 at 23:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.