SA-MP Forums

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

Reply
 
Thread Tools Display Modes
Old 24/10/2018, 01:29 AM   #1
sammp
Gangsta
 
sammp's Avatar
 
Join Date: Jan 2014
Posts: 584
Reputation: 49
Default How to properly link tables in MySQL

I see so many gamemodes that get posted to this forum. They all work. Their databases work. So what's the problem?

I'm going to keep this short, sweet and to the point.

First of all, lets look at this MySQL table creation:

Code:
DROP TABLE IF EXISTS `accounts`;
CREATE TABLE `accounts` (
  `DatabaseID` int(11) NOT NULL AUTO_INCREMENT,
  `Username` varchar(20) NOT NULL,
  `Password` varchar(256) NOT NULL,
  `RegisterIP` varchar(20) NOT NULL,
  `LastIP` varchar(20) NOT NULL,
  `PosX` float(50,5) NOT NULL,
  `PosY` float(50,5) NOT NULL,
  `PosZ` float(50,5) NOT NULL,
  `PosA` float(50,5) NOT NULL,
  `Health` float(50,5) NOT NULL,
  `Armour` float(50,5) NOT NULL,
  `Money` int(11) NOT NULL,
  `Accent` int(11) NOT NULL,
  `Skin` int(11) NOT NULL,
  `AdminLevel` int(11) NOT NULL,
  PRIMARY KEY (`DatabaseID`)
);
I'm going to quickly show you how to make this table more RDB-like.

In any database - data should only be stored once. There's multiple areas where we could adjust this table to relfect that statement:

To keep it quick, let us simply evaluate the Admins side of this table. We can remove admin-related data from the accounts table and put it in its own table. This table will simply reference the ID of the account (which I'm not sure why the developer made it DatabaseID), and give them a level:

Code:
drop table if exists 'admins';

create table 'admins' (
	'DatabaseID' int,
	'level' int not null,
	
	foreign key (DatabaseID) references accounts(DatabaseID) on delete cascade
);
The last line in there makes DatabaseID a foreign key that references the DatabaseID in the accounts table. The statement 'on delete cascade' means that when I delete the row from the parent table, the corresponding row in admins will drop too.


We can simply tidy up our accounts table by removing the 'AdminLevel' field in the accounts table. Just think about the cardinality of a field, and its optionality. Why give every single player a 'Weapons' field in a roleplay server database if not every player is definitely going to carry a weapon? Why not create another table called 'player_weapons'?

This is only one example so it might become a bit unclear. However, you should 100% follow this when you're creating databases. If you're inputting the same value more than once - then your database design doesn't follow the conventional RDB design and performance will suffer greatly when your database needs to search through thousands of records. This design I've showed you allows me to search ONLY through the admins table if I need to list the administrators, and stops me from searching an entire table of accounts - the majority of whom are not administrators. It's faster, tidier and is far more plausable than the first table I shown you. There's much more that could be done with that table - but you can work that one out.
__________________
SA-MP must become open-source to maximise longetivity of the project.
sammp is offline   Reply With Quote
Old 24/10/2018, 08:55 AM   #2
Yashas
Gangsta
 
Join Date: Jun 2012
Location: India
Posts: 882
Reputation: 317
Default Re: How to properly link tables in MySQL

https://en.wikipedia.org/wiki/Database_normalization

Every schema can be expressed in the third normal form. Such a schema will be a well-designed schema most of the times. An easy way to start would be to merge all the relations into one and write down the functional dependencies on paper and then slowly split them into separate relations to satisfy 2NF and then 3NF.
Yashas is offline   Reply With Quote
Old 31/10/2018, 11:11 PM   #3
Whitley
Little Clucker
 
Whitley's Avatar
 
Join Date: Jan 2018
Posts: 3
Reputation: 0
Default Re: How to properly link tables in MySQL

Thanks, that's helpful!
Whitley 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
a lot of tables in mysql Bussyman Scripting Help 1 11/04/2017 07:04 PM
MYSQL tables help please Terror9921 Server Support 0 22/12/2014 12:57 PM
Tables on MySQL lekizinho Scripting Help 0 03/12/2013 11:42 AM
Mysql tables (Rep +) M16 Scripting Help 5 27/03/2012 10:33 AM
mysql tables/gm EleMenTalL Server Support 1 13/12/2011 05:08 PM


All times are GMT. The time now is 09:35 AM.


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