SA-MP Forums

Go Back   SA-MP Forums > SA-MP Scripting and Plugins > Scripting Help

Reply
 
Thread Tools Display Modes
Old 27/03/2020, 09:10 PM   #1
KamilPolska
Huge Clucker
 
Join Date: May 2016
Location: Poland
Posts: 477
Reputation: 5
Default Identifiers ID

Hello. For a long time I have a confusion on how to use and efficiently with the latest version of mysql blueg. How do you make systems with IDs? Do you have to add AUTO_INCREMENT to each system or can you do it without AUTO_INCREMENT?
KamilPolska is offline   Reply With Quote
Old 28/03/2020, 09:59 AM   #2
Symon
Gangsta
 
Symon's Avatar
 
Join Date: Apr 2019
Location: Italy
Posts: 867
Reputation: 69
Default Re: Identifiers ID

You can use AUTO_INCREMENTfor everything you need. For houses, vehicles, players accounts and so on.
__________________

Join The Server (play.symonscnr.com) | Discord | Website/Forum
Choose up from 5 different skills, Con Artist, Drug Dealer, Firefighter, Hitman and Terrorist.
Play as a Civilian or a LEO, bomb buildings/vehicles, rob shops or bank and many many more. Join us!
Symon is offline   Reply With Quote
Old 28/03/2020, 10:11 AM   #3
Calisthenics
High-roller
 
Join Date: May 2018
Posts: 1,229
Reputation: 205
Default Re: Identifiers ID

Quote:
Originally Posted by KamilPolska View Post
Do you have to add AUTO_INCREMENT to each system or can you do it without AUTO_INCREMENT?
No, it is not necessary to have an increment ID if you can uniquely identify records with other columns (composite primary key).

For example you have a table with attachments. A primary key (userid, slot) can be enough to select all slots by certain player or certain slot for player.
__________________
Calisthenics is offline   Reply With Quote
Old 28/03/2020, 11:21 AM   #4
jasperschellekens
High-roller
 
jasperschellekens's Avatar
 
Join Date: Dec 2016
Location: Holland
Posts: 1,014
Reputation: 92
Default Re: Identifiers ID

Quote:
Originally Posted by Calisthenics View Post
No, it is not necessary to have an increment ID if you can uniquely identify records with other columns (composite primary key).
This is absolutaly true. It really depends on the kind of table you are creating.
For example user accounts, you will want to use auto increment so each account has it's own unique ID which it can be identified with. When it comes to a table for example the player's inventory, you don't really need add an auto increment column but instead you go for an column that would in this case hold the user account ID.

Code:
CREATE TABLE `ACCOUNTS` ( 
`ID` INTEGER PRIMARY KEY AUTOINCREMENT, 
// Other stuff
)

CREATE TABLE `INVENTORIES` ( 
`ACCID` INTEGER, 
// Other stuff
)
__________________
Coming soon (6-1-2020):

Undead Wasteland Roleplay - The year is 1996. San Andreas is a wasteland ruled by the undead. the once so glorius landscape of San Andreas, is now a shattered decayed place that is only a vague memory of the world as we used to know it.
Official Live Beta Release Date: 1st of June, 2020
Discord - Website
jasperschellekens is offline   Reply With Quote
Old 28/03/2020, 11:57 AM   #5
Symon
Gangsta
 
Symon's Avatar
 
Join Date: Apr 2019
Location: Italy
Posts: 867
Reputation: 69
Default Re: Identifiers ID

Quote:
Originally Posted by jasperschellekens View Post
This is absolutaly true. It really depends on the kind of table you are creating.
For example user accounts, you will want to use auto increment so each account has it's own unique ID which it can be identified with. When it comes to a table for example the player's inventory, you don't really need add an auto increment column but instead you go for an column that would in this case hold the user account ID.

Code:
CREATE TABLE `ACCOUNTS` ( 
`ID` INTEGER PRIMARY KEY AUTOINCREMENT, 
// Other stuff
)

CREATE TABLE `INVENTORIES` ( 
`ACCID` INTEGER, 
// Other stuff
)
That's what i was talking about.
__________________

Join The Server (play.symonscnr.com) | Discord | Website/Forum
Choose up from 5 different skills, Con Artist, Drug Dealer, Firefighter, Hitman and Terrorist.
Play as a Civilian or a LEO, bomb buildings/vehicles, rob shops or bank and many many more. Join us!
Symon is offline   Reply With Quote
Old 28/03/2020, 01:36 PM   #6
KamilPolska
Huge Clucker
 
Join Date: May 2016
Location: Poland
Posts: 477
Reputation: 5
Default Re: Identifiers ID

I understand good. That I use auto increment for player accounts. For other systems I do not want to use auto increment because then I will want to delete something such as ID 4 so it can be 1, 2, 3, 5, 6. Is this a good house system create table mysql?
Code:
CREATE TABLE IF NOT EXISTS `houses` (
`hID` int(11) NOT NULL,
`Owner` varchar(24) DEFAULT 'Not owner',
`Owned` tinyint(1) DEFAULT '0',
`Locked` tinyint(1) DEFAULT '0',
`Price` int(11) DEFAULT '0',
`InteriorE` int(11) DEFAULT '0',
`InteriorI` int(11) NOT NULL DEFAULT '0',
`ExteriorX` float DEFAULT '0',
`ExteriorY` float DEFAULT '0',
`ExteriorZ` float DEFAULT '0',
`InteriorX` float DEFAULT '0',
`InteriorY` float DEFAULT '0',
`InteriorZ` float DEFAULT '0',
PRIMARY KEY (`hID`))
KamilPolska is offline   Reply With Quote
Old 28/03/2020, 02:21 PM   #7
Calisthenics
High-roller
 
Join Date: May 2018
Posts: 1,229
Reputation: 205
Default Re: Identifiers ID

Why do you care about gaps? I have said it before, ID columns are only there to know which row to update/select from. In this case, `hID` can be and should be auto increment rather than finding which row to fill just for making it to look 'better'. Players do not care if a house ID is 6 or 1000, they just want to buy the house.

As for the design, `Owner` should be foreign key from player accounts table. If you want to know the name of the owner, you can JOIN the tables. If you allow name changes, you will be forced to update EVERY table you store the name of player (again, more work for you). When using foreign keys, there must be an index for userid (it is automatically created in most cases) so searching the houses of a player is also efficient.

If `Owner` is NULL, then it can mean that house is not owned.

Is exterior always at interior ID 0? If it is, you do not need extra column to store it. Is max interior ID 18? TINYINT which can store 0-255 is a good option.
__________________
Calisthenics is offline   Reply With Quote
Old 28/03/2020, 02:34 PM   #8
KamilPolska
Huge Clucker
 
Join Date: May 2016
Location: Poland
Posts: 477
Reputation: 5
Default Re: Identifiers ID

I understand. As for hID, I want to do it without Auto_Increment because if I have hID at 1, 2, 3 and I want to delete hID 2, then next time I create a home, I want it to find free hID, i.e. 2, and this is not auto_increment because there would be 1 ,3, 4 that's why I don't want to use it and it bothers me a lot.. Because I want the game to look hID in order of count, e.g. we have 10 houses, there should be another 1, 2, 3, 4, 5 to 10 in the game. Even if I want to remove a house, I want it to find the missing hID. So I have done so when creating a home with hID
Code:
GetHouseID()
{
	for(new i = 1; i < MAX_HOUSES; i++)
	{
		if(!hInfo[i][hIDUsed])
			return i;
	}
	return MAX_HOUSES;
}
KamilPolska is offline   Reply With Quote
Old 28/03/2020, 08:11 PM   #9
KamilPolska
Huge Clucker
 
Join Date: May 2016
Location: Poland
Posts: 477
Reputation: 5
Default Re: Identifiers ID

Someone would improve my code to be efficient and work very well. I still want to ask if I wrote the code all right. Thanks.

https://pastebin.com/M8pP9H5t
KamilPolska is offline   Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Dialogs unique identifiers (no longer maintained) Donny_k Help Archive 65 27/11/2009 10:40 PM


All times are GMT. The time now is 06:16 AM.


Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.