[Tutorial] Association tables [SQL]

[Tutorial] Association tables [SQL] - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+---- Forum: Tutorials (https://sampforum.blast.hk/forumdisplay.php?fid=70)
+---- Thread: [Tutorial] Association tables [SQL] (/showthread.php?tid=609888)

Association tables [SQL] - Vince - 17.06.2016

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 код:



FOREIGN KEY (playerId) REFERENCES Player (id) ON DELETE CASCADE ON UPDATE CASCADE;

FOREIGN KEY (factionId) REFERENCES Faction (id) ON 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 код:



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 код:



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 код:



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.

Re: Association tables [SQL] - Luis- - 25.06.2016

That's actually pretty decent, I need to update my tables to work like this.

Re: Association tables [SQL] - Stanford - 25.06.2016

Awesome, great and spectacular SQL tutorials! Thanks mate.

Re: Association tables [SQL] - Luis- - 26.06.2016

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.

Re: Association tables [SQL] - Konstantinos - 26.06.2016

Quote:

Originally Posted by Luis-

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.

Re: Association tables [SQL] - Luis- - 26.06.2016

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.

Re: Association tables [SQL] - Vince - 26.06.2016

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.

Re: Association tables [SQL] - Luis- - 26.06.2016

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?

Re: Association tables [SQL] - Vince - 26.06.2016

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.

Re: Association tables [SQL] - Luis- - 26.06.2016

Ah, right! So, factionId in the ranks table would need to be a foreign key with the id of the actual faction id?

Re: Association tables [SQL] - Kyle - 07.08.2016

Shouldn't the WHERE cause be after the JOIN?