1
\$\begingroup\$

I'm wondering how to design a database for the player's experience, whether to create a single table that will collect basic information such as. Class, race, number of experiences, number of occupied bases, etc. Or whether it would be more ideal to divide it into individual databases

\$\endgroup\$
  • 1
    \$\begingroup\$ Can you add more detail to your question? What data exactly do you want to persist to your database and how is it structured? How exactly does the schema look which you plan to use? Keep in mind that we know absolutely nothing about your game, except that it plays in a web browser. But in general, an application which uses SQL usually uses one single database with multiple tables for different kinds of records. \$\endgroup\$ – Philipp May 11 at 7:48
  • \$\begingroup\$ My mistake is a single database. I wrote it wrong, but to add to it. I have a player table in the database. And I'm thinking about whether to store all the basic data in this table or go the way of more tables. As I have already stated as basic data, I know the science, game name, experience, number of bases, race, specialization, etc. \$\endgroup\$ – martin suchodol May 11 at 9:00
  • \$\begingroup\$ You are repeating yourself but not adding any further information. How would that table look exactly? What alternative data model do you envision and how exactly would that look? Is "number of controlled bases" really just an integer? I would expect bases to be a separate entity stored in a separate table and the "number of controlled abses" to be derived by queriying that table (SELECT COUNT(*) FROM bases WHERE controlling_player=<id>) \$\endgroup\$ – Philipp May 11 at 9:02
  • \$\begingroup\$ Maybe you need a book about how to design relational databases to get a better idea of what you are doing? Or maybe just read the Wikipedia article? \$\endgroup\$ – Philipp May 11 at 9:04
  • \$\begingroup\$ Different games will use different database setups depending on their particular needs. In order to give you good answers for your game, we need details about your game and the particular problems you're running into with the database design you've tried so far in your testing. Please edit the question to include this detail, and we can re-open it for answers. \$\endgroup\$ – DMGregory May 11 at 14:36
0
\$\begingroup\$

While this does not answer most of the specifics related to your game, a lot of similar questions will be answered by learning about database normalization.

It is a large topic, but it explains how to avoid all kinds of design blunders (like having a massive table with more and more fields as your object grows). For example, you have a table of players, and you want to record their inventories:

+-----------+-------------+--------------+--------------+------
| Player ID | Player Name | Player Level | Player Items | .....
+-----------+-------------+--------------+--------------+------
| 000000001 | Robert      | 3            | Item1, Item2 | .....
+-----------+-------------+--------------+--------------+------
| 000000002 | George      | 50           | Item3, Item1 | .....
+-----------+-------------+--------------+--------------+------

Like this the Items field will be requiring extra parsing and can bloat needlessly.

A first level normalization can turn this into

+-----------+-------------+--------------+---------------+---------------+---
| Player ID | Player Name | Player Level | Player Item 1 | Player Item 2 | ..
+-----------+-------------+--------------+---------------+---------------+---

but it will be still hard to maintain if you decide you want a bigger inventory.

So you go next level instead and make a separate table of item/inventory entries:

+----------+-----------+--------+
| Entry ID | Player ID | Item   |
+----------+-----------+--------+
| 00000001 | 000000001 | Item1  |
+----------+-----------+--------+
| 00000002 | 000000001 | Item2  |
+----------+-----------+--------+
| 00000003 | 000000002 | Item3  |
+----------+-----------+--------+
| 00000004 | 000000002 | Item1  |
+----------+-----------+--------+

By consulting this table you can easily see which items belong to Robert or George's character. The Entry ID column might be unnecessary if you can ensure that there are no duplicate combinations of Player ID and Item in the table - you can for example allow multiple of the same item by adding an "amount" column.

A second thing that is really useful specifically for games is called Entity-Attribute-Value system. It is used when you have a lot of different kinds of "things" that you nonetheless want to store in the same database - inventory items are again a very good example.

Why?

Here are three very common RPG style items:

Item 1:

Name: Health Potion

What it does: restore health

How much? 50

How much does it cost? 100 gold pieces.

Item 2:

Name: Magic Sword

What it does: nothing, but you can equip it.

If equipped, your character's attack is increased by 10. Also, they dodge hits better now. Also, they have a chance of setting their enemy on fire.

How much does it cost? 500 gold pieces.

Item 3:

Name: Carrot

What it does: nothing, it is a quest item.

Who wants it: the rabbit in the town of rabbits.

How much does it cost? as a quest item it is literally priceless.

Our task is to store these items in the database. Clearly, they should all be stored in the same table, that will later be referred to by our inventory table - but we cannot really store all these vastly different properties in just ONE table. It does not make sense for a sword to have store a number for how many health points it can recover if you drink it. The quest item has no price, and the potion does nothing when equipped. The sword has 3 different but similar things that happen when it is equipped. Total chaos.

What you can do then is to create a table of "things" - in this case, items - and store some bare minimum in that table. Usually, one stores an ID to identify the specific thing uniquely, and an extra field roughly specifying the "type" of a thing, perhaps some other common parameters that are often used - maybe icon ID or the "rarity" of an item - so you can draw a nice little border around the rare and legendary ones.

All the rest are in their own separate table, a kind of a key-value store:

+----------+---------+------------+--------+
| Entry ID | Item ID |  Attribute | Value  |
+----------+---------+------------+--------+
| 00000001 | Item1   | health_up  | 50     |
+----------+---------+------------+--------+
| 00000002 | Item2   | atk_equip  | 10     |
+----------+---------+------------+--------+
| 00000003 | Item2   | evasion_up | 10     |
+----------+---------+------------+--------+
| ........ | .....   | .........  | ....   |

You get the idea. Now all you have to do to "reassemble" your item from the database is to look in your attributes and retrieve all the ones that have Item ID same as the item you're working with.

| improve this answer | |
\$\endgroup\$

Not the answer you're looking for? Browse other questions tagged or ask your own question.