database design for browser based game [closed]

Question

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

Answer 1

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.