Best way to handle a database for RPG game?

Not sure where to post this, but here goes.

I'm working on an action RPG game and I'm trying to figure out the best way to handle items (weapons, armor, potions, etc.) and NPCs (townsfolk, monsters, quest mobs, etc.). I've looked at a few database things, but I'm lost as to which one to use. The issue is that I'm working on a project for computers (PC and Mac) and consoles and I need a solution that works for both. I would also like a way to enter and deal with items via website instead of entering through Unity. Nothing wrong with Unity, but I'm going to have other people create and enter items into the database and having everyone using Unity just doesn't seem like a good idea.

What would be the best database to use that flexible, easy to use, and works on both consoles and computers?

Any help or discussion would be greatly appreciated.

 

XML

 

Why is XML good? Is there a way to encrypt it so that the data can't be easily modified? I'll admit that I don't have much experience with it and what little experience I do have is from messing around with it using Ogre 3D back in the day.

 

You never mentioned security, is there a reason you don't want it to be modified? Is the game single player or multiplayer? Is it competitive? Is it an MMO? All these things matter.

You could encrypt XML, but it's not like that can't be broken. The question is why do you want to encrypt it?

 

I just don't want game files modified if at all possible. Of course, it's a PC, so people will probably do it at some point. I just don't want that process to be easy. It's not an online RPG or MMO, though. The game is a single player action RPG. Not a loot game like Diablo or Torchlight, but there's still a decent amount of stuff to get via quests and such.

 

You should let them cheat themself, if you do it via XML and if it's done right your game would be moddable to an extent. Mods are the essence of some PC games and it's what can make or break some games.

There's no reason to not let players cheat a little if it's single player, if they're having fun then that's all that matters.

 

Just buy a teach yourself mysql in 21 days or whatever and I am sure it will be more then enough to do whatever you want with it.
https://www.assetstore.unity3d.com/#/content/4334

 

I'd like to be able to add things to the game and I just don't want people "cheating". It's an option I can explore later if I wish to go that way. It's a philosophical thing, but I don't like cheating personally, and I'd prefer if people played the game the way it was supposed to be played.

I considered that. The issue I have is whether or not this will work on consoles as well. None of the packages I saw on the Asset Store mentioned anything about this. I don't want to pay money for something that ultimately won't work.

 

Well, XML in the simplest form is just a text file. Encrypt the text file and decrypt it at run time. But if you want to go the way of securing your single player game, you're going to need to do a lot of prevention from things like memory editing etc so people can't give themselves 5 billion humans(oo..I just got a game idea, ergh..another one to add to the pile of thousands of sim game ideas) or whatever currency you use. There is no way to completely secure your game completely(except for a few things that aren't viable), this would not be so much of an issue on a console though.

That should work . If it doesn't, I'm sure he'd be willing to offer you a refund as it clearly states it works on all platforms. You can ask him.

 

Yeah. Nothing will stop the hardcore from haxin' their way to victory. However, I want to make it hard enough for the average Joe to avoid it. Again, it's just a philosophical thing.

I'll have to look into it. MySQL was my first choice, but after looking over some assets I was worried about the console side. I guess I can check with the guy to see what he has going on.

 

XML works fine as Dabeh pointed out besides I don't think the average joe is able to crack it. You could also try JSON.

 

Simple way would be when it reads from XML it makes sure the numbers aren't ridiculous, that's the best you can really do.

 

I wouldn't use Mysql, unless you're keen on shipping it with your game, dealing with install issues, and paying license fees for using it commercially. XML is fine. But the simplest thing is just to keep your data in a serializable class, and then serialize and deserialize it. Binary format will mean it's harder to view/edit/cheat.

http://msdn.microsoft.com/en-us/lib...zation.formatters.binary.binaryformatter.aspx

 

Can use dummy values or a secret formula to check for an edited var.

Say you have generic variables for gold.

Have gold be the result of a calculation. So like var xyz * 2 = gold (or something more complex)

Then if they change the variable gold directly, the calculation xyz * 2 will not = their edited value for gold.

 

Thanks for all of the suggestions in this thread. I've got a lot to think about, but honestly still just as lost as I was last night.

Anyway, what would be the advantage of using serialized classes?

What do other folks use in RPGs for databases?

 

I used XML for practically everything from entities to quests. It took me an hour at max to write a script that would take a giant xml file (a very large amount of quests, npcs, entities, dialogue) from Articy Draft, which would then be used in combination with reflection to create the entities(without any extra programming even for new entity types, the variable names and types just had to match up along with the entity type) and put them in an organized array with their ID or I could have just loaded up individual entities at runtime(but as my game was quite large, had a lot of entities and that was on the server I loaded up all the "non-unique" entities at the worlds loading phase). I also made a "blacklist", instead of a white list for what variables I would prefer not to be modifiable by the XML, although I never used it. I could have also encrypted the whole file, decrypted it at the loading screen but while it might be slow to do it that way it's what loading screens are for .

For entities generated at runtime(I had one million at minimum) I used NoSQL, storing them in a somewhat efficient way with proper indexing so I could grab them. I would have used XML for these, but as there was a huge amount I felt the "runtime costs" of loading them up with the costs of getting the correct .xml file from my hard drive would have been both time consuming and bad for my hard drive(I did have this at one point though, it ran in a separate thread, but this was before I realized I needed a solution that scales well).

It really does depend on what your game is, knowing it's a RPG and single player isn't enough. I still suggest XML either way though.

In fact, now that I think about it, I highly recommend you take a look into articy draft. It's perfect for teams(has team collab), has a free student version that's a 30 day trial etc. They also have a plugin for .NET etc but it's .NET 4.0+ and sadly unity does not support that to my knowledge.

Is there a particular reason you don't want to use XML?

 

Well, when I was messing around with it with Ogre3D, I told myself I wouldn't screw with it again after that. No offense to anyone, but just isn't something I want to mess around with. Even if that weren't the case, I don't want the data open for everyone to read. Won't be a problem on consoles, but any PC guy will be able to get to the data and I don't want that stuff messed with...if possible.

I'd like a different solution. I guess there aren't any standards for how people deal with databases. Looks like back to the drawing board.

 

Sorry you had such an unfortunate experience with XML, I have nothing but song and praise(except for that day I had to first wrap my head around it heh).

Understandable(hope you can deal with me repeating myself(which I'll stop doing after this post hehe)

It's just text, you can encrypt it just like you'd encrypt a save file. There's nothing special about it. It doesn't require the file to be read "as is", it can be read from a string if need be.

If you secure the .xml files itself, then the persons next plan might be to target memory etc, if you have a method of doing this that makes it harder than modifying code so you can isolate where it decrypts an xml file and then outputs it to a file you can modify, then encrypt it again so your program can use it, let me know. I do have lots of half-decent methods of preventing people from screwing around with variables with cheat engine, even those with lots of experience but it still would be less work than the former method.

The standard is XML IMO. Console games use it (PS3 Xbox 360) and the future generation will too, so do games like sky rim etc. Your only other option is to use something like NoSQL, json, inseranothermethodhere or just roll out your own format.


Either way, not disagreeing with you here, just letting you know what I know, maybe someone more experienced than I knows of a better way to do it.. I'll end here, since I'm just repeating myself and you're trying to get a different answer and I can't give it, I'll let someone else chime in now that might have a different opinion on how to do things .

I personally think there's plenty of room for improvement in these areas and so far there hasn't been anything revolutionary that's come out, maybe I'm out of touch though. I definitely would like to see something better than XML so I can understand why you wouldn't want to use it somewhat and I'm curious to see what other people think is a good solution because this has been a topic I've spent some time on in the past.

 

The advantage of serialized classes is that the loading/saving of complex types is handled for you. You can create a main class to hold all of your data and simply serialize/deserialize it. If you have gigabytes of data that all has to be accessed randomly, then this is not a valid solution, as all your data is held in memory. However, for the vast majority of games this is not the case. You can use dictionaries to use standard key/value searches in place of selects, etc.
You can certainly use the XMLSerializer to read/write XML of your classes, but it's a little more limited than the binary serializer. However, using XML does give you the ability to hand edit the files.

 

Was just browsing the wiki and saw sqlite http://wiki.unity3d.com/index.php/SQLite never used it but may be an option.

 

If your data needs to be stored on a server (which I assume it does since you want to enter items on a website), then I'd suggest going with web services as your service layer and use them to get any information that you need from your database.

I like using MySQL for a server database solution.

Check out http://wiki.unity3d.com/index.php?title=Webservices_In_Unity

SQLLite is a great little client-side DB that you can use as an in-memory database or to persist local data, it shouldn't be used as a server database though, there are far more robust solutions for that.

 

Thanks for more replies, guys.

It's a single player game in the vein of The Witcher or Final Fantasy. No server stuff going on. The reason I want to be able to enter data from a browser is that it would be easy for other people to add to the database without needing Unity. I actually purchased ZDatabase without really understanding what I was getting into. So it was a waste of money. Which is why I wanted to know what people usually use for single player RPGs. I don't know about keeping everything in memory as I'm not sure how people do that, either. I guess more research is in order, but I was hoping that people who have done this before could shed some light on how it's usually done. What database is usually used and such.

I have no problem blowing a few megabytes on keeping the item database in memory, but a file solution would be fine, too. However, I'm not sure how large the database will get, but there are a bunch of items for the player to get as well as crafted weapons as armor so it has to be expandable on the fly.

Anyway, I hope that sheds more light on my situation. I worked on a MMO at some point and everything was done with PostGRESQL. I wasn't sure what people used in the single player type games, but if I was going in that direction, I'd definitely use SQL.

 

For a client-side database deployed with a Unity game, then SQLLite is your best option.
I still don't understand this 'website' you are talking about, What is it used for, is it only for the local player to add some data to the game?

If you want a client-side management tool, your best bet is to skip the website and build a desktop application to manage the data.
But since you want this on Mac and PC, I'd suggest that whatever this 'website' can do with the database, save yourself the hassle, and just build this functionality directly into your game.

 

He needs the database to be distributed with the game. What he is going to do is: build a website and connect it to the database; use people with no knowledge of software programming to create/update the items in database and when is all done, include the database file in the game distribution kit. The advantages of such an approach is the relative easiness in building the tool for item input (a website can be done in days) and the structured data layout based on a relational database structure. Another advantage is that if you decide in the future to make it online, moving the database to the server should be a breeze.
As Meltdown said, SQLite is your answer. What you should be concern is the distribution terms for the database software, because you need to bundle the database in your game and install it on players machine.
A quick search revealed a wiki example on how to connect to the database directly from unity http://wiki.unity3d.com/index.php/SQLite.
Also as far as I remember, SQLite is used by mobile apps to store data offline.

 

SQLite would be good, but the issue I have there is with consoles. If it's no problem on consoles, then I'm okay going that way. I'll have to look into it a bit. Anyone know what's the best SQL package for Unity?

The reason is so that people who are not me can update and maintain the quest, item, and NPC databases without needing Unity. All they would need to do is go to the web portal and start adding things. I can update the database in game to reflect the changes, but basically it's a relatively easy thing to do and people entering items don't need to know any programming or fiddle with Unity at all. Less chance of someone messing something up by accident and multiple people can be doing things at the same time.

 

Unless you have access to and can test your game on every platform (all the consoles, etc.), I would stay away from any binary format for the database. I, too, would recommend XML. You can obfuscate the variable names in the XML to discourage superficial tampering, and decompose critical variables (like "gold") into component variables (this will make it harder, but not impossible for the average player to tamper with them).

There is no solution that will stop everyone from tampering except to store all the data on a server, rather than on the client. Even encrypting the data will not prevent this because you have to put the key somewhere in your client code. There are all sorts of interesting articles out there about how developers of popular MMORPG's work to prevent mod'ing, and you'll see it's a constant battle.....

 

+100 for XML. There is good support for serialistion, its cross platform, simple, extensible and extreemly well documented. There are loads of good tools for editing/validating XML and its likely to be your friend whatever direction you take the project in. It seems to me a relational database or similar is overkill for any single player, single PC game.

The encryption question is a little bit of a distraction, there are a number of ways you can encrypt XML from .Net, but I'd also worry about encryption later on, all you need to know right now is that if youwant to, you can. You probably don't want it encrypted/obfuscated while you develop anyway.

 

Well, I tend to think about these things in the long term. What can I do with X or Y and how will it affect me later on. To be able to encrypt the data or store the data in a particular way is something I'm interested in. Not just now, but for the future as well. Like I said earlier, I didn't have a good experience with XML so I really don't want to use that. On top of that, it didn't seem very secure.

 

there has an option , http://forum.unity3d.com/threads/199279-A-database-for-Q-amp-A-and-RPG-games

 

What are the advantages of XML over JSON?

 

If you have the money to purchase Unity licenses for consoles, then I am sure you'd have the money to pay someone to make sqlite work, assuming it doesn't out of the box. (The source code to sqlite is available, so am sure it can be made to work.)

 

client side definitely xml or json(I prefer json these days).

As far as security goes it doesn't matter if you are running sqlite or xml files, either can easily be modified/read. You might consider encryption as was mentioned(of course a good decompile of your code will reveal the key) and/or a hashing algorithm to make a checksum.

Ie. Compute a hash from those values and store it as an attribute of the object. When you read the object from disk you recompute the values into a hash and compare the two hashes to make sure they are the same. You store a new hash when you do change the object. Also if the user is able to figure out your hashing algorithm they can then compute new hashes for modified values themselves(ie don't make it just sha1 hash of the filename, use some external variables).

 

Also consider JSON - it's smaller than XML, more suited for data, and has a number of equally small access implementations.

 

Contenders are:


- From Hardest to Easiest:
MessagePackUnity, BSON, Custom serialization, JSON, SiaoqDB, UniDatabase, SQLite, XML

 

When you make a technology decision you need to determine what factors affect your decision and make your decision based on those factors. Heres a couple of tables I whipped up. Its not necessarily accurate but gives you an idea of the kinds of things that you might need to think about when answering this question:

​

 

Eh , querying JSON isn't all that fun . It's best to just use a local database . With Json you have to deal with the bizzareness of arrays of hashtables , ect . Plus its vastly easier to add a new column to a SQL database then it is to add a new property to JSON objects

 

We are not making a RPG game, it's a survival horror but we do have quests, items, dialogs, conversations, etc (lot of data to handle mind you). We use XML and some tricks (similar to what Imbarns described) to avoid people modding it easily. Works pretty well, It's lightweight and actually very easy to handle.

 

You can't protect your stuff from hackers, just do something simple like a binary xor to get the average script kiddie out, anything above that is a waste of time

 

Sorry you had a bad experience with XML. I've dealt with bad XML and good XML. There are a variety of ways to interact with it and some are ugly, others are not.

I routinely work with MySQL (monthly), SQLite (weekly to daily), and IBM's DB2 (daily). I strongly recommend SQLite for local storage or high read-only sites. One thought is if you can find a safe encoding/compression scheme for the SQLite database files, you could go that route. Storage always maintains encoded/compressed version and in-memory has unencoded version that is sync'd whenever necessary... maybe on commit (if you're using transactions?).

 

Thanks for all the replies, guys. Lots of things to consider and I'll look into your suggestions. I didn't think this would be that big of an issue, but I still haven't settled on a good solution. Good to know there are a lot of options.

 

Even though it took me ages to get running, I'd suggest XML.

 

Chat-Mapper and Articy:: Draft can import/export XML.


You can design your dialogs, quests, in Chat-Mapper and then export it to XML, read it from your game...

 

If you don't want to deal with XML, you can design your dialogue and quests in Chat Mapper and bring it into your game using the Dialogue System for Unity. The next version (1.1.1) supports articy:draft too. If you decide to buy the Dialogue System, PM me your invoice number for the 10% discount code on Chat Mapper that we arranged with Urban Brain Studios.

 

My suggestion is to take the time to learn Hibernate (or NHibernate for C#). At that point you can just use some online provider to store data to ... alternately if you're building a server put it in the cloud, amazon has a 'simpledb' for instance.

 

I looked at your software a few days ago, but I do my development on a Mac and Chat Mapper doesn't work for Macs. Yeah, sure I could use Parallels, but it's a poor substitute for having actual native software that we don't have to jump through hoops to use. So it's not really an option. Though, I did read that it has a built in Unity editor, so I haven't ruled it out completely. Just seems like software that's made specifically for PC users.

On another note, I've looked into some software on the Asset Store that may help out, but I still haven't decided what's the best way to do what I need to do. I suppose I'm going to have to decide sooner rather than later, but it's been hard deciding what to pull the trigger on. Lots of options and no way to know which way is the best.

 

We have several Mac users. Some use Bootcamp/Parallels/etc, but I think many use the built-in editor. Chat Mapper and articy:draft are just a convenience if the author is more comfortable with them. There's much, much more to a complete dialogue system than just the editor, and the Dialogue System as a whole is platform-independent.

 

<nod> I can understand your position. Still, seems to get the most out of what you've setup Chat Mapper is needed. I imagine that using the built-in editor isn't anywhere near as sophisticated as Chat Mapper, either. So while a Mac user can do some things with it, they are missing out on some things by not being able to use Chat Mapper.

Anyway, don't mean to derail the thread. This was just my impression of Dialogue System after doing some research on it a couple of days ago. If this is not the impression you're trying to convey, then perhaps you should definitely be a bit clearer for those who are looking into your software for the first time. It seems powerful, I don't deny that, but on the surface it just looks like a piece of software that would benefit PC users more than Mac users.

 

Thanks. I'll clarify that all features are available without Chat Mapper or articy:draft -- or, for that matter, Twine. The next release will also import Twine, which is available for Mac. For large conversations, I actually prefer the built-in Unity editor's outline format, which is similar to the Dragon Age Toolset. It's easier to see the big picture than graphical node editors that take up so much screen space. In any case, dialogue writing is just a small part of what the Dialogue System offers (AAA UIs, cutscenes, gameplay integration, etc.); my goal is to leave it up to the authors how they want to write it. Sorry, didn't mean to keep derailing the thread. If anyone wants to continue the discussion, please hop over http://forum.unity3d.com/threads/204752-Dialogue-System-for-Unity or feel free to PM me.

 

XML is bloated and often difficult to work with.
I'd suggest JSON over XML any day.

Although you can't beat the flexibility of a SQLIte database for queries and joins, or something like Entity Framework for querying data.

 

Or if you use JSON .NET you can use BSON (Binary) to be even more compact and not human readable.