SA-MP Forums

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

Reply
 
Thread Tools Display Modes
Old 17/06/2016, 04:10 PM   #1
Vince
Spam Machine
 
Vince's Avatar
 
Join Date: Sep 2007
Location: Belgium
Posts: 11,094
Reputation: 2647
Default Association tables [SQL]

What is an association table?
An association table, sometimes known as a cross-reference table, is required when a many-to-many relationship exists between two entities.

Some examples
Underlined attributes are primary keys. Non-essential attributes are left out for clarity.


A player can be a member of many factions, and conversely a faction can have many players.


A player can access many locations, and conversely a location can be a accessed by many players.

More information, please?
The association table is in the center in both images. It can be recognized because it has a multiplicity of "many" (denoted by the 0..*) on both sides. Note that the association table also does not have an "id" field of its own. This is because a row in that table can be uniquely identified by combining the two other ids that reference their respective tables. This also means that both foreign keys originate from the association table.

PHP Code:
FOREIGN KEY (playerIdREFERENCES Player (idON DELETE CASCADE ON UPDATE CASCADE;
FOREIGN KEY (factionIdREFERENCES Faction (idON DELETE RESTRICT ON UPDATE CASCADE;
// restrict in this instance means that a faction cannot be deleted as long as it still has members 
Okay, that sounded sophisticated, so more examples. A playerId can occur more than once in the table. A factionId can also occur more than once in the table. But the combination of them both must be unique. And let's be honest: it would be silly if the combination did occur more than once.

Taking the first example, the 'Member' table might look like:

playerId factionId
42 1
42 2
42 5
137 1
137 7
257 5
384 2

Some queries
Now, by themselves all those ids are meaningless. So we need to write a few queries to join in either or both table(s). Let's assume that players and factions have a name.

To match a player's name with their faction's name
PHP Code:
SELECT 
    Player
.name
    
Faction.name 
FROM 
    Member
INNER JOIN 
    Player ON Player
.id Member.playerId
INNER JOIN 
    Faction ON Faction
.id Member.factionId 
To retrieve a list of group members
PHP Code:
SELECT
    Player
.*
FROM
    Member
WHERE
    factionId 
1
INNER JOIN
    Player ON Player
.id Member.playerId 
To retrieve a list of factions a player is a member of (basically the previous one in reverse)
PHP Code:
SELECT
    Faction
.*
FROM
    Member
WHERE
    playerId 
42
INNER JOIN
    Faction ON Faction
.id Member.groupId 
It is important to note - and this goes for any query - that the SELECT portion of the query, even though it appears first, is evaluated last. Possibly only superseded by ORDER BY. Thus, the query should actually be read: from, where, inner join, select. This makes it slightly easier to understand what is going on. The queries can be shortened by using aliases but for the sake of clarity they have been written out in full.

Extra columns
In all other aspects an association tables acts like a normal table, so feel free to add any more columns such as rank, join date, etc. These values then apply for that specific combination.

Final thoughts
I hope this was clear and understandable. I feel like I forgot to mention some things, but I can't think of anything else to add at this time.
__________________

Last edited by Vince; 25/06/2016 at 03:18 PM.
Vince is offline   Reply With Quote
Old 25/06/2016, 02:03 PM   #2
Luis-
High-roller
 
Luis-'s Avatar
 
Join Date: Jan 2010
Location: England
Posts: 4,050
Reputation: 343
Default Re: Association tables [SQL]

That's actually pretty decent, I need to update my tables to work like this.
Luis- is offline   Reply With Quote
Old 25/06/2016, 03:38 PM   #3
Stanford
High-roller
 
Stanford's Avatar
 
Join Date: Feb 2012
Posts: 1,037
Reputation: 84
Default Re: Association tables [SQL]

Awesome, great and spectacular SQL tutorials! Thanks mate.
__________________
Y_Less: "That code compiles perfectly, but I can tell you now it won't make me a cup of tea."

Y_Less: "I've said this before. People think that MySQL is some simple plug-and-play magic bullet that you can use to just make your mode instantly better. They don't realise that the "L" in "SQL" means "language". So instead of programming PAWN, and using a bit of this mystery thing, you are now programming in two languages at once. If you spend months learning PAWN, you should spend the same time learning SQL, if not more - it is probably the more complex of the two languages, and not something that just gets bolted on to the side."
Stanford is offline   Reply With Quote
Old 26/06/2016, 08:18 PM   #4
Luis-
High-roller
 
Luis-'s Avatar
 
Join Date: Jan 2010
Location: England
Posts: 4,050
Reputation: 343
Default Re: Association tables [SQL]

I have a question. Do I not need to have a "faction" field on my player data table? If I was to use this system.
Luis- is offline   Reply With Quote
Old 26/06/2016, 08:23 PM   #5
Konstantinos
Spam Machine
 
Konstantinos's Avatar
 
Join Date: Dec 2011
Posts: 11,938
Reputation: 1358
Default Re: Association tables [SQL]

Quote:
Originally Posted by Luis- View Post
I have a question. Do I not need to have a "faction" field on my player data table? If I was to use this system.
The point of the tutorial is to keep those data outside of main tables.

"players" and "factions" are the main tables and a third table "members" is what "connects" both.
__________________
Life is like riding a bicycle. To keep your balance, you must keep moving.

[Tutorial] How to use SQLite
[FilterScript] Tune System



www.SF-SE.net
play.sf-se.net:7777
Play now!
Konstantinos is offline   Reply With Quote
Old 26/06/2016, 08:27 PM   #6
Luis-
High-roller
 
Luis-'s Avatar
 
Join Date: Jan 2010
Location: England
Posts: 4,050
Reputation: 343
Default Re: Association tables [SQL]

Yeah, I know that. Tried to make it as clear as possible. I meant on my actual players data table with all the information like money, score etc. I've still got the faction data table. Was just asking if it would be easier to use this method instead of having a "faction" row on the players data table.
Luis- is offline   Reply With Quote
Old 26/06/2016, 08:29 PM   #7
Vince
Spam Machine
 
Vince's Avatar
 
Join Date: Sep 2007
Location: Belgium
Posts: 11,094
Reputation: 2647
Default Re: Association tables [SQL]

If a player can only ever be in one faction then you don't need the association table and you can link the two together directly. Otherwise, no you don't need that field.
__________________
Vince is offline   Reply With Quote
Old 26/06/2016, 08:35 PM   #8
Luis-
High-roller
 
Luis-'s Avatar
 
Join Date: Jan 2010
Location: England
Posts: 4,050
Reputation: 343
Default Re: Association tables [SQL]

Ah right. Still trying to properly understand MySQL, only know the basics at the minute.

If I was to set up a factions table, how should I go about setting the ranks? I would normally just add each row as rank1, rank2 etc.. Would that be the best way to do it?
Luis- is offline   Reply With Quote
Old 26/06/2016, 08:54 PM   #9
Vince
Spam Machine
 
Vince's Avatar
 
Join Date: Sep 2007
Location: Belgium
Posts: 11,094
Reputation: 2647
Default Re: Association tables [SQL]

If you want to add actual rank names then the proper way to do it would be to create yet another table which would then contain factionId, rank and rankname. With the primary key being the combination of factionId and rank.
__________________
Vince is offline   Reply With Quote
Old 26/06/2016, 09:00 PM   #10
Luis-
High-roller
 
Luis-'s Avatar
 
Join Date: Jan 2010
Location: England
Posts: 4,050
Reputation: 343
Default Re: Association tables [SQL]

Ah, right! So, factionId in the ranks table would need to be a foreign key with the id of the actual faction id?
Luis- 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
ip tables. audriuxxx Scripting Help 0 23/03/2013 09:29 AM
Pawn Association problem Rapgangsta Client Support 1 06/02/2013 12:56 PM
Pawn problem (File association) fordawinzz Everything and Nothing 2 02/08/2012 07:31 PM
How to do tables? SomeGuy#1 General 7 01/03/2012 07:49 PM
+~~New! NASASCAR [National Association for San Andreas Stock Car Auto Racing]~~+ Zack9764 Screenshots and Videos 2 07/06/2009 03:45 PM


All times are GMT. The time now is 09:59 PM.


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