Hi all,
I’m making an online RPG and my current approach to handling items and character inventory is using SQL, where a table for the character contains entries for each item slot which stores the item type, material used for that item, and any modifications made to that item, each having referencing a corresponding table where stats, names and any other pertinent information can be grabbed.
But I feel that there might be a better way to do this, though I don’t know what it might be. I’ve looked a little bit into using JSON, but since Visual Studios Community (the version that comes with Unity) doesn’t seem to support JSON editing, I haven’t really spent much time investigating it.
SO! My question for the community is, what do you think would be the best way to handle an item database and/or character inventory system where most items are highly customizable?
Look at GameSparks. They have a NoSQL Database which is perfect for storing objects in a db. And they are easily serialised back and from the server.
Their SDK for Unity is really good as well, makes things really easy.
JSON is a file format it solves a different problem to a database. You can (and very commonly do) use it as a format for transferring data from your database to your client (e.g. via a web app).
General wisdom is that you don’t expose you database directly to the client. Typically you would have a thin web app in front.
Actually storing data in JSON files (or CSV, or XML) either locally on the server is a completely reasonable approach depending on the scale and scope of your game. It becomes harder to manage and maintain as the amount of items grows.
Network efficiency is rarely the concern here, the main concern is maintainability. A custom binary format would be the most efficient but also the hardest to maintain.
Also for smaller projects don’t discount XML (slightly more network bandwidth but natively implemented), or CSV (you have to write a parser, but a google sheet is a nice way to easily maintain game data).
Another option is a backend service (e.g PlayFab). These will handle the items, logins, etc, and generally have good editors.
TLDR
Large project - DB with web app or game backend as a service
Small project - JSON, CSV or XML files
I understand about not exposing the database. I’m currently using SmartFox as the go-between. I’ve actually already had someone make an attempt at SQL injection, but since SmartFox only executes pre-made commands, the attempt failed.
I’ll give an example. One entry in the character inventory table is an equipment. It has the slot it is currently equipped to on the character, the item type (long sword, dagger, short sword, hand ax, battle ax, etc) the material used to create it (iron, steel, copper, etc) and a modifier, first enchantment and a modifier, second enchantment and modifier, item tier, and how many of this item are in that slot.
As a database, the entry, as I have it set up, would look like
slot | type | mat | mmod | ench1 | e1mod | ench2 | e2mod | tier | count
1 | 2 | 1 | 1.0 | 2 | 1.0 | 4 | 1.0 | 2.2 | 1
There’s 18 equipment slots and up to 160 inventory slots per character.
I’m not really sure what this would be considered in terms of scale or scope. Also, network efficiency is a concern for me because character information, including inventory, is sent to the client during keep-alive checks every few seconds. Even a couple bytes to communicate each entry or item would make a large difference.
does not go with
^ What he said. You don’t need to send item data during keep alive checks, you send deltas (i.e. send details of what has changed when it changes).
Generally you would have a big item database sync on game load. You would then have character specific details retrieved by the client when the other player comes within some kind of zone of interest. Finally you send deltas when the known things change. You can create the current state from any previous state by combining it with the list of all deltas since that state was sent.
This is not the pattern for every game, but its a generally good starting point.
I don’t know how I’d go about even checking what is different. SmartFox commands are all JavaScript, which is about as foreign to me as German. I don’t know how to send information to connected players except as replies to any commands the player sends to the server. Putting everything in with the keepalive, or checking everything every so often, was the only way I could think to ensure that all the information the player has matches what is on the server.
I know nothing about SmartFoxServer so I can’t provide any insight in to that.
Probably the best system for storing inventory data ever created.
Your answer for every question seems to be SmartFox Server… what do they recommend? In all honesty.
I really don’t know. It’s supposedly really versatile, but their documentation is utter garbage, so it’s just authentication and database interfacing for me. I’d gladly switch to a different server software, but since I’m running Linux, the other popular choice, Photon, is not an option for me, and I can’t exactly make my own due to inexperience.
I’d like to use Unity for authentication and database interfacing and have everything using the same program, but, again, inexperience.
You only need SmartFox for multiplayer, you don’t ‘have’ to use it for anything else.
As I said, check out GameSparks, it makes all your data and authentication requirements a breeze.
I been looking at their website, does GameSparks host their own servers so I don’t need to make my own server? Can you still use SQL with GameSparks?
Gamesparks uses NoSQL exclusively. Can’t use SQL.
My only issue with GameSparks is that it’s a cloud-based service, whereas I want physical access to the system where my database is stored.
It is very unclear what your actual problem is. You said you have a SQL database but want to look at alternatives that are network friendly, those two things have nothing to do with each other so you need to expand on exactly what the issue is.
JSON is just a format. SQL is a query language used to access tables in a database. You are mixing apples, oranges, and possibly some grapefruit all together here in your comparisons.
My best guess is you want to know if you can replace your database with a bunch of flat files containing JSON, but it is hard to tell. If that is the question, then sure, you can, assuming you can represent the relationships you need within the JSON objects. None of that has anything to do with networking however.
I’m wanting to know if there is something that would handle my item/inventory organization and storage better than an SQL database, that also fits the criteria of having low network cost. JSON just happen to be one possibility that I’ve heard of/read about.
I also worry that with having so many tables being referenced for information, that query times will be noticeable, and if a different method might be more efficient
A SQL database in no way implies that you have a low network cost. I think that is where the first misunderstanding is.
If you send all the inventory data to the client with a SQL query, it will roughly be around the same amount of data as if you stored the data in say flat files and downloaded it over HTTP.
The problem I see right now is you are sending too much data to the client, you need to fix your networking logic. The way you have described is not really how any networked game works, so possibly you are not explaining it right.
I understand my method for transmitting is inefficient, but I’ve also researched how much data each value type will cost, so I know roughly how large each entry will be.
But the tables seems like a messy way to store things. Look at this table for this pieces of info, this other table for these other pieces, and yet another table for even more pieces, none of the tables being related.
Since people seem to be getting hung up on the network criteria, forget about it.
Is there a system that would handle a lot of diverse items better than SQL database?
SQL databases are relational in nature so table data should always contain unique identifiers(keys) that can be used as links to data in other tables. Using those relationships you construct a single SQL query(called a join) that returns just the data you want from multiple tables in a single response(also see unions, subqueries, views, etc.)
The other route is what was mentioned above, use a NoSQL database which works a bit differently. In NoSQL you store everything you need for a query in a single record(also called document). It can lead to a lot of redundant data in the database but is better for certain types of games/applications.