F
F
fortael2017-02-06 17:29:24
Database
fortael, 2017-02-06 17:29:24

What is the best way to organize the storage of game inventory in the database?

There is already a combat project, where there is a table of accounts and there is also inventory and other game indicators.
It looks like this: each item that exists in the game is a column with its quantity the player has.

spoiler
76d01496d1.png

There are 57 such columns and a dozen other indicators. Of course, such a base does not withstand scaling. Accounts periodically wipe, there are not very many of them in principle. Indexes don't help much. Structure + regular web hosting:
spoiler
0c2f4502f0.png(на другом проекте, где колонок в 3 раза меньше, а записей больше подобный запрос занимает 0.0002)

What is the best way to arrange an existing hierarchy in the database? There is an idea to make it in a separate table, where each item is a separate entry. According to the idea, writing a new one should be cheaper than updating the same one, but I'm not an expert to say. Like this:
1 belongs to Vasya in the amount of 3 pieces
3 belongs to Kolya in the number of 1 piece
1 belongs to Petya in the number of 4 pieces
8 belongs to Vasya in the number of 1 piece

But if, for example, a person will have all the objects - 200k accounts * 57 items (that's for now), will it give any increase at all or just more records?

Answer the question

In order to leave comments, you need to log in

2 answer(s)
S
Saboteur, 2017-02-06
@fortael

Why doesn't scalability hold up?
In projects like Lineage2 (online up to 5000, registered accounts up to 15000) - this is not a problem.
EACH item is a row in the items table, indicating the owner.
The fields are something like this:
item_id , owner_id, item_type, param1, param2, param3
item_id - unique item number
owner_id - owner ID, you can add a field for coordinates and storage time for those items that lie on the ground. Periodically, they can be deleted, and those with a storage time of 0 cannot be deleted.
item_type- ID of the item type, in a separate table, store all items and their invariable parameters (weight, material, name, description, restrictions on races, where to wear, all that)
param1...param10 - variable item parameters (sharpening, color, effects , broken condition)
For items like money, coins, bottles - you can make a column with the quantity, for other items it is always 1.
The position of the item (such as dressed, or in a backpack) can be done either in the same table, or in the table of the owner.
Actually it's not very clear what you mean when you think that it doesn't scale.

W
Wexter, 2017-02-06
@Wexter

1 table - a table of users with a unique id
2 table - a table of all possible items with a unique id, description and other necessary fields
3 table - stores the correspondence of the user id from t1, the id of the item from t2 and the amount of this item the user has.
Make a selection with a JOIN

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question