PSO SQL Database structure - reworked

Chuk

Member
Working on restructuring the SQL structure of PSO BB. Mainly focusing on splitting the character_data blob into a more dynamic and clear structure.

I will post a draft of my reworked version in here when I get home.
 

tofuman

Administrator
Staff member
This is something we have been considering. Not that readability of backend data should take priority over efficiency. After all, data changes are better made with a frontend. We're considering restructuring the data across multiple tables and using joins. (as sql will suffer if you have 100's of columns in a table. it wont be noticeable small scale but when you get into the 10's and 100's of GBs it will. And we want to keep scalability in mind).
 

Sodaboy

Administrator
Staff member
I don't really think the SQL character data blob needs to be changed, really. Like said, you should just read the data into a structure and modify it with a front end, then write the whole data back to the structure when you're done with it.

We don't send small updates to the SQL structure for a byte here and there for character data on a regular basis, rather the whole character is backed up every few minutes or so and also when the player signs off.

If you broke down the structure and had to write your updates as a bunch of columns and such with many characters connected to the server, it really would be inefficient.

Feel free to prove me wrong, though. I just feel if you had a big server with hundreds connected, complicated writes and reads of the broken down structure regularly would slow things down. I would prefer to keep the structure lean and fast. Quickly send the data to the server, write and be done with it.

What do you need to break the structure down for anyway? Character editing purposes? New program, front end, read into a structure the same way Tethealla does, make your changes, write the whole structure back to SQL.
 

Chuk

Member
The problem with the datablob is you do a lot of unnecessary writes to the database, for example : challenge mode statistics, character proportions and customization, etc are very static parts that are rarely altered, yet get written to the blob every x minutes.

What is the thing we want to write most to the database? Inventories, banks, experience etc. By splitting the data into more logical parts we can seriously reduce the amount of data traffic between server and DB. Separate the saving process to be more efficient and to the point.

A side benefit from splitting it up is also to make it easier to work with the data outside of the core server. Like if you want to link stuff to a website, no need to rewrite the parsing of the data each time if you want to display some player data.

It also allows us to add more information that wouldn't fit in the blob before without messing up the offsets. ( A datablob is generally only used to store file data, like images, music etc. )

Don't get me wrong soda, your solution works well but to me it just feels it has more downsides than upsides. I'm not saying you are wrong, I just want to state my point of view since I think there can be a lot of improvement done on this part.
 

Sodaboy

Administrator
Staff member
Honestly, the challenge stats and what not are so small., them being backed up along with the other data is insignificant..

The inventory, banks, and quest flags (all of which change regularly) are what make up the bulk of the character data from what I remember... And they should always be backed up regularly... Full size.

I think its unnecessary to write different save functions for different parts of the data... It just makes things complicated for little gain.

If you want to reduce traffic between the server and the DB, then change the backup interval. There is already compression being used during communication between the servers, and I think it already reduces the amount of data sent quite a bit.

Just remember whatever changes you make, you need to account for different people's setups and if there is a catastrophic failure of hardware, software or power. Longer backup intervals without a UPS connected to the server or customizing saving too much and a crash occurs on with the server executable or hardware could cause major losses of data.
 

Chuk

Member
Ok so here is a quick (simplified) example of how the item storage could be handled :

item_SQL_structure.png


It's also possible to keep a Blob in the inventory, bank and common bank tables but I feel like that would be a limitation. The relations between the entities allow easy querying of the data. (I respected the cardinality)

NOTE: More stuff will be stored as a column inside chracter_data that was previously in the BLOB. Don't forget that SQL has been designed to work with rows and columns of data so don't get scared that this can result in a lot of separate data entities. Specific data can easily be fetched, updated and made into objects inside the core server this way.
 
Top