Best practices in a database

Hello,

I am developing an online multiplayer game and my doubts arise with the database. Assuming I have the following scenario:
[character]
id, username, password

[items]
id, name, power, etc,

[invent]
slot

What is the correct way to go? Should I have one database for character, another for items, and another for inventory, or have a single database for the entire game and divide it by tables only?

PS: I am using MySQL as a database.

Thank you and sorry for my English.
Best regards.

 

This is a really good question. Generally, with SQL database design, you start with one big database with a lot of tables for all the objects in your database.

I believe that your question above is asking about what SQL calls linking tables to show the relationship between the two types of objects (your character and your items object types). Here is an example on how I would store the inventory of a character based on your tables above:

Code (csharp):

1.  
2. [character_items]
3. characterID, itemsID, inventory_position, hotbar_position, is_active_bool
4.  

The character_items table would represent the inventory of your character's items. You could name the table inventory, but good database design recommends linking tables to be the names of the two tables which they link, and I generally follow that pattern in my own database design.

You would then use a sql join statement in your query to get all of the details back from your character_items table in the future. It would look something like this:

Code (csharp):

1.  
2. select
3.   *
4. from
5.   character,
6.   items,
7.   character_items
8. where
9.   character.id = 123
10. and
11.   character_items.characterID = character.id
12. and
13.   character_items.itemsID = items.id
14. ;
15.  

The above example assumes you've setup foreign keys in your character_items table when you created it.

 

Hi. Thank you very much for your explanation. now I have the same doubt but about the world. If the players have the possibility to interact with it, so that events like the growth of a tree are reflected for everyone, the growth time of the tree should be managed from the database from what I understand, correct?

Thanks You.
Best regards.

 

You can certainly keep track of all the objects in a world in your database -- but for something that is constantly changing (such as the growing of a tree), your database will melt down with a small forest if you are trying to keep all of their growth metrics tracked in real time.

What I've seen is that anything that is a "transaction" such as buying an item from a store, or wearing a hat on the player's head (which moves the hat from the player's inventory over to the player's avatar) gets stored in the DB.

For things that update all the time -- their "current" status gets serialized to a save file (or save database) when the server stops or a region gets unloaded from memory, and gets reloaded into memory when the server starts back up or a player gets close to the region. Then it is the server's job is to keep all the trees growth numbers in memory and then whenever a player gets within the area of interest of that tree (meaning that the player could possibly see or interact with the tree), the server will "fast forward" the tree to what it should look like right now.

This keeps you from having to talk to the database every second for every tree, and it keeps you from having to bog down your server with constant tree growth updates.

You still have to serialize *everything* that a player can interact with out to some kind of data store (database or a flat file) whenever the server stops or a region is unloaded, but the data store doesn't necessarily have to be a sql database when you don't need to do powerful queries across the database, or you don't need transaction integrity.

 

You solved all my doubts
Thank you very much for your time and attention. I have to dig into databases.

Thank you very much and Happy new year!