If you decide to design some sort of in-depth customization system for the player that utilizes a ton of different options, would you be better off using SQLite or just a whole bunch of constant variables in the program?

For instance, let's say you want the player to be able to build their own weapons. For a sword, they can put together a hilt, a blade, and maybe a jewel and inscription. That's 4 parts, of which there can be many different options, and that's just for one weapon. For the sake of example, let's say there are hundreds of different parts alone, which makes for a great number of combinations. The parts themselves are of course just weapon components, and don't have any special properties that need to be programmed - just certain attributes that say how they interact with the weapons (like which ones they can be attached to) and how they interact with other parts (if there is a special effect when combined with other ones, for instance - though this is probably better off programmed).

Presumably, this would allow for a great deal of customization, so there would be an in-game system in place that would allow the player to easily search for parts of a certain type/with a certain effect/of a certain element/etc. by using search filters, which sounds perfect for a database and not-so-perfect for program variables.

As a general question, how do you know when it is time to manage all of your constants through a database as opposed to doing it through the program? Is it the number of your variables? How valuable search/sort is? Will using a database quicken or slow the performance of these customization options?


There's a third option, one between "constants in the program" and "data in a database," and that's data in regular old files on the disk (for example, JSON files).

If the primary operations you are going to be doing here are (1) editing the data files and (2) loading all the data into memory, you don't really need to employ the additional complexity of a database; you don't need the additional complicated query functionality and that way you won't have to manage the additional overhead of the table schema or deployment problems (for non-trivial games). If your searches are simple enough (it sounds like they are primarily compatibility-matching searches), you can do them easily enough without a SQL query.

Although SQLite specifically is a compact exception, many database solutions are overkill for this kind of simple data-driven game data problem.

Even if you opt to put the data into SQLite or some other database eventually, moving to a file-based approached first might be a good stepping stone as it will allow you to reduce the scope of the overall change slightly, letting you get your work done in smaller chunks.

As for knowing when to put things in data versus code... it's almost always better to put them in data, unless you have a really trivial project. What kind of data format you use depends heavily on the kind of project, and often a really good data-driven architecture (that supports awesome things like live-edit loops and cascading hot-reloads) can be more work up front, but in the long it's usually far more efficient.

The value of searching and sorting depends, again, on the way your game will use that data. It's really rare, in my experience, that you'll want the user to be given an interface (of any kind) to a search that is complex enough to warrant SQL. However, if the data you are tracking is analytical in nature, for example, if you are tracking events users perform in an online game and you'll need to query that data after-the-fact to discover user trends and help tune your design decisions... in that case complex queries are a good thing and you can justify the extra complexity of a database backing store. But then again, that's not constant data we're talking about.

  • \$\begingroup\$ Thanks. I hadn't considered this option. However, it seems very probable that the developer may not wish for the user to view this file (they may not want the user to see all the parts, for instance, since that might ruin novelty/surprise/secrets). Is there a way to hide this file from the user? Also, will using SQLite in a situation like this actually cause any performance issues, or is it primarily a matter of good design? From your post, though, it seems like you're suggesting that a DB for any number of plain old constants is typically less efficient. \$\endgroup\$ – Daniel Ward May 2 '13 at 18:15
  • 1
    \$\begingroup\$ You can do various things to hide data from users, but that only acts as deterrent. If the data is on their system (and data in a local SQLite database is also on their system), then users can/will access it if they are determined. Search this site for the various questions about protecting data from cheating/spoilering/et cetera. It's a common question (and usually not worth the trouble). \$\endgroup\$ – user1430 May 2 '13 at 19:18
  • 1
    \$\begingroup\$ I think that a DB is probably less efficient for you, the developer. It may or may not (depending on your uses) be a runtime efficiency problem. \$\endgroup\$ – user1430 May 2 '13 at 19:19

Go with your SQLite idea. You'll find that it's very easy to integrate and use, plus much faster than you would think.

First reason is that you can add new "stuff" just by editing the database and your code never has to change, this makes tweaking and updating end users much simpler. Plus even a non-programmer can adjust these things, which is great since you will have a ton of them.

Second reason is that organizing data is kind of what databases do, why reinvent the wheel?

Third reason is that SQLite is great at "give me all the sword hilts that are red colored and require > level 25 to use" whereas your own code... why reinvent the wheel?

The decision point on when to convert from in-program data and external data mostly ties into the above reasons: how much data do I have to wrangle? how often will I need to update the variables? will I need to organize? and how do I find what I need at runtime?

Here's the neat thing, once you start thinking "data driven" many tasks become simpler. Your programs will become more flexible, your designs more dynamic, and so on.

  • 1
    \$\begingroup\$ I can't speak for what Daniel Ward would think, but SQL databases only come in varying degrees of slow. SQLite is probably one of the less slow, but it is still ridiculously slow compared to process memory. As for searching, it generally takes something in the order of 1000 rows before an SQL database using an index will beat a C program doing a brute force search, possibly a lot more if the database has to hit the disk in the process. The prime feature of SQLite is providing durability for lots of small pieces of data, if you don't need that, you don't need SQLite. \$\endgroup\$ – aaaaaaaaaaaa May 2 '13 at 20:15
  • \$\begingroup\$ Yes. But. Compared to the effort required on the tools side creating the data, modifying it and being able to let someone else edit that data without special instructions, this overhead is less than nothing. Especially over the lifetime of a project that involves "a ton of different options" and the requirement to "allow the player to easily search for parts of a certain type/with a certain effect/of a certain element/etc." I can either write all that code or hook in SQLite and get on with other things. \$\endgroup\$ – Patrick Hughes May 2 '13 at 22:34
  • 1
    \$\begingroup\$ I kinda doubt that an SQLite database makes for a particularly good modding platform, various plain text file data formats are usually preferable for that purpose. As for comparing runtime performance with the time it takes to write code, that is apples and oranges. If you run out of performance with SQLite there is only ugly hacks and chopping features to get you forward. You may be experienced enough to not use SQLite in cases where performance would be an issue, but your answer doesn't show. \$\endgroup\$ – aaaaaaaaaaaa May 3 '13 at 6:26

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.