[Stefhan]

I got a couple of questions related to MySQL, that I can't seem to find on the internet, I was hoping someone could answer them for me, briefly or in detail.

1. When it comes to accounts, it seems unlikely that I should save everything in one table.

How do you guys suggest I split the tables? What should I put where? For example, password, name, id etc go well together, but should I have a seperate table for weapons, preferences and so on? How long should my tables get? And how would I go from making sure all the tables are linked up/loaded together correctly?

2. When saving data, should I save everything on OnPlayerDisconnect, or seperately when it gets changed? Currently, I have a forward/public that gets called at OnPlayerDisconnect which saves everything. Also, does that cause major issues? Saving a lot of stuff in one go? I imagine it would mess up with a GMX if it becomes too much.

3. Interesting statistics.
How do I go over saving interesting data such as how many players have joined my server on a day, how much time someone has played in total/over a week and so on? I've seen people have such tables but have yet to figure out how it's done, and I can't find any clear guides on it.

I will probably have more questions later on, but for now that's it. Happy New Year and thanks a lot for your help in advance. It's truly appreciated more than you think... Also, if I wasn't ment to post this here, since there's no code involved, I'm extremely sorry.

[PowerMwK]

You can find in the official wiki, the better way.

https://sampwiki.blast.hk/wiki/MySQL/R40

[Stefhan]

Quote:

Originally Posted by PowerMwK You can find in the official wiki, the better way. https://sampwiki.blast.hk/wiki/MySQL/R40

I know and I've used this to learn how stuff works, but that's all it does. It explains how something works. It doesn't tell me what is the best thing to do.

[Calisthenics]

Quote:

Originally Posted by Stefhan 1. When it comes to accounts, it seems unlikely that I should save everything in one table. How do you guys suggest I split the tables? What should I put where? For example, password, name, id etc go well together, but should I have a seperate table for weapons, preferences and so on? How long should my tables get? And how would I go from making sure all the tables are linked up/loaded together correctly?

Correct. Split them just like you said: player main data, weapons, preferences etc. A good table design should not have more than 10-15 columns. Link the child-table with FOREIGN KEY to main players (parent) table. After a player is successfully logged in, you know their registration ID so you do not even need JOINs.

Quote:

Originally Posted by Stefhan 2. When saving data, should I save everything on OnPlayerDisconnect, or seperately when it gets changed? Currently, I have a forward/public that gets called at OnPlayerDisconnect which saves everything. Also, does that cause major issues? Saving a lot of stuff in one go? I imagine it would mess up with a GMX if it becomes too much.

No, not everything at once. Save data when they change and save on disconnect those who do not.

Quote:

Originally Posted by Stefhan 3. Interesting statistics. How do I go over saving interesting data such as how many players have joined my server on a day, how much time someone has played in total/over a week and so on? I've seen people have such tables but have yet to figure out how it's done, and I can't find any clear guides on it.

A table that stores connect and disconnect time for players. Using aggregate functions can allow you to count distinct players in a specific timeline or you can count the total gameplay time of a player.

[AjaxM]

1. Yes, you could save all the player data in a single table but it would make it bulky and it'd take you long to browse your table. You better have different tables for different datas such as player weapons, player vehicles and all. You could link them all with the player name or the player ID (foreign key - which is better) and fetch them all once player log in. The ID should be on the main table where essential data of the player is stored.

2. Yes, you could save them OnPlayerDisconnect but I personally prefer saving players' data at an interval time (every 5 minutes). Server might not get the data such as "GetPlayer_" when the player disconnects.

3. Yes, you could have a separate table for that, even though there will be like only 1 row. I do that myself and it is much better than having a file (INI). So, when the server starts, check if the table is created and if it is not, create it and automatically add a row with the values that you would want ; for example, server weather having the ID 3.

- AjaxM.

[Stefhan]

Thank you everyone for the advice.

[BlackyX]

Quote:

Originally Posted by Stefhan 1. When it comes to accounts, it seems unlikely that I should save everything in one table.

EVERYTHING IN ONE TABLE. No more, no less. Everything related to an account should be saved in the same table(ofc you can use a separate one for every single thing, but then good luck sorting your things)

Quote:

Originally Posted by Stefhan 2. When saving data, should I save everything on OnPlayerDisconnect, or seperately when it gets changed? Currently, I have a forward/public that gets called at OnPlayerDisconnect which saves everything. Also, does that cause major issues? Saving a lot of stuff in one go? I imagine it would mess up with a GMX if it becomes too much.

OnPlayerDisconnect is your best choice if you ask me. And, no, it won't cause issues, it will just be a bit slower. But trust me, I've used scripts that save a ton shit of things into mysql and still didn't take much to GMX so you're pretty much fine.