[Vince]

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.