Database Buckets - Creation a Character His Owner (Simple Question)

Hi there, I've spent the past week or so developing a server and have learned so much and got farther than I could have hoped for (this is my first time), but since I lack any form of foundation sometimes it's the simple stuff that escapes me. As is the case here. I have read a lot online but it's all concepts unrelated to this.

This is not an MMO but the architecture is virtually the same, minus the chained servers (zoned instances running on nodes).

The question is how do I connect the character to the owner? I need the bucket with the character "Bob" for example to be owned by the account "Billy".



I have a bucket "Accounts" for the accounts, it will contain:

- Username ("Billy" for the example)
- Email
- Password
- CharacterLimit (included because different users will have different limits)

I have another bucket "Characters" for the characters, it will contain:

- Name (of character - this is "Bob" for the example)
- Guild
- Class
- Level



Will also have another for inventory/shared stash, and I need the stash owned by the account and the inventory owned by the character.

If I had all these characters in a bucket "Characters" with an another attribute "Owner", then I would have to search the entire database and pull up everything with the "Owner" attribute. This seems extremely bad from a logical standpoint. So do I need a bucket eg ( string bucketName = "character-info" + Owner; )for each account and tie that bucket to the "Owner", in which case it'd have far less iterations to do, but assuming the game has an adequate number of players it could still be inefficient.

What is the best course of action?

 

From the word bucket i guess that you are using NoSQL.

I didnt yet use it but with normal SQL databases my approach was to have a 'id' column
in the 'Accounts' table and a 'owner' column in the 'Characters' table where i would put the id from the accounts table.

E.g.:

PHP:

SELECT * FROM Characters
WHERE owner=1;

This way i could get every character associated with a specified account (id=1).

I dont know how NoSQL works but i hope that it helped either way, much of luck

 

Hi there, I'm using uGameDB (part of the unitypark suite with ulink/uzone/ulobby).

Isn't that essentially what I was worried about doing? Because it means searching through the whole DB to allocate characters to an account when they open up the game.

Because then when they enter the game I'd also have to search through every inventory to allocate an inventory to that player. And not to mention allocating a shared stash to the account.

 

Yes uGameDB is a NoSQL database.

Im by far no database expert but from what i know at least normal SQL databases
can handle calls like the above very efficiently.

However i would suppose that you keep you eyes open for better answers
but dont get stuck on early optimisations.

 

Ah I see! For now it'll have to do.

I edited this post because it was about generating IDs, but it turns out uLobby generates the ID for you.

 

Isn't MapReduce the Riak equivalent of SELECT?

 

Not sure. Currently I'm having trouble with the fact that I can't use C# for the MapReduce and I don't know JS.

I just posted a question on unity answers trying to get help and some "special" individual closes it because it's apparently not related to Unity. How the hell is uGameDB not related to unity?

Anyway, I guess I'll post it here instead.

I'm trying to retrieve the character list for the account, currently it returns all characters in the database because I don't know how to check the IDs against each other when one is in JS and other is in C# - and I don't really know JS.

This is what I have at the moment.

Code (csharp):

1. private const string GetCharacterListReduceFunction =
2.     @"
3. function(valueList, keydata, arg) {
4.    return valueList;
5. }
6. ";

And I call it with this:

Code (csharp):

1. private IEnumerator GetInfo()
2. {
3.     var bucket = new Bucket(bucketName);
4.     var getCharacterListRequest = bucket.MapReduce(new JavaScriptMapPhase(GetCharacterListMapFunction),
5.                                                 new JavaScriptReducePhase(GetCharacterListReduceFunction));
6.  
7.     yield return getCharacterListRequest.WaitUntilDone();
8.     _characterEntries = getCharacterListRequest.GetResult<CharacterEntry>();
9. }

 

Hey Vire!

As someone who often works a lot with large data sets, my best guess here would be MapReduce is overkill for this particular situation. MapReduce is designed for a one->many situation (often termed 'low selectivity' across bulk data); that's where you have a ton of content for just one input key - think all the posts (many) associated with any one thread (input key, one) on a forum. That's one to many.

Depending on what sort of game you've got, I would imagine there won't be that many characters per account. In which case that forms a high selectivity situation, which is excellent for any standard index; General SQL and NoSQL databases support indexing. Take the following query from the post above:

Code (csharp):

1.  
2. SELECT * FROM Characters
3. WHERE owner=1;  
4.  

Without an index, this query has no choice but to check the whole 'Characters' set; this is known as a table scan, and table scans are slow.

Adding an index to the owner field however is a whole different story. The database can find the rows it needs very quickly as indexes are fast.

If it's a one to one relationship, as in one account has one character, then an index is in ideal situations as every key is unique. So in short, use an index where possible

 

Hey Kule,

Thanks for the information - I'll definitely keep that in mind - but - Accounts currently are restricted to ten characters, and that number may increase - and they'll be able to purchase extra character slots (assuming it's ever released). This game will have level capped arena's where players will want to compete and that involves having multiple characters.

So essentially, can I still do:

Code (csharp):

1. SELECT * FROM Characters
2. WHERE owner=loggedInAccount.ID

^ Also, how would I write that in C#?

Or is that still a table scan? The ID isn't an integer unfortunately. Eg an ID could be KDLeG3

 

Right okay, do you have any ideas on the average amounts of characters any player would have? I know it's a bit of a far off question if you're pretty early in development hehe but would you expect most people to actually have 10 for example?

If that's the case then the selectivity is pretty low; 1 key and 10 results makes for a selectivity of 0.1. In this case an index would still be more effective than a table scan, but reverse lookups would likely outperform both an index and MapReduce (which is unsuitable still).

Reverse lookups involve anything like the following:

+ Set of character ID's


MapReduce is almost always applied to the entire or very large fragments of the data set, thus the 'reduce' step; e.g. "what's the average amount of characters per account" is a great situation for MapReduce; something that requires every entry to be analysed in some way.

As for rewriting that in C# I haven't used uGameDB myself so I'm not sure what kind of interface it provides you with; their unfortunately all different. If owner was indexed though, it wouldn't be a table scan unless the WHERE clause was more open

E.g. with a index on owner:

Code (csharp):

1.  
2.     SELECT * FROM Characters
3.     WHERE owner=loggedInAccount.ID
4.  

Is not a table scan, but..

Code (csharp):

1.  
2.     SELECT * FROM Characters
3.     WHERE owner=loggedInAccount.ID or Level<20
4.  

..would be one, unless you also indexed Level. Level is the kind of thing that has exceptionally low selectivity, as in tons of characters with the same level, so indices there aren't very useful at all anyway.


Indexing can work with non-numeric stuff, but is it possible for you to also assign a numeric ID too? They will far outperform any textual ID's in lots of situations

 

I might have to re-write some of the stuff that uLobby/uGameDB uses by default; meaning the account system. It has: Username, Password, Email.. but I'm not sure how to add extra attributes to it. It automatically assigns that ID and I don't know how to change it to numeric. I don't know if I can even access this stuff... unfortunately.

So I'll make my own accounts bucket.. Working on something in the actual game at the moment, but hopefully I'll finish that up in the next couple of hours and move onto this. Thanks so much for your advise.

But then I'm back to how do I generate unique random IDs?

 

Just a counter is all that requires; most of the time though a database can provide these counter ("auto increment") ID's for you

Sometimes it's a good idea to mix and match storage solutions. You wouldn't use a hammer to fix a leak for example; similarly, using different storage solutions which are each best suited to what their doing is the best way to structure an efficient network. A lot of the data I work with is in totally custom NoSQL style databases, each slightly different to get the best possible efficiency on the large query volumes that they handle.

In databases fixed blocks of data are ideal; e.g. if each of your accounts takes up exactly the same amount of space, then you end up with a highly efficient system. Reverse lookups are of course varying blocks of data; there can be 3 ID's or 20, so if you can, you may find implementing a linked list would work wonders

E.g.
- Account #14 has 3 characters. The first character is #204. The account row is only holding two values - a count (3) and the first ID (204).

- Each character then has a 'next' property.

- Character 204's next is 198
- Character 198's next is 190.
- Character 190's next is blank.

So by following the chain, the linked list, you get hold of each character for that account directly. Note that each character is just storing a single property, so everything can be a fixed size block of data; if you can make use of structures like this, then you end up with some excellent performance. There's also a reason why the ID's count backwards along the chain too!