My game has around 3,000 data points that players change and need synced. Currently I just use dictionaries to save / store everything, only barebones functionality as of now. I’ve never used sql and I’m not even sure why or what benefit using a database will give me.
Is using dictionaries to save thousands of items fine or is it worth it to learn sqlite?
The reason to learn something like SQL is if you constantly had to search thousands of not-in-memory copies of the same class for elements containing multiple specific values. Like “find me all the villagers with lips=rosered, hair=ebonyblack, skin=snowwhite, age<30.” Query languages and databases shine when you have multiple criteria, or criteria that are not exact or not unique.
If you have to do this with instances of a class that are already loaded in memory, LINQ has a very similar approach (it’s modeled after query languages like SQL after all).
If you just need to find things occasionally, by a very small number of field values by exact, unique value matching, indexing in Dictionaries is fine. One Dictionary per field value that you must search.
Yes.
Also yes, but this part of the question does not logically follow from the start. In this context, dictionary vs. database has little to do with the number of elements involved, and more to do with the complexity of relationships between your data elements and/or the queries you need to perform.
“SQL” stands for “Structured Query Language” and that’s really a big hint as to what databases built around it are for. Are your data relationships and/or queries complex enough that it’s worth having a purpose-built language to address them? If yes, then SQLite is probably a tool well worth looking into.
You use databases when you need complex queries.
“Find names of all items cheaper than gold pieces that are not epic grade, group them by grade and sort them by price”.
This sort of thing.
If you don’t need something like that then dictionaries can be faster.
Another reason to use databases is when you have a HUGE amount of data. millions or billions of entries.
I use SQLite in my VR app. It isn’t the amount of data that is important as perhaps the purpose of that data. If your data has to persist then it needs to be written to storage on a server that will survive a reboot.
It also makes that data accessible “outside of the VR app” which is useful if a setting seems to have gone awry.
DB is also good if you’re making small discrete changes to the dataset.
Up until needing a db I usually just make a serializable class that models a db table, serialize the class to json and save it to file and easy to load and deserialize, then can query it with linq to organize and populate my dictionaries for runtime.
But when just 1 item changes, I have to serialize the entire thing each time I save, same if it’s in dictionaries being serialized and saved to disk.
Whereas a db lets you edit individual items and persist it in one step. It’s faster for reading/writing individual records/items rather than a giant save file.
A DB is significantly safer as well. If you have 20 users in a serialized class that data is sent to all 20 clients isn’t it? 19 of which should be permitted to see it and even the user who’s data it is should be able to see all the data items.
I can disable a user (for instance) or adjust their access by making simple SQL queries.
If it’s a shared online database, or if there’s some kind of replication system set up, then those things can happen. But I wouldn’t assume that based on a local SQLite integration.
SQL is definatelly easier to maintain, if comes to storing persistent data.
Can use SQL light external tools, to manipulate tables. Filtering of data, mass changes, with single query, etc. Big advantage over storing data in text files.
Also easy to use for modibility purposes of the application, where data driven.
However, it will be slower to read from, when it comes in comparison with C#.
I had SQLite in one project. Been storing set of visual programming nodes, which then could be used, to build bigger more complex nodes. That built complex relation structures. Direct reading and saving, also querying from DB when adding, to removing, or modifying records.
However, once loaded into a game, I were converting pseudo code queries, into set of collections (dictionaries, lists, arrays) in C#. So no need to read from SQL at the runtime. This way I had both modibility, VS and performance.
Modifying structure of whole data, is also much easier, than let’s say doing same on bunch of json files.
In the situation described below, is this a situation where a SQLite database would be beneficial compared to just using excel and then transposing csv to dictionary? It’s more about data entry ergonomics to ease authoring new content and reduce human errors - not runtime optimizations.
suppose you have a list of entities numbering in the hundreds. Each has a categorization string. Based on that category, you want to automatically add each new entry to other spreadsheets.
For instance, in the master list, anytime I add something with string “Item”, it should also get added to a spreadsheet just for “items” where they have item specific stuff defined.
In excel this seems kinda hard to do… I think it can be done but I’m having a hard time at it. Excel seems to always focus on cells, whereas I just want to loop through the text and then if there is something unique in sheetA, copy it to sheetB, based on specific search criteria. I wonder if dedicated database tools make that sort of thing easier?
edit - actually it was easy to do this in excel, I had just been searching wrong things. Was looking for formulas, but no formula is required. Can just do a paste link, and can arbitrarily split sheets and recombine so no need for the string filtering.
General question about ease of authoring content still stands though.
I’m not quite sure where you’re going here but… if you need an Excel spreadsheet then just use Excel. If you are using it for data entry (perhaps a bit odd) but only expect to output the data I suppose that would work reasonably. Note that Excel is notoriously un-auditable and SQL is designed with data in mind. You define a schema with as many rules as needed to insure that your data is consistent. Tables can be defined for categories of data, fields can be defined as having a specific type, to not be null, to be unique, etc…
You could try fiddling with it and see if it makes any sense. Simple SQL is fairly easy but it can get complex if you are trying to create a commercial grade database with data integrity. But then it is at least possible to do. Nothing in Excel is going to make sure that rules are followed.
this should work: http://sqlfiddle.com
Well, there’s standard functionality designed for exactly this type of thing. However, it does require thinking a differently, and it’s a significant technical overhead which I wouldn’t want to take on unless you have loads of data management to do, or there were other problems it would also help solve.
I’m not sure how in line my questions are with the original post or not. If not, perhaps this could be split. My questions are only about data authoring.
i think the concept of primary and foreign keys is the main thing I’m after.
Regarding quantifying how much data management there is, I’m not sure what significant means. Imagine a survival game with a few hundred types of entities, where each can be used as a key for various actions, and each is mapped to various components, and has various efficacy in different context…nothing like dwarf fortress, but probably slightly more complicated than The Long Dark, in terms of number of relationships between items and what they can do.
So like, for data authoring, we work in spreadsheets, right? One way or another that data gets parsed and ends up in the game. We want to work in spreadsheets so that our human eyes can easily see the full picture of whats going on, and also so we can easily cut,paste, rearrange, rename, etc, right? Like, we aren’t going to generate a list of 300 items and 20 different variables for each one by entering code line by line?
So imagine you have an item in the game called “Big Hammer”. It it described with a litany of basic types like ints, strings. You also need to associate it with texture files, and models, and sound effects, etc. That can all be described in one table called something like, “Items Basic Data.”
But in another table you might have all of the actions possible in the game listed. And for each action, you want to know an array of tools that are compatible with the action. So for the action, “Make Big House,” the “Big Hammer” has a viability rating of +1.
And “Little hammer” has a rating of “-1”.
This table would be called something like, “Actions mapped to items efficacy ratings”.
Maybe later you decide to rename the Big Hammer to “Super Hammer”, and you change the category it was in, and maybe you remove Little Hammer. Ideally, those changes should propogate to everywhere “Big Hammer” was used, and wherever Little Hammer was used, we should at least get a warning to indicate that a null reference now exist.
In excel we can do a find and replace, but its slow and manual.
Another thing you might want to do is say, “for columns, show me every action in the game. For rows, show me every item in the game. Draw a highlight where there are any items that do not match to any actions.”
That would be a basic check, but you could layer on more rules, like check for general balance to see if expensive items evenly distribute with actions, etc.
If is just simple listing, probably excle would do.
For more complex DB, where ralational tabelas are matter, excel is not suitable tool. SQL has its purpose. And since you can import csv, plus you need to do the parsing of some sort anyway, only with extra few lines of code you can bring whole data from SQLite.
Again, all depending on use case.
Relationships is what a relational database is intended to model. I’ve worked on systems with 600+ tables, perhaps 200+ of them sets of data used to populate the other tables. A spreadsheet with 600 sheets or tables would be a horrific thing to maintain and unless you are willing to learn VBA a horrible thing to validate.