[dominik523]

Hey everyone! I tried to create a vehicle system in my script and I'm stuck with MySQL saving and loading.
Every player can have three vehicles at the same time and right now I've added 'Model', 'Color[2]', 'Paintjob[2]' and 'Float:Position[3]' to my VehicleData enum. I don't know what would be the best way to save all that vehicle data. I'll have to save every vehicle's mods eventually so there some even more fields.
I've come with these two options:
1) Save all vehicle data into one table without mods and create another table only for mods:

List goes on. There is also a model3 and so on.

2) One vehicle slot per row so I'll get more rows and less fields. Again there would be a mods table.


Does someone have any ideas on which method would be the best to use here?

[[XST]O_x]

Why are you saving the vehicle owner as an ID and not name? It won't work, because if a player is ID 0, he can reconnect as ID 1, 2 or 500 if other players has connected before him.

Secondly, I recommend that you save vehicles as vehicles, and not player vehicles. It would be easier to handle.
I mean, the leftmost field should be `vID`, which would contain the ID of the vehicle, ranging from 1 to MAX_VEHICLES, and then when you update the table you simply UPDATE `vehicles` SET `Model`=%d, `Color1`=%d... WHERE `vID`=%d

And your enum would look like:

pawn Code:

enum vehicleData
{
    Owner[ MAX_PLAYER_NAME + 1 ],
    Model,
    Color1,
    Color2,
    ...
}

new vData[ MAX_VEHICLES ][ vehicleData ];

And you would define a MAX_VEHICLES_PER_PLAYER somewhere. Would be much easier this way.

[Patchwerk]

http://prntscr.com/7zluyj

There's no reason to have tuning on another table.

[Mellnik]

Please https://sampforum.blast.hk/showthread.php?tid=420363

[dominik523]

Quote:

Originally Posted by [XST]O_x Why are you saving the vehicle owner as an ID and not name? It won't work, because if a player is ID 0, he can reconnect as ID 1, 2 or 500 if other players has connected before him. Secondly, I recommend that you save vehicles as vehicles, and not player vehicles. It would be easier to handle. I mean, the leftmost field should be `vID`, which would contain the ID of the vehicle, ranging from 1 to MAX_VEHICLES, and then when you update the table you simply UPDATE `vehicles` SET `Model`=%d, `Color1`=%d... WHERE `vID`=%d And your enum would look like: pawn Code: enum vehicleData {     Owner[ MAX_PLAYER_NAME + 1 ],     Model,     Color1,     Color2,     ... } new vData[ MAX_VEHICLES ][ vehicleData ]; And you would define a MAX_VEHICLES_PER_PLAYER somewhere. Would be much easier this way.

It's not a player ID from the server, it's an ID from the table so I can now which user is the owner of the vehicle.
I didn't understand you what you meant by ranging from 1 to MAX_VEHICLES. There's vID in the table as the primary key.

And my enum looks like this:

pawn Code:

enum vInfo
{
    ID,
    Model,
    Color[2],
    Paintjob[2],
    Float:Park[3]
};

new VehicleInfo[MAX_PLAYERS][MAX_PLAYER_VEHICLES][vInfo];

No need for string or anything, only an ID.

@Patchwerk:
Please read what Mellnik has posted.

[maddinat0r]

Method 2 is the best option. What if you want to quickly change the vehicle limit per player? What if you want to delete a vehicle from a player? Also queries (when using method 2) are more applicable.

[Cypress]

Quote:

Originally Posted by Patchwerk http://prntscr.com/7zluyj There's no reason to have tuning on another table.

That's terrible and should be avoided.

I've actually seen a MySQL tutorial around here on how to do it right, and as maddinat0r said, should be the best choice.

Also why do you need X, Y, Z values in your second table?

[dominik523]

Quote:

Originally Posted by Cypress That's terrible and should be avoided. I've actually seen a MySQL tutorial around here on how to do it right, and as maddinat0r said, should be the best choice. Also why do you need X, Y, Z values in your second table?

I want to save the spawn position of the vehicle so people can have their vehicles wherever they want.

[Cypress]

Quote:

Originally Posted by dominik523 I want to save the spawn position of the vehicle so people can have their vehicles wherever they want.

You don't need to add X, Y, Z values in the components table, just in the main table to save the vehicle's position.

[Jay_]

Quote:

Originally Posted by Cypress That's terrible and should be avoided.

It would be wrong to normalise a vehicle database table containing fields represented by vehicle modifications.

Patchwerk's post is correct: you do not need to split these off because the number of vehicle modifications is not a dynamic amount. There is always a maximum of 9. You'd only need to normalise this when there would be a dynamic amount of modifications, or the fields could potentially contain repeating data.

[Scenario]

Quote:

Originally Posted by [XST]O_x Why are you saving the vehicle owner as an ID and not name? It won't work, because if a player is ID 0, he can reconnect as ID 1, 2 or 500 if other players has connected before him.

The "ownerID" is probably the ID of the player's user account information in the database.

[Gammix]

What about attached objects?

[Jay_]

Quote:

Originally Posted by Gammix What about attached objects?

Yes, these would be split off into a separate table, referencing the vehicle id as the foreign key because the number of attached objects is a dynamic amount.

[maddinat0r]

Quote:

Originally Posted by Jay_ It would be wrong to normalise a vehicle database table containing fields represented by vehicle modifications. Patchwerk's post is correct: you do not need to split these off because the number of vehicle modifications is not a dynamic amount. There is always a maximum of 9. You'd only need to normalise this when there would be a dynamic amount of modifications, or the fields could potentially contain repeating data.

Not normalizing this is a waste of memory. Does every vehicle always has all 9 mod slots filled? No. So most of the time your "mod" fields are empty, thus having the value "0" -> repeating data.

[Jay_]

Hmm, okay yeah I suppose I can see your point about repeating data as it's less likely that a vehicle would have all 9 mod slots filled. However they wouldn't necessarily need to contain data or store the value of "0" if they where nullable columns.

The fact that it's only 9 rows containing very small amounts of data, personally I'd leave it in the one table. However you do make a good point and I can see why it's debatable.

[dominik523]

Well, yes. It's a waste of the memory to just add nine columns. But then again, smallint is just 2 bytes large. One user = 18 bytes of memory in the table. One thousand users takes 17.6 KBs of memory only for storing vehicle mods. In these days this isn't too much I think so it's not that much of a problem.

[Tamer]

In the manner of having the database organized (I am talking about making it organized, not optimized)

I'd go for making the tune data line by line for each vehicle, such as:

Vehicle db id | mod 1 | mod 2 | mod 3 | mod 4 | mod 5 | mod 6 | ...

This will make the database much more readable if you're using phpmyadmin, then you'll easily edit the table if you ever need to do it with phpmyadmin.

However if you're going to use a custom UCP or something then you can simply do it by:

Vehicle db id | Mod id

And then you'll keep inserting new modifications.

In that manner this tutorial is perfect for such tasks: https://sampforum.blast.hk/showthread.php?tid=420363

And it also covers what dominik is asking for real.

[Kar]

Just make full usage of your options.

I made my personal vehicle table years ago, so I didn't know about foreign keys then. Up to this day it only has 1.3k vehicles in it lol. Make sure you have restrictions of how much vehicles players can buy and you'll be set.