17.06.2016, 16:10
(
Последний раз редактировалось Vince; 25.06.2016 в 15:18.
)
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.
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:
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
To retrieve a list of group members
To retrieve a list of factions a player is a member of (basically the previous one in reverse)
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.
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
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 |
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
PHP код:
SELECT
Player.*
FROM
Member
WHERE
factionId = 1
INNER JOIN
Player ON Player.id = Member.playerId
PHP код:
SELECT
Faction.*
FROM
Member
WHERE
playerId = 42
INNER JOIN
Faction ON Faction.id = Member.groupId
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.
