Hello everyone,

I'm currently working on a small project of mine - it's an MMO-lite RPG with a feature where players can pick different jobs, rank up, and get paid in-game. To keep track of all the job details and player progress, I'm planning to use MySQL. I'm using C++ for the game, and I've got some basic structs set up for the jobs.

I'm interested in hearing from those who have experience with similar systems. Specifically, I'm looking for best practices or tips on the following:

Database Schema Design: What would be the most efficient way to structure the database to handle a potentially large number of users and frequent updates to their job-related information?

Data Retrieval and Update: How can I ensure quick and efficient retrieval and updating of user job information to maintain smooth gameplay, especially in a multiplayer setting?

Scalability: How can I design the system to easily scale as the game grows in terms of user base and complexity of job roles?

Any advice or examples from similar projects would be greatly appreciated!

Thank you in advance for your help.

As for the schema, you should use the first three normal forms to normalize your database. That is, you need lots of foreign key references. Do you understand what I mean?

P.S. Also, it seems that you have a terminology problem.

You talk about databases storing information. That's the wrong way to look at it. Instead, databases store data.

Information (entropy) is a measure related to the data. It's a property of the data; it's metadata. Entropy (when using log_2) is basically the number of bits needed to encode 2^n messages. Like, an 8-bit integer can encode 2^8 = 256 different messages with equal probability, and so the entropy is n = 8. The entropy of course need not be an integer.

Did you have time/interest in the suggestions that I gave you?

Now that you've read about normalization and foreign keys, can you show your SQL code for creating the database? That way we can be sure that you're doing it the correct way.

Also, here is a code to calculate the entropy of a vector<T> in C++:

https://github.com/sjhalayka/qc_for_cs/blob/main/pd_10_3_2.cpp​ – see get_vector_binary_entropy() on line 330

Note that when the X messages are generally not equiprobable, like in this code, the entropy is less than the maximum log_2(X).

@taby Thanks a lot for the pointers on database normalization and the distinction between data and information. I'm planning to use your advice to set up my database schema and explore the concept of entropy with the C++ code you shared. I haven't set up a database or written any code for this particular feature of mine yet though.

jeremysakamaki said:
players can pick different jobs, rank up, and get paid in-game. To keep track of all the job details and player progress,

That usually isn't serialized. Sometimes part of it needs to be serialized, but often it is a count, or a flag for each job, or other, simpler data rather than a full log.

If you're tracking quests, it's a single row keyed to the player and the quest, or a collection of quests, but that's not typically stored in these games like that.

jeremysakamaki said:
What would be the most efficient way to structure the database to handle a potentially large number of users and frequent updates to their job-related information?

What is a “potentially large number”? In general, don't do that. Store data that you actually need for your game, don't store it if it isn't. If you need a count, store a count.

There are games where you might want to store a bunch of actions a player did, like a chess game recording every move, but that's not typical for the “MMO-lite” style game you're describing. Most likely you've got a single row for the player with their information of counters or tallies, and that single row needs an update.

Otherwise, what do you consider big? For real-world systems, multi-gigabyte databases aren't that big these days.

jeremysakamaki said:
How can I ensure quick and efficient retrieval and updating of user job information to maintain smooth gameplay, especially in a multiplayer setting?

The database access on your server takes place entirely outside the game. What you're describing isn't a big transaction, it is probably a single row upsert, so unless you're waiting on a lock or disk or something you're looking at a few microseconds. Communications latency with the database will probably be your bottleneck.

Build your system first. Figure out a way to make it work. Odds are you won't need to do much of an optimization pass in this area. What you're asking about usually isn't a problem in practice.

I forgot about the wonderful w3schools – https://www.w3schools.com/sql/default.asp​

There are sample codes for:

• creating a database – https://www.w3schools.com/sql/sql_create_db.asp
• creating a table with a primary key – https://www.w3schools.com/sql/sql_primarykey.asp
• creating a table with a foreign key – https://www.w3schools.com/sql/sql_foreignkey.asp​

Think of entropy as a way of measuring how informative the data are. If I send X messages, and they're all the same message, then that is not informative at all, and the entropy is log_2(1) = 0. Only with a multitude of messages can a non-zero entropy arise from the data.

I used the MySQL C++ library like 15 years ago, and I'm assuming that it's still awesome. This is why I'm recommending C++.

Here is a SQL script for a basic database:

CREATE DATABASE game;


use game;


CREATE TABLE player
(
    player_ID int NOT NULL,
    name varchar(255) NOT NULL,
    level int NOT NULL,
    PRIMARY KEY (player_ID),
    UNIQUE (player_ID)
);


CREATE TABLE item
(
    item_ID int NOT NULL,
    name varchar(255) NOT NULL,
    PRIMARY KEY (item_ID),
    UNIQUE (item_ID)
);


CREATE TABLE player_item
(
    player_item_ID int NOT NULL,
    player_ID int NOT NULL,
    item_ID int NOT NULL,
    PRIMARY KEY (player_item_ID),
    FOREIGN KEY (player_ID) REFERENCES player(player_ID),
    FOREIGN KEY (item_ID) REFERENCES item(item_ID),
    UNIQUE (player_item_ID)
);


-- Create some players
INSERT INTO player (player_ID, name, level)
VALUES (0, "taby", 1);

INSERT INTO player (player_ID, name, level)
VALUES (1, "jeremysakamaki", 1);


-- Create some items
INSERT INTO item (item_ID, name)
VALUES (0, "axe");

INSERT INTO item (item_ID, name)
VALUES (1, "lantern");

INSERT INTO item (item_ID, name)
VALUES (2, "shield");


-- Give player 0 an axe
INSERT INTO player_item (player_item_ID, player_ID, item_ID)
VALUES (0, 0, 0);


-- Look up items for player 0
SELECT name from item where item_id in (select item_id FROM player_item where player_ID = 0);

It's probably appropriate to ask how much experience you have with databases. I.e. do you need help at the level of how to represent game data at all, or do you want to hear if anyone with relevant experience have some tips that could come in handy?

Why are you not using PostgreSQL?

I sometimes daydream about dropping out of society for a while to become Batman^^write an MMO, and then I'd apply CQRS/EventSourcing to much of the data persistence. I.e. most of the data would be in a big append-only event log (with events such as “player gained xp” or “player picked up item” or “player completed quest” etc), and then the realtime queries would go to a projection that is built by processing each of those events and turning them into aggregate data such as the current player state/inventory etc. Those projections would be done with code in-memory, not in the database, and could be either rebuilt from scratch at startup, or accelerated by loading a snapshot of the projection up to a specific event and then only processing later events.

Event processing like this adds a lot of flexibility and robustness, as well as scale easily. Assuming your event log is correct, then just about any bug in your aggregate-state logic can be fixed, RETROACTIVELY, just by changing the code and then rebuilding the projection from scratch from event 0 and on. Or instead of a bugfix, it could be some feature (perhaps an achievement) that you are then able to apply retroactively because you have the full history of the game world.