Sign in to follow this  

Database storage

This topic is 3549 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Hello all, (In this example, I'm speaking quasi-MySQL-pseudocode as I am using MySQL for my database storage.) When designing the database storage for the various objects in my game, I came across the dilemma of storing arrays of objects. Now, the simplest form of storage I can think of, is to dedicate tables based on object types. For example:
Table 'account':
int id;
varchar username;
varchar password;

Table 'playercharacter':
int id;
int account_id;
varchar name;

Table 'inventory':
int id;
int playercharacter_id;
int object_id;

Table 'object':
int id;
varchar name;
int weight;


In the above examples, to obtain a list of a particular playercharacter's inventory items, I would simply need to "SELECT * FROM inventory WHERE inventory.playercharacter_id = 'some playercharacter.id';" Is this a common method of storage for situations like this? Cheers,

Share this post


Link to post
Share on other sites
Yes, it is. Databases are optimized for such situations. Make sure to create an index on the id field. BTW, for more complicated situations, you'll want to do a little more reading; the key phrase is "database normalization".

Share this post


Link to post
Share on other sites
Quote:

Table 'inventory':
int id;
int playercharacter_id;
int object_id;

Some trick questions spring to mind:
1) Why do you need a key for this table?
2) Ownership should be a one to many relation: are you sure you need a special table, like for many to many relations?
3) Objects that belong to players are listed here. What other states can an object be in? (Lying around, NPC-owned, borrowed...)

Share this post


Link to post
Share on other sites
Quote:
Original post by LorenzoGatti
Quote:

Table 'inventory':
int id;
int playercharacter_id;
int object_id;

Some trick questions spring to mind:
1) Why do you need a key for this table?
2) Ownership should be a one to many relation: are you sure you need a special table, like for many to many relations?
3) Objects that belong to players are listed here. What other states can an object be in? (Lying around, NPC-owned, borrowed...)


1) The database isn't the only location that an object's unique ID is used; it only makes sense to duplicate in the database tables what is stored on the gameserver.

2) I'm not sure; databases are a weak point in my knowledge, hence why I asked here :)

3) I have loads of other stats that objects can have. I omitted them here for brevity's sake.

Share this post


Link to post
Share on other sites
What you have come up with as a simple solution is actually a rather standard way of resolving n:m (many to many) relations in relational databases.

Ownership/Inventory could be a 1:n (one to many) relation, but I think it's n:m in the vast majority of cases. It really depends on your concept of "object". The most likely scenario (and what you did here) is that your object table stores object archetypes. That's why an object can be owned by many players, and a player can own many objects. The actual "instance" of the object is defined in your inventory table. That table would also be a good place to attach data that is related to the relationship and not the archetype, for instance the size of the object stack in the player's inventory or the current condition of an item.

As for other states, you'd end up with more relationship tables like this one.

Having said all that, I think you're starting at the wrong end here. The first step in designing the database storage for your game should be an entity-relationship model, expressed as an ER-diagram (example). From there, the next step is decomposing the entities and relationships into the various tables (and that's where you'll see that you need tables for n:m relationships). Maybe a more immediately relevant example, here's one (sans attributes) for a space trading game that I implemented for a class project a couple of years ago. There are many n:m relationships in it. Good and factory are archetypes, with goods in particular appearing all over the place in various tables.

A good rule for writing such diagrams is to keep entities as nouns and relationships as verbs. Attributes are mostly attached to entities but can be attached to relationships too (like with n:m relationships).

[Edited by - lightbringer on March 24, 2008 10:01:17 AM]

Share this post


Link to post
Share on other sites
Quote:
Original post by LorenzoGatti
Quote:

Table 'inventory':
int id;
int playercharacter_id;
int object_id;

Some trick questions spring to mind:
1) Why do you need a key for this table?
2) Ownership should be a one to many relation: are you sure you need a special table, like for many to many relations?
3) Objects that belong to players are listed here. What other states can an object be in? (Lying around, NPC-owned, borrowed...)


I guess since I have done quiet a bit of DB work in my days I should answer some of this.

The number one rule of DB design; "Only store information once." DB normalization comes from that rule.

1. A few reasons I can think of; you can use the ID in your game to track the inventory instead of having to pass all the information around. Logging. Quick, moves and deletes in the table.

2. A table like this is how you do a one of the ways you do a one to many relationship. But it is not really a one to many, it is a many to many. There is no reason there is only one player.

3. For objects in other locations you would just have another table.

theTroll

Share this post


Link to post
Share on other sites
As for lightbringer's suggestion. Not a bad idea but many times it is over kill and not very practical for a newer designer or programmer. The problem is that many times a newer person won't know they need some information until they actually need it in the program. Yes, the design process is suppose to find that stuff but until you have enough experience it doesn't seem to go that way.

So just plug through it understanding that there is going to be a lot of changes before you make it to the end. What you think is a good design today will not even be close to what you have in the end.

theTroll

Share this post


Link to post
Share on other sites
And to help illustrate the post on normalization, here's a bit of an example.

If your table looks like this, then it isn't properly normalized. (It's [Row][Column] syntax on the left side)

Table [Inventory]
[1][1]: WeaponName = "A sword!"
[1][2]: WeaponDamage = 5
[2][1]: WeaponName = "A sword!"
[2][2]: WeaponDamage = 5
[3][1]: WeaponName = "A different, better sword!"
[3][2]: WeaponDamage = 10

The best way to break this down would be to have the following setup.

[Inventory]
[1][1]: WeaponId = 1;
[2][1]: WeaponId = 1;
[3][1]: WeaponId = 2;

[Weapons]
Weapon ID 1 (assumed to be Row 1 if you've started fresh with your records)
[1][1]: WeaponName = "A sword!"
[1][2]: WeaponDamage = 5;
Weapon ID 2 (assumed to be Row 2 if you've started fresh with your records)
[2][1]: WeaponName = "A different, better sword!"
[2][2]: WeaponDamage = 10;

Oh course, if you had more than one type of item for your inventory you would want to break it down further and add the appropriate tables for each type of item and add means to assign / determine which item table to pull the data from. This would also allow you to make another table entirely, like [Player Bank] and use these same IDs / assignments.

This method makes it really easy to modify separate tables without having to roll through an entire database having to change a weapon name because of spelling errors, or some similar situation.

Also, you may (or may not?) want to add redundancy backup in your tables, instead of Deleting records from the database. Add an IsDeleted boolean to every table, and make sure that you just set it to true / false as appropriate, so if you were to delete "A sword!" from most of the game's mechanics (say the dragon dropped them because it randomly cycled through all swords for what to drop when it died), if somehow there were players out there that still have "A sword!" in their inventory, the SQL won't bomb out trying to find a WeaponId that doesn't exist anymore. The dragon would then just be pulling all the records where "IsDeleted" is either null or false, and your players would still have their now removed from the queue swords :P

I hope I helped and didn't confused you ;) Good luck with it.

[Edited by - TogaMario on March 24, 2008 2:39:53 PM]

Share this post


Link to post
Share on other sites
Keep in mind that databases should really only be used for longer term storage. You don't want to query the DB on every frame obviously. However, unless you plan to have gobs of data to sift through, you would probably be better off with a simpler solution like storing data in flat files of your own design. That way, you don't require a server daemon.

Share this post


Link to post
Share on other sites
Quote:
Original post by leiavoia
Keep in mind that databases should really only be used for longer term storage. You don't want to query the DB on every frame obviously. However, unless you plan to have gobs of data to sift through, you would probably be better off with a simpler solution like storing data in flat files of your own design. That way, you don't require a server daemon.

Good god. The annoyance of another EXE on your computer pales next to the inefficiency and pain of maintaining a flat file for arbitrary data, particularly one you're going to be accessing so often. People often use databases even for very small amounts of persistent storage. They don't do this because it's enterprisey. They do it because it makes their lives easier.

Share this post


Link to post
Share on other sites
Quote:
Original post by DarkHorizon
Quote:
Original post by LorenzoGatti
Quote:

Table 'inventory':
int id;
int playercharacter_id;
int object_id;

Some trick questions spring to mind:
1) Why do you need a key for this table?
2) Ownership should be a one to many relation: are you sure you need a special table, like for many to many relations?
3) Objects that belong to players are listed here. What other states can an object be in? (Lying around, NPC-owned, borrowed...)


1) The database isn't the only location that an object's unique ID is used; it only makes sense to duplicate in the database tables what is stored on the gameserver.

I mean the "id" in the inventory table; searching by character id (what are his objects?) or by object id (is it owned? If it is, by what character?) are the only reasonable operations.
The id field allows the DB to distinguish between multiple ownerships of the same object by the same character, which doesn't seem useful.
A unique ownership id might become appropriate if you attach information to object ownership (reason for ownership, date of acquisition, expiration, etc.) because other tables could have relations with this one (e.g. a trader or maker of goods to his sales) and queries neither by character id or by object id might make sense (e.g. to find and purge objects acquired while an item duplication bug was in effect).
Quote:

2) I'm not sure; databases are a weak point in my knowledge, hence why I asked here :)

Allowing an object to coexist in multiple character inventories seems a very bad thing; you could ensure it doesn't happen with a UNIQUE constraint on "object_id", which would also help with indexing.
Then every object would have at most one owner character; it could be represented as a nullable field in the "object" table, but a separate "inventory" table improves normalization.
Quote:

3) I have loads of other stats that objects can have. I omitted them here for brevity's sake.

States, not stats. How can I search for object instances other than through a character's inventory? In map locations (lying around), in stores, in other items (containers)? The more possibilities there are, the more separate tables similar to "inventory" become attractive because they don't clutter "object" with optional data.

Share this post


Link to post
Share on other sites
@LorenzoGatti: I meant stats as in statistics. To answer your inquiry #3, I omitted these statistics (including the ones -- Lying around, NPC-owned, borrowed -- you asked about) because it was not the focus of my question.

@Leaivoia: This database IS intended for long-term (i.e. daily operations intended for potentially years of) storage. The gameserver reads necessary data at initialization time, dumps data X times daily and performs database maintenance Y times nightly, and generally communicates with the database as a long-term formatted storage system.

Thanks to all who answered my original question, I've got more than enough info to go on, and you've solidified some shaky areas of my db knowledge.

Share this post


Link to post
Share on other sites

This topic is 3549 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this