[Tutorial] Association tables [SQL]
#1

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 (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 код:
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.
Reply
#2

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

Awesome, great and spectacular SQL tutorials! Thanks mate.
Reply
#4

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.
Reply
#5

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.
Reply
#6

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.
Reply
#7

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.
Reply
#8

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?
Reply
#9

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.
Reply
#10

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

Shouldn't the WHERE cause be after the JOIN?
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)