[PowerPC603]

Hi guys,

I'm in need of some input about MySQL tables.
I currently have 26 columns in my "playerdata" table and alot more are coming.
This table holds these fields:

Код:

ID - Unique ID to identify each player in the database
Name - The name of the player
Password - His SHA512 hashed password
RegisterDate - The date and time he registered his account (2014-12-05 09:17:34 for example)
IP - The IP of the player
AdminLevel - The admin-level (values ranging from 0 to 6)
SilentLogin - 1 or 0 to indicate an admin can login silently (won't display "Admin xxx has entered the server" if set to 1)
UnbanTime - The timestamp where a ban is ended
BanReason - The reason for a ban
Money - The money of the player
Score - The scorepoints of the player
Fines - The total value of the player's fines (when caught speeding)
Tickets - The amount of tickets the player has
TicketCost - The total value of his tickets (fines get converted into tickets when a cop catches a player which has any fine open)
SpeedInMph - If set to 1 displays the speedometer in mph, 0 for kph
FuelReadout - The readout setting for fuel (0 = bars, 1 = percent, 2 = liters, 3 = gallons)
PoliceStation - Holds the ID of the police station the player is located (used when a player is jailed)
JailTime - The amount of seconds remaining when the player is jailed
FreezeTime - The amount of seconds remaining for the frozen player
CompanyID - The id of the company in which the player is registered
CompanyAccessLevel - The accesslevel in the company which determines his rights for certain things (a level 1 member can't kick someone for example)
CompanyContribution - The amount of experience this player contributed to his current company
CompanyJoinDate - The date and time this player has joined the company (2015-03-24 00:22:34 for example)
CompanyJoinTime - The timestamp when this player joined the company

Alot more columns would be added later on such as amount of jobs finished in each class (trucker, busdriver, pilot, courier, traindriver, ...) and several other things as well.

I could end up with over 40 columns easily.

My question:
Would it be better if certain data is separated from the main playerdata tables?
Like one table for ban-info, another for company-info, another one for finished jobs, and another for settings (like the fuelreadout, silentlogin, speedinmph, ...).
And keep the default table for the basic data such as his IP, money, score, experience, name, id, registerdate, ...


Is there a certain limit where it isn't acceptable anymore to hold all data in one table?

It's not that I'm creating columns like Jobs1, Jobs2, Jobs3, ... or anything like that.


Using one table has some advantages (all data is properly organised into one single table and can be read easily using a simple query).
Using multiple tables would make the queries alot longer and more complex.

[Abagail]

For something's such as the ban information, previous arrests, jail information / past jails etc they should, in my view, be separated.

[PowerPC603]

Would if be beneficial to add a new record in a separate table to store every arrest/jail for every player?
It may be interesting for statistics, but has no further use beyond that.

I've already split up the table to:
- store every ban for each player in a separate table (which will be scanned first upon logging in to determine if a player is still banned)
- company-member info is also a separate table now

I would still need some jail info in the main table as this is needed for the current status of a player.
The new jail table would only hold info about previous jail-times and such so it could be easy to track who was jailed before, when it happened and for how long he was jailed.
Perhaps even the reason for jailing could be recorded.

I'll see further what else could become a separate table for logging purposes.

Would is also be beneficial to have a separate table to record every action the player does that earns him money or spend it? Or scorepoints?
Maybe it could be used by admins to follow a player's money in case someone claims he has been a victim to a bug or whatever, where his money is suddenly gone.

I guess that would take it a little too far.

[Vince]

Anything that is not directly related to the player or that does not apply to all players should not be in the player table. For example, I don't think every player is in a company. So what you need is a third table, known as an association entity. Call it "companyMembers" or something. This entity then consists of atleast a) userid and b) companyid. The extra information you need (joindate and so on) may also be in that table.

Likewise, not every player is banned (luckily) so that information also shouldn't be in the player table.

[PowerPC603]

Yeah, I've created those 2 tables already.

One table that holds all info about banned players (playerid, playername, IP, bantime, unbantime, reason and bannedby), and one table that holds all info about players who are in a company (playerid, companyid, joindate, accesslevel, contribution and jobs completed).

I also planned for an extra table that holds the level, experience points and completed jobs per class (about 15 classes) for every player, so that's 45 columns (46 with the added playerid column).
These are actually connected to each player as every player has the ability to change classes whenever they want and do jobs there (earning experience and gain levels), but then the playerdata table would become too big if these columns would be in the main playerdata table.

I'll also create an extra table for playersettings, such as the fuel readout, speed readout, silentlogin and such things that are actually connected to every player, but need to be adjusted manually by the player himself using the "/settings" command.

[Vince]

Quote:

Originally Posted by PowerPC603 I also planned for an extra table that holds the level, experience points and completed jobs per class (about 15 classes) for every player, so that's 45 columns (46 with the added playerid column).

This, again, would require an associative table that holds 5 fields: classid, userid, level, experience, numcompleted
primary key (classid, userid)

[PowerPC603]

This may even be better, as not everyone plays every class.
Less columns, but more records.

Good idea, thank you.

Soon I end up with 50 tables for one script.