# Database storage

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

## 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;

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 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 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 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 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 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 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 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 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 on other sites
Quote:
 Original post by leiavoiaKeep 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.

1. 1
2. 2
3. 3
4. 4
5. 5
Rutin
18

• 11
• 12
• 9
• 12
• 37
• ### Forum Statistics

• Total Topics
631420
• Total Posts
2999990
×