Database design: Why is each skill level and tries listed in separate columns in the player table?

[Sam Drost]

Just curious about this because normally, I would expect those to be in a separate table with a reference to the player table and the skill type, but I am no expert, and want to know if there is a reason for that.

It would seem to me that having it in a separate table would ease the ability to change the skill list, since you would not have to change the schema of the database to do so.

 

[Fox Rother]

Just curious about this because normally, I would expect those to be in a separate table with a reference to the player table and the skill type, but I am no expert, and want to know if there is a reason for that.

It would seem to me that having it in a separate table would ease the ability to change the skill list, since you would not have to change the schema of the database to do so.

I think your reasoning makes perfect sense. Maybe it's been done like that to minimize the number of queries necessary to load a player in and out of the server memory.
I've read somewhere that every time a player logs in or out, its data need to be loaded from/to the database.

 

[Sam Drost]

I think your reasoning makes perfect sense. Maybe it's been done like that to minimize the number of queries necessary to load a player in and out of the server memory.
I've read somewhere that every time a player logs in or out, its data need to be loaded from/to the database.

That had been my initial thought as well, but looking at the code there is already more than one query being made on player login, and this change would only require one extra query. So I was curious if there was another reason.

 

[averatec]

Maybe, but it's only my blind guess, it's like that because it's easier to gather data from every skill in terms of AAC.
Imagine situation when you'd like to show on the same list e.g. level, mlvl and other skills.
When you have table like that:
player_skills:

• player_id
• skill_type
• etc...

You have to join this data somehow, it's little bit tricky.
Instead of one clear query you will end with messed up query.
There're no new skills in Tibia since I can remember, so solution with separate column for every skill in players table seems okay for me.

I'm sure that that performace is better when you're updating just one row, instead of N+1 where N is number of skills and please remember that player_skills table consists of N*M rows where M is amount of players.

In the past, I'm pretty sure there was player_skills table.

 

[Fox Rother]

@Sam Drost opentibia/server (https://github.com/opentibia/server/blob/0178e62a12ac4522de61b99a11406faf7f5ee5d7/src/schema.mysql#L170)
This is the legacy OpenTibia Server's MySQL schema and it does have a player_skill table indeed. You can tell it is setup very inneficiently hehe.
I think you were thinking of a skill table with every skill in a single record, right? That would be a sweet-spot for customization, but not sure it was ever done like that.
I guess the cost of adding columns is negligible for such a small table, even if you add a dozen of them

 

[Sam Drost]

@Sam Drost opentibia/server (https://github.com/opentibia/server/blob/0178e62a12ac4522de61b99a11406faf7f5ee5d7/src/schema.mysql#L170)
This is the legacy OpenTibia Server's MySQL schema and it does have a player_skill table indeed. You can tell it is setup very inneficiently hehe.
I think you were thinking of a skill table with every skill in a single record, right? That would be a sweet-spot for customization, but not sure it was ever done like that.
I guess the cost of adding columns is negligible for such a small table, even if you add a dozen of them

No, that design is exactly what I was thinking of, which is the way I would design it with purely relational design as a consideration (except I would have added PRIMARY KEY (`player_id`,`skillid`)). So, my question, because I am trying to learn concepts of database/software design, is why was it changed to the current schema?

As far as query speed because of so many skill rows per player is concerned, I routinely run queries joining 800,000 and 1,000,000 row tables with MySQL, and, while not fast enough for game, it has no problem handling data in that volume (as long as I make sure I am using fields that are properly indexed. It crashes and burns when I run a query that is not properly indexed, but that is a fault of the index design). This is on a computer that is not a dedicated server, so MySQL is set up as a development install so that it doesn't use as much memory. I can't imagine that the few tens of thousand rows that would be sufficient for the largest server I could imagine, would be a breeze for MySQL. Of course, maybe I am underestimating the popularity of any single OTS, but at that point, I would think they would have a dedicated MySQL server.

 

[Fox Rother]

No, that design is exactly what I was thinking of, which is the way I would design it with purely relational design as a consideration (except I would have added PRIMARY KEY (`player_id`,`skillid`)). So, my question, because I am trying to learn concepts of database/software design, is why was it changed to the current schema?

As far as query speed because of so many skill rows per player is concerned, I routinely run queries joining 800,000 and 1,000,000 row tables with MySQL, and, while not fast enough for game, it has no problem handling data in that volume (as long as I make sure I am using fields that are properly indexed. It crashes and burns when I run a query that is not properly indexed, but that is a fault of the index design). This is on a computer that is not a dedicated server, so MySQL is set up as a development install so that it doesn't use as much memory. I can't imagine that the few tens of thousand rows that would be sufficient for the largest server I could imagine, would be a breeze for MySQL. Of course, maybe I am underestimating the popularity of any single OTS, but at that point, I would think they would have a dedicated MySQL server.

It probably changed for performance and/or ease-of-use, I guess.
What you also must consider is that the current TFS architecture is not very scalable.
A single server has to handle its own state, communication to hundreds of clients and a database.
For that reason, you wouldn't want your CPU waiting for the disk for too long.

All guesses though. And they only make sense considering the current player loading strategy.
Quick edit: @Znote made a very interesting suggestion on that [suggestion] Different ways to load and unload players (https://otland.net/threads/suggestion-different-ways-to-load-and-unload-players.265974/)

 

[Red]

@Sam Drost As @Fox Rother had mentioned, player_skills used to be a completely separate table until TFS 1.0. You can see Mark’s commit and rationale: Move skills into players table · otland/forgottenserver@e801489 (https://github.com/otland/forgottenserver/commit/e8014897ed2100c2a43f320b82d58763b56b4463#diff-cbc8e977ae1dcf55f32f862e21e4aa34)

Red

 

[Sam Drost]

@Sam Drost As @Fox Rother had mentioned, player_skills used to be a completely separate table until TFS 1.0. You can see Mark’s commit and rationale: Move skills into players table · otland/forgottenserver@e801489 (https://github.com/otland/forgottenserver/commit/e8014897ed2100c2a43f320b82d58763b56b4463#diff-cbc8e977ae1dcf55f32f862e21e4aa34)

Red

Awesome. Thanks for that. Just out of curiosity, was keeping the cleaner design and reducing the player save down to a single query using cases considered?

UPDATE `player_skills`
  SET `count` = (CASE WHEN skillid = %fist skill id% THEN %fist skill count%
                      WHEN skillid = %axe skill id% THEN %axe skill count%
                 END)
  WHERE player_id = %player id%

Or even

INSERT INTO `player_skills` (player_id, skillid, value, count)
  VALUES
    (%player_id%, %fist skill id%, %fist skill level%, %fist skill tries%),
    (%player_id%, %axe skill id%, %axe skill level%, %axe skill tries%)
  ON DUPLICATE KEY
    UPDATE value = VALUES(value), count = VALUES(count)

Which would take care of any missing rows, should there happen to be any for whatever reason.

 

[Gesior.pl]

Awesome. Thanks for that. Just out of curiosity, was keeping the cleaner design and reducing the player save down to a single query using cases considered?

UPDATE `player_skills`
  SET `count` = (CASE WHEN skillid = %fist skill id% THEN %fist skill count%
                      WHEN skillid = %axe skill id% THEN %axe skill count%
                 END)
  WHERE player_id = %player id%

Or even

INSERT INTO `player_skills` (player_id, skillid, value, count)
  VALUES
    (%player_id%, %fist skill id%, %fist skill level%, %fist skill tries%),
    (%player_id%, %axe skill id%, %axe skill level%, %axe skill tries%)
  ON DUPLICATE KEY
    UPDATE value = VALUES(value), count = VALUES(count)

Which would take care of any missing rows, should there happen to be any for whatever reason.

No. OTSes were made by programmers. C++ programmers. Not 'database programmers'. Versioning and updating database schema (with procedures, triggers etc.) is hell when you compare it to C++ update. Tibia changes over years, OTS engine code must be flexible and easy to maintain.
Also databases specification and implementation changes over years. C++ functions are more stable.

Few years ago OTS engine was compatible with MySQL, PgSQL and SQLite - same code, just change in config.lua type of connection. Try to make some update, when part of game logic is stored in these databases and your code must be compatible with all these database specifications.

EDIT:
In my personal opinion, procedures and triggers are cancer made by 'database administrators', because they felt useless and wanted to do something important and critical for their companies.